Append a .csv file to a SQL db

Anand, thanks for the quick response. The values won't always be a perfect count...take the address for instance. It will be a different length most times.

I think Anand is right

you need to count the fields not the length of a field, fields or a record

Just loop over the variable and reset the counter if the end of a record is reached

<cfset fieldcounter = 0>
<cfloop Index="LOOP" list="#DATA#" delimiters=",">
.
.
.
    <cfif fieldcounter IS 232> <!--- 232 fields end of record???  --->
       <cfset fieldcounter = 0> <!--- reset counter --->
    <cfelse>
      <cfset fieldcounter = fieldcounter + 1>
    </cfif>
.
.
.
</cfloop>

first replace consecutive ,, by ,NULL, like:


<cffile ACTION="READ" FILE="#form.filename#" VARIABLE="DISK_DATA">
<cfset DISK_DATA=replace(DISK_DATA,",,",",NULL,", "all")>

you need to loop over 2 lists like so:

<cfloop list="#DISK_DATA#" index="row" delimiters="#chr(13)##chr(10)#">
  <cfloop list="#row#" index="col">
   #col# is the column value in the current row
  </cfloop>
</cfloop>

if you wanted to insert, you might not loop through the row, you could just go like:

<cfloop list="#DISK_DATA#" index="row" delimiters="#chr(13)##chr(10)#">
  <cfquery name="insertdata" datasource="yourDSN">
    insert into tablename (col1, col2, col3...)
    values (#preservesinglequotes(replace(replace(row,"'","\'","all"),'"',"'","all"))#);
 
  </cfquery>
</cfloop>

where \' is supposed to escape any single quotes already in the data..

who's bothered abt the lenght of the address !
let it be ne-thing

& if ur scared abt teh address having the "," in it & spoiling ur length of the string [as even ur delimeters are ","]

the do a chnagedelims() & change from "," to "~" or "|"

& then u wld be fine !

K'Rgds
Anand

just try this code.it will replace ,, with ,~,
that means if there is balnk in between comma, it will replace blank with ~.

once it is being converted i think it will be easy for u to insert into table.

<cfoutput>
  <cfset thisrow = '"N","MYWAY","6439116","16","111795630359191309","N","11.7","N","12.0","Prepaid","20030325172959","N",,,"N",,,,"N",,"N",,,,,,,,,,,,,,,,,,,,,"N",,"179563","4.86","4.86","0.00","1",,"JONES & CO","JANET","269 SOUTH 20TH STREET",,,"United States","55555","MYTOWN","PA",,,,,,"N","N","4.86","Package","11.7","222795630359191309","None","63324-1001",,,,,"N",,"N",,,,,,"N",,,"N",,"N","N",,,,,,,,,,,,,,,,,,,,"N",,,,"0","0","0",
"N","MYWAY","6439116","16","333795630359742713","N","1.0","N","1.0","Prepaid","20030325172959","N",,,"N",,,,"N",,"N",,,,,,,,,,,,,,,,,,,,,"N",,"179563","4.54","4.54","0.00","1",,"CHUCK SMITH",,"2301 N MY ST",,,"United States","44444","MYTOWN","PA",,,,,,"Y","N","4.54","Package","1.0","1Z1795630359742713","None","40415-2055-2403",,,,,"N",,"N",,,,,,"N",,,"N",,"N","N",,"Y",,"email","CHUCK SMITH",,,"ME@YAHOO.COM","N",,,,,,,,,,,"N",,,,"0","0","0",
'>
  <cfset mylist = "">
  <cfloop index="i" from="1" to="#len(thisrow)#">
      <cfset mylist = mylist & #mid(thisrow,i,1)#>  
      <cfif #mid(thisrow,i,1)# is ",">
         <cfif (len(thisrow) GTE i+1)  and mid(thisrow,i+1,1) is ",">
             <cfset mylist = mylist & "~">
          </cfif>
       </cfif>  
  </cfloop>
#mylist#
<vfset thisrow = mylist>
</cfoutput>

You do have one other option.

Sql Server has a service called DTS. Which means import/export data from all kinds of sources, including csv or text files.

Depending on the complexity of the data, and the difference in table structures.

You could even run this as a scheduled job in sql server, once you got the data and incoming files as a regular predictable event.

With all the power Cold Fusion has, it sometimes helps for performance and stability to migrate stuff to the database, when it has the power to do so.

I doubt with this format of data - DTS cld be used !

first replace consecutive ,, by ,NULL, like:

<cffile ACTION="READ" FILE="#form.filename#" VARIABLE="DISK_DATA">
<cfset DISK_DATA=replace(DISK_DATA,",,",",NULL,", "all")>

also replace the last one (which is not ,,)
<cfset DISK_DATA=replace(DISK_DATA,",#chr(13)##chr(10)#",",NULL#chr(13)##chr(10)#", "all")>


you need to loop over 2 lists like so:

<cfloop list="#DISK_DATA#" index="row" delimiters="#chr(13)##chr(10)#">
 <cfloop list="#row#" index="col">
  #col# is the column value in the current row
 </cfloop>
</cfloop>

if you wanted to insert, you might not loop through the row, you could just go like:

<cfloop list="#DISK_DATA#" index="row" delimiters="#chr(13)##chr(10)#">
 <cfquery name="insertdata" datasource="yourDSN">
   insert into tablename (col1, col2, col3...)
   values (#preservesinglequotes(replace(replace(row,"'","\'","all"),'"',"'","all"))#);

 </cfquery>
</cfloop>

where \' is supposed to escape any single quotes already in the data..