http://www.ostermiller.org/utils/CSV.html that I've used with great success
for parsing CSV files. It takes care of all the nastiness with quotes in
Excel CSV files. It also has classes for handling "normal" CSV files.
Little more work than dropping in a UDF, but it's blazing fast.
Cheers,
barneyb
> -----Original Message-----
> From: Pascal Peters [mailto:[EMAIL PROTECTED]
> Sent: Tuesday, June 22, 2004 6:29 AM
> To: CF-Talk
> Subject: RE: CSV HELL
>
> This is the regexp hell. There is no error handling for wrong csv. It
> 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]
