Help parsing a .txt file for import

[Guest]

Hello, I wonder if someone could help me.  I thought I had my system all set up where I import a .txt file into the sql database but then I noticed the tables cells were not matching up as the file progressed.  It turns out that my loop is picking up the commas within string fields as delimiters. 

Does anyone know how to handle a text file that contains commas within the string fields so Cold Fusion will read them as separate values.  Right now it messing with the order.

For example.  The text file might be

"2101","CF,CI,DB,DE,DR,DS,DW,ES,FD,5P,GC,GD,GR,JA,KI,LO,MB,","MO,MS,OG,QP,RF,SC,ST,VL,WA,WB,WC,WD,WI","GUMBY'S", 56780, 23, "SUPER FUNNY"

on a single line.   I was writing something like this...

<cffile action="read" file="#bigfilename#" variable="bigfiletext">

<cfloop index="index" list="#bigfiletext#" delimiters="#chr(10)##chr(13)#">

    <cfquery name="test" datasource="#dsn#">

         INSERT INTO TestTable (Year, Stores, Something, Name, ID, Price, Detail)

         VALUES (#listgetAt('#index#',1, ',')#, #listgetAt('#index#',2, ',')#,#listgetAt('#index#',3, ',')#,#listgetAt('#index#',4, ',')#,#listgetAt('#index#',5, ',')#,#listgetAt('#index#',6, ',')#,#listgetAt('#index#',7, ',')#)

     </cfquery>

So.... How do I get it to only recoginze the comma delimiters between the strings.  Some fields are coming across in the .txt as "string" and other are number (no ""). 

Any help is appreciated.

I am on Coldfusion10

[Dan_Bracuk]

If you use cfhttp instead of cffile, you can take advantage of the double quotes.  The details are in the documentation.

[Guest]

Thanks, I'll try this.  Will this work when the integer values have no quotes at all around them?

[BKBK]

Ah, I discovered a nice little trick for you. Just replace the double quotes with single quotes, then move the entire text in one wallop into the insert-query. Something like this:

<cffile action="read" file="#bigfilename#" variable="bigfiletext">

<cfloop index="index" list="#bigfiletext#" delimiters="#chr(10)##chr(13)#">

<!--- Replace double quotes with single quotes --->

<cfset rowData = replace(index,'"',"'", "all")>

<cfquery name="test" datasource="#dsn#">

    INSERT INTO TestTable (Year, Stores, Something, Name, ID, Price, Detail)

    VALUES (#rowData#)

</cfquery>

</cfloop>

[Guest]

Thanks, I'll try this.  Will this work when the integer values have no quotes at all around them?

[BKBK]

Naturally. That is in fact as it should be, assuming that you insert the values in database fields of type integer.