csv parsing

Guys,

any ideas, im parsing a csv file using coldfusion. Im using this line

<cfloop index="index" list="#mycsv#" delimiters="#chr(10)##chr(13)#">
<cfset field1=replace(#listgetAt('#index#',1, ',')#, "#chr(34)#", "", "all")>
<cfset field2=replace(#listgetAt('#index#',2, ',')#, "#chr(34)#", "", "all")>
</cfloop>

example data

"a","b"
"a,b","c,d" <-- problem coz of comma bewtween a,b

now my problem is when field1 contains comma, it destroys my field2

Any idea

Thanks,

Mike

 

Kaibigan,
You could replace double quote-comma-double quote with something else like double quote-pipe-double quote so you end up with:

Then you can use the pipe as the delimiter.

 

hi tbarr,

Thanks for the quick response. But the CSV is coming from 3rd party API. Anyways i'm trying some regular expression but im still having trouble using rereplace

<cfset index=ReReplace(#index#, '"([a-z]*)[,](?)([a-z]*)"', '', 'ALL')>

but it seems it doesnt work like preg_match_all

example

"1","2,3","4"

but it seems turns into "1,2,3,4" instead of replacing all string bewteen double quote

Kaibigan, please help...

Thanks,

Mike

 

I am not an expert in reg expressions but I think you are putting too much into the RE. This might do it:


<cfset index=ReReplace(#index#, '"[,]"'| '', 'ALL')>
This should a ", " with "| " thus replacing the delimiting comma with a delimiting pipe.

 

tbarr,

almost same result, ill try to play with it

"11","1007","10 Mbps Capped Server (HOU0018)","1167631200","33.00","1","0.00","33.00","cus","0","0","1199167200","1201845600","-33.00","0","","","","","","1","2","1","0","0","","0","0","1.00","","1168105212","0","32","0","0","0","0","0","0","0","0"

"11|1007|10 Mbps Capped Server (HOU0018)|1167631200|33.00|1|0.00|33.00|cus|0|0|1199167200|1201845600|-33.00|0||||||1|2|1|0|0||0|0|1.00||1168105212|0|32|0|0|0|0|0|0|0|0"

Thanks,

Mike

 

What about something like this?

for CF8

<!--- this array will hold all the data from the file --->
<cfset FileDataArray = ArrayNew(1) />

<cfloop file="#ExpandPath("./myfile.csv")#" index="i">
     <!--- create an array to stuff in the next element of the FileDataArray --->
     <cfset ThisLineArray = ArrayNew(1) />
     <cfloop list="#i#" index="j" delimiters="#chr(34)#">
          <cfif Len(j) AND j NEQ ",">
               <cfset ArrayAppend(ThisLineArray,j) />
          </cfif>
     </cfloop>
     <cfset ArrayAppend(FileDataArray,ThisLineArray) />
</cfloop>

Code (markup):

for <CF8

<cffile action="read" file="#ExpandPath("./myfile.csv")#" variable="MyFile" />

<!--- this array will hold all the data from the file --->
<cfset FileDataArray = ArrayNew(1) />


<cfloop list="#MyFile#" index="i" delimiters="#chr(13)##chr(10)#">
     <!--- create an array to stuff in the next element of the FileDataArray --->
     <cfset ThisLineArray = ArrayNew(1) />
     <cfloop list="#i#" index="j" delimiters="#chr(34)#">
          <cfif Len(j) AND j NEQ ",">
               <cfset ArrayAppend(ThisLineArray,j) />
          </cfif>
     </cfloop>
     <cfset ArrayAppend(FileDataArray,ThisLineArray) />
</cfloop>

Code (markup):

This will give you an array, where each element represents a line of your file. Each element within the line array represents one of the columns in your CSV.