5 Replies Latest reply on Oct 28, 2012 9:59 AM by BKBK

    Help parsing a .txt file for import

    TheUrbanBrain

      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,W B,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