also doesn't handle CR/LF in a quoted value. It's MX, but with some
minor modifications it should work on CF5. But hey, I only had 20' to
write it.
I think you can use cfhttp to do it too.
Pascal
<cfsavecontent variable="str">
a,b,c
This is red, "This is sorta ""red""", "This is blue, really"
1,2,3
</cfsavecontent>
<cfscript>
function CsvToQuery(str){
var columnlist = "";
var line = "";
var i = 1;
var j = 0;
var startrow = 1;
var start = 1;
var val = "";
var stTmp = StructNew();
var qReturn = "";
// if the columnlist isn't specified, use the first line in the
csv as columnlist
if(ArrayLen(arguments) GT 1){
columnlist = arguments[2];
}
else{
columnlist = ListFirst(str,chr(13)&chr(10));
startrow = 2;
}
qReturn = QueryNew(columnlist);
for(i=startrow;i LE ListLen(str,chr(13)&chr(10));i=i+1){
line = ListGetAt(str,i,chr(13)&chr(10));
j = 0;
QueryAddRow(qReturn);
start = 1;
while(true){
stTmp = REFind("([^,""]*|[
\t]*""(?:""""|[^""])*""[ \t]*)(,|$)",line,start,true);
if(stTmp.pos[1]){
j=j+1;
if(stTmp.len[2]){
val =
Mid(line,stTmp.pos[2],stTmp.len[2]);
if(REFind("^[
\t]*""((""""|[^""])*)""[ \t]*$",val)){
val = REReplace(val,"^[
\t]*""((""""|[^""])*)""[ \t]*$","\1");
val =
Replace(val,"""""","""","all");
}
}
else val = "";
QuerySetCell(qReturn,ListGetAt(columnlist,j),val);
start = stTmp.pos[1]+stTmp.len[1];
}
else break;
}
}
return qReturn;
}
</cfscript>
<cfdump var="#CsvToQuery(str)#">
<cfdump var="#CsvToQuery(str,'x,y,z')#">
> -----Original Message-----
> From: Rich Ziade [mailto:[EMAIL PROTECTED]
> Sent: dinsdag 22 juni 2004 14:15
> To: CF-Talk
> Subject: CSV HELL
>
> I'm guessing I can probably go down some sort of regular
> _expression_ hell to do this, but is there an easier way?
>
> Thanks,
> Rich
>
[Todays Threads] [This Message] [Subscription] [Fast Unsubscribe] [User Settings] [Donations and Support]
