asked on

csv files and commas in data fields

I have a csv file that needs to be imported to mysql, and then get locations of photos, and rework them. example looks like this:

"Product1","Red","Small","http://www.abc.com/image1.jpg,http://www.com/image2.jpg,http://www.com/image3.jpg","Available"
"Product2","Blue","Small","http://www.abc.com/image1.jpg,http://www.com/image2.jpg,http://www.com/image3.jpg","Available"
"Product3","Yellow","Small","http://www.abc.com/image1.jpg,http://www.com/image2.jpg,http://www.com/image3.jpg","Available"

I can successfully get the data out, but when i try to get the images, it treats them as separate columns. The code I'm using is like that below. How do I grab all the photo column as one column? All columns are separated by double quotes.

<cfloop file="D:\inetpub\seolot\test\csvdemo.csv" index="index" from="2" to="2"> 
 
<cfquery name="importcsv" datasource="seolot"> 
INSERT INTO csvdemo 
         (
         ProductName,
         Color,
         Size
         ) 
         
         VALUES
         (
         '#ReplaceNoCase(listgetAt('#index#',1, ','),'"','','ALL' )#', 
         '#ReplaceNoCase(listgetAt('#index#',2, ','),'"','','ALL' )#',  
         '#ReplaceNoCase(listgetAt('#index#',3),'"','','ALL' )#' 
         ) 
</cfquery> 
 
<!--- GET LIST OF PHOTOS --->
<cfset PhotoList = listgetAt('#index#',4)>
 
<!--- REMOVE DOUBLE QUOTES --->
<cfset PhotoList = #ReplaceNoCase(Photolist,'"','','ALL' )#>
 
1st photo: #listgetAt('#PhotoList#',1)#<br />
 
<br />
<cfloop list="PhotoList" index="i" delimiters=","> 
<cfset PhotoList = listgetAt('#PhotoList#',#i#, ',')>
 
Photo #i#: #PhotoList#<br />
</cfloop>
   
</cfloop>

Open in new window

SOLUTION

Zvonko

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Zvonko

Also be aware that you do not need no quote chars nor hash chars inside CF functions:

<cfset PhotoList = listgetAt(index,7,'"') >

ASKER CERTIFIED SOLUTION

eszaq

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

eszaq

OOOPS, used wrong variable name in my code. Of course it should be:
<cfset index = rereplace(index,'#chr(34)#(,#chr(34)#)?','|','all')>

eszaq

And OF COURSE (dah) i have extra comma at the end of insert query.

monosyth

ASKER

I ended up using a bit of both, and also I used this code below to figure out the null spaces.

<cfscript>
function listFix(list) {
var delim = '|';
var null = "NULL";
var special_char_list = "\,+,*,?,.,[,],^,$,(,),{,},|,-";
var esc_special_char_list = "\\,\+,\*,\?,\.,\[,\],\^,\$,\(,\),\{,\},\|,\-";
var i = "";
 
if(arrayLen(arguments) gt 1) delim = arguments[2];
if(arrayLen(arguments) gt 2) null = arguments[3];
 
if(findnocase(left(list, 1),delim)) list = null & list;
if(findnocase(right(list,1),delim)) list = list & null;
 
i = len(delim) - 1;
while(i GTE 1){
   delim = mid(delim,1,i) & "_Separator_" & mid(delim,i+1,len(delim) - (i));
   i = i - 1;
}
 
delim = ReplaceList(delim, special_char_list, esc_special_char_list);
delim = Replace(delim, "_Separator_", "|", "ALL");
 
list = rereplace(list, "(" & delim & ")(" & delim & ")", "\1" & null & "\2", "ALL");
list = rereplace(list, "(" & delim & ")(" & delim & ")", "\1" & null & "\2", "ALL");
    
return list;
}
</cfscript>
 
<!--- THEN PUT THIS INSIDE YOUR LOOP --->
 <cfset index = listFix(index)>

Open in new window