CSVToArray() ColdFusion UDF For Parsing CSV Data / Files

Posted October 12, 2007 at 8:59 AM

Tags: ColdFusion

NOTE: This UDF was modified on 10/15/2007 to include a trimming argument.

Yesterday, Troy asked me to wrap my new CSV parsing algorithm up into a ColdFusion user defined function for ease of use. So, here it is:

 Launch code in new window » Download code as text file »

  • <cffunction
  • name="CSVToArray"
  • access="public"
  • returntype="array"
  • output="false"
  • hint="Takes a CSV file or CSV data value and converts it to an array of arrays based on the given field delimiter. Line delimiter is assumed to be new line / carriage return related.">
  •  
  • <!--- Define arguments. --->
  • <cfargument
  • name="File"
  • type="string"
  • required="false"
  • default=""
  • hint="The optional file containing the CSV data."
  • />
  •  
  • <cfargument
  • name="CSV"
  • type="string"
  • required="false"
  • default=""
  • hint="The CSV text data (if the file was not used)."
  • />
  •  
  • <cfargument
  • name="Delimiter"
  • type="string"
  • required="false"
  • default=","
  • hint="The data field delimiter."
  • />
  •  
  • <cfargument
  • name="Trim"
  • type="boolean"
  • required="false"
  • default="true"
  • hint="Flags whether or not to trim the END of the file for line breaks and carriage returns."
  • />
  •  
  •  
  • <!--- Define the local scope. --->
  • <cfset var LOCAL = StructNew() />
  •  
  •  
  • <!---
  • Check to see if we are using a CSV File. If so,
  • then all we want to do is move the file data into
  • the CSV variable. That way, the rest of the algorithm
  • can be uniform.
  • --->
  • <cfif Len( ARGUMENTS.File )>
  •  
  • <!--- Read the file into Data. --->
  • <cffile
  • action="read"
  • file="#ARGUMENTS.File#"
  • variable="ARGUMENTS.CSV"
  • />
  •  
  • </cfif>
  •  
  •  
  • <!---
  • ASSERT: At this point, no matter how the data was
  • passed in, we now have it in the CSV variable.
  • --->
  •  
  •  
  • <!---
  • Check to see if we need to trim the data. Be default,
  • we are going to pull off any new line and carraige
  • returns that are at the end of the file (we do NOT want
  • to strip spaces or tabs).
  • --->
  • <cfif ARGUMENTS.Trim>
  •  
  • <!--- Remove trailing returns. --->
  • <cfset ARGUMENTS.CSV = REReplace(
  • ARGUMENTS.CSV,
  • "[\r\n]+$",
  • "",
  • "ALL"
  • ) />
  •  
  • </cfif>
  •  
  •  
  • <!--- Make sure the delimiter is just one character. --->
  • <cfif (Len( ARGUMENTS.Delimiter ) NEQ 1)>
  •  
  • <!--- Set the default delimiter value. --->
  • <cfset ARGUMENTS.Delimiter = "," />
  •  
  • </cfif>
  •  
  •  
  • <!---
  • Create a compiled Java regular expression pattern object
  • for the experssion that will be needed to parse the
  • CSV tokens including the field values as well as any
  • delimiters along the way.
  • --->
  • <cfset LOCAL.Pattern = CreateObject(
  • "java",
  • "java.util.regex.Pattern"
  • ).Compile(
  • JavaCast(
  • "string",
  •  
  • <!--- Delimiter. --->
  • "\G(\#ARGUMENTS.Delimiter#|\r?\n|\r|^)" &
  •  
  • <!--- Quoted field value. --->
  • "(?:""([^""]*+(?>""""[^""]*+)*)""|" &
  •  
  • <!--- Standard field value --->
  • "([^""\#ARGUMENTS.Delimiter#\r\n]*+))"
  • )
  • )
  • />
  •  
  • <!---
  • Get the pattern matcher for our target text (the
  • CSV data). This will allows us to iterate over all the
  • tokens in the CSV data for individual evaluation.
  • --->
  • <cfset LOCAL.Matcher = LOCAL.Pattern.Matcher(
  • JavaCast( "string", ARGUMENTS.CSV )
  • ) />
  •  
  •  
  • <!---
  • Create an array to hold the CSV data. We are going
  • to create an array of arrays in which each nested
  • array represents a row in the CSV data file.
  • --->
  • <cfset LOCAL.Data = ArrayNew( 1 ) />
  •  
  • <!--- Start off with a new array for the new data. --->
  • <cfset ArrayAppend( LOCAL.Data, ArrayNew( 1 ) ) />
  •  
  •  
  • <!---
  • Here's where the magic is taking place; we are going
  • to use the Java pattern matcher to iterate over each
  • of the CSV data fields using the regular expression
  • we defined above.
  •  
  • Each match will have at least the field value and
  • possibly an optional trailing delimiter.
  • --->
  • <cfloop condition="LOCAL.Matcher.Find()">
  •  
  • <!---
  • Get the delimiter. We know that the delimiter will
  • always be matched, but in the case that it matched
  • the START expression, it will not have a length.
  • --->
  • <cfset LOCAL.Delimiter = LOCAL.Matcher.Group(
  • JavaCast( "int", 1 )
  • ) />
  •  
  •  
  • <!---
  • Check for delimiter length and is not the field
  • delimiter. This is the only time we ever need to
  • perform an action (adding a new line array). We
  • need to check the length because it might be the
  • START STRING match which is empty.
  • --->
  • <cfif (
  • Len( LOCAL.Delimiter ) AND
  • (LOCAL.Delimiter NEQ ARGUMENTS.Delimiter)
  • )>
  •  
  • <!--- Start new row data array. --->
  • <cfset ArrayAppend(
  • LOCAL.Data,
  • ArrayNew( 1 )
  • ) />
  •  
  • </cfif>
  •  
  •  
  • <!---
  • Get the field token value in group 2 (which may
  • not exist if the field value was not qualified.
  • --->
  • <cfset LOCAL.Value = LOCAL.Matcher.Group(
  • JavaCast( "int", 2 )
  • ) />
  •  
  • <!---
  • Check to see if the value exists. If it doesn't
  • exist, then we want the non-qualified field. If
  • it does exist, then we want to replace any escaped
  • embedded quotes.
  • --->
  • <cfif StructKeyExists( LOCAL, "Value" )>
  •  
  • <!---
  • Replace escpaed quotes with an unescaped double
  • quote. No need to perform regex for this.
  • --->
  • <cfset LOCAL.Value = Replace(
  • LOCAL.Value,
  • """""",
  • """",
  • "all"
  • ) />
  •  
  • <cfelse>
  •  
  • <!---
  • No qualified field value was found, so use group
  • 3 - the non-qualified alternative.
  • --->
  • <cfset LOCAL.Value = LOCAL.Matcher.Group(
  • JavaCast( "int", 3 )
  • ) />
  •  
  • </cfif>
  •  
  •  
  • <!--- Add the field value to the row array. --->
  • <cfset ArrayAppend(
  • LOCAL.Data[ ArrayLen( LOCAL.Data ) ],
  • LOCAL.Value
  • ) />
  •  
  • </cfloop>
  •  
  •  
  • <!---
  • At this point, our array should contain the parsed
  • contents of the CSV value. Return the array.
  • --->
  • <cfreturn LOCAL.Data />
  • </cffunction>

It can take either a CSV data value or a CSV file path. However, since it can take either value or file path, you must used Named Parameters, not ordered parameters for it to work correctly. Additionally, I fixed a small bug in the algorithm which forgot to check for the length of the delimiter (required for the start-of-string match).

Here, you can see a sample of the function being called with both a CSV data value and with a File path (output not shown):

 Launch code in new window » Download code as text file »

  • <!--- Load CSV file. Use the default comma delimiter. --->
  • <cfset arrData = CSVToArray(
  • File = ExpandPath( "./application.log" )
  • ) />
  •  
  •  
  • <!---
  • Load CSV data. Instead of using default comma
  • delimiter, pass in overriding TAB delimiter.
  • --->
  • <cfset arrData = CSVToArray(
  • CSV = strCSVTab,
  • Delimiter = " "
  • ) />

Download Code Snippet ZIP File

Post Comment  |  Ask Ben  |  Permalink  |  Other Searches  |  Print Page




Learning ColdFusion 9 - ColdFusion 9 tutorials, samples, examples, demos

Reader Comments

Oct 12, 2007 at 11:04 AM // reply »
9 Comments

In the previous UDF, I would pass in "Chr(9)" for a TAB. This is not working now, nor is passing in "\t". What is needed in this new regex UDF to represent TAB? It looks like the example you show for overriding TAB delimiter just has a space " ".

Also, I'm seeing an extra array element which is empty on each of my comma separated test files. Have you tested this on a real file and not just your manually generated data of Kim, Sarah, and Kit? Wondering if there is something quirky in my files, or this is a real bug?

So a file with 592 rows gives me a cfdump with array element 593 which only contains:
593 array
1 [empty string]


Oct 12, 2007 at 11:18 AM // reply »
6,371 Comments

@Troy,

Try passing in #Char( 9 )#" (make sure you are using the hash signs). Also, I tested it on a Application.log file for fun and it seemed to work, although, I might not have caught an empty row at the bottom. Let me do some more testing.


Oct 12, 2007 at 11:40 AM // reply »
9 Comments

Ok, I see there is a carraige return at the end of the final row of data in my CSV files and then a final line that is blank. After I removed the blank line, all was well. However, your old UDF seemed to account for this case and handled it gracefully, not putting the blank line into the array of arrays.

Also, I tried the TAB like this, but didn't work.
<cfset Variables.del = "##Char(9)##">


Oct 12, 2007 at 11:52 AM // reply »
6,371 Comments

@Troy,

I will take a look at the old UDF to see what it was doing. Also, don't use double-hash:

<cfset Variables.del = "#Char( 9 )#">


Oct 12, 2007 at 12:07 PM // reply »
9 Comments

I was using the double hash to escape them. I thought you wanted the single hashes passed into the UDF. Using your way, CF throws this error: Variable CHAR is undefined.


Oct 12, 2007 at 12:08 PM // reply »
6,371 Comments

Ooops :) Chr(), not CHAR()... my bad.


Oct 12, 2007 at 1:07 PM // reply »
9 Comments

That's what I noted I tried in the first comment I made "Chr(9)". Maybe you need to translate that inside the UDF to "\t" in the places needed in your regex lines of code?


Oct 12, 2007 at 1:32 PM // reply »
6,371 Comments

@Troy,

I think there is some confusion on when to use the Hash signs. If we get rid of the quotes, you don't even need to use them. I just ran this and it works:

<cfset arrData = CSVToArray(
CSV = strCSVTab,
Delimiter = Chr( 9 )
) />

Notice that I am just passing in Chr( 9 ) as the delimiter value.


Michael Long
Oct 12, 2007 at 5:05 PM // reply »
1 Comments

Does this correctly handle cases where there might be carriage returns within quoted values?


Oct 12, 2007 at 5:14 PM // reply »
6,371 Comments

Absolutely. As long as the value is within a quoted field value, you are all good.


Matthew
Oct 13, 2007 at 5:18 PM // reply »
41 Comments

I have easily taken Ben's function and parsed 500-1000 line csv that is tab delimited. I used the Chr(9) as the delimeter. I did bring it to his attention about having an extra line at the end.. but It was only because there was a carriage return in my txt file. It wasnt to ask him to change his function.

Also I just accounted for the blank lines and didnt show them, since I get the files in the same format everytime. I account for additional formats as well, so it didnt matter if it really had extra lines, as my app ignores them anyway.

Anyway, good job Ben. Keep up the good work. Ill probably integrate this into my next version of my csv parsing application, as it seems to be a little faster. Got to test it some to make sure.

Have a good day Ben and everyone.

-Matthew


Oct 13, 2007 at 8:37 PM // reply »
9 Comments

I found a dumb mistake in my code and now my TAB parsing works with Chr(9). I would like the final blank line to be ignored if it's a simple tweak Ben can make. But not a showstopper, and I'm grateful to Ben for all his help, good stuff!


Oct 14, 2007 at 1:21 PM // reply »
6,371 Comments

Do you guys think it would make sense to just strip off the final new lines and carriage returns? Basically, like doing an RTrim() that would stop if it hit a TAB character or space or something? At first, I was worried that this might erase needed data, but I don't think a number of empty lines at the end is necessarily lost... unless the number or rows is meaningful.

I think I will add this and make an optional boolean argument that can turn it OFF.


Oct 15, 2007 at 7:54 AM // reply »
6,371 Comments

I have updated the UDF in this post to include some quasi RTrim() functionality. It will not strip spaces or tabs as these might be valid data characters. It will, however, by default, strip off trailing new line and carriage returns characters from the file. This can be turned off with a fourth, optional argument.


Steve
Jan 21, 2008 at 5:52 PM // reply »
11 Comments

Since Excel does not use the backslash as an escape character, but instead escapes quote literals with two quotes,
which does your regex use?

I presume it is "" based on some of your writings, but I am no regex expert.


Jan 23, 2008 at 9:51 AM // reply »
6,371 Comments

@Steve,

Yeah, I am using "" (double-double quotes) in order to escape quotes within a qualified field. And remember, all "special characters" MUST be in qualified fields.


May 30, 2008 at 9:37 AM // reply »
9 Comments

Ben,

What happens if you have something like this?

Field1,Field2,Field3
Data1,Data2,Data3
Data1
Data1,,,

Where the commas aren't always there? I know exporting from Excel to CSV sometimes the commas get ignored it the cell is empty.

Have you ever come across this?


May 30, 2008 at 9:40 AM // reply »
6,371 Comments

@Chris,

That should be fine. If you look at the example on which this UDF is based:

http://www.bennadel.com/index.cfm?dax=blog:978.view

... you will see that it handles empty spaces quite nicely.


Steve
May 30, 2008 at 4:33 PM // reply »
11 Comments

@Chris
The array will contain the number of fields that exist in that row,
Some rows may be 3, some 1 , some 4 (1 and 3empty)
What I do is when I am iterating over the array is I check that the array row has the number of fields I am expecting, then I do any data validation I expect on each field (numeric, date, etc)

Also I check for a row of all empty fields (and exit my import).
Excel is also notorious for
field1, field2, field3
d1,d2,d3
d1,d2,d3
,,,
,,,
,,,
(and so on for a few hundred or thousand rows)
,,,
<eof>


Jun 2, 2008 at 11:08 AM // reply »
9 Comments

Thanks Ben.

@Steve, I want to take my CSV file and insert it into a database table. However, since some commas are missing, the "Column count doesn't match value count at row". Any ideas how I can fix that? It is due to the fact when exporting to a csv file from Excel it leaves off commas. I add the commas, it works fine. I want to validate the csv before input.

Thanks,
Chris


Steve
Jun 2, 2008 at 11:18 AM // reply »
11 Comments

@Chris
You need to perform all your validation before attempting database insert.
Say I want 5 columns...
<cfif ArrayLen(arrData[i]) LT 5>
<!--- Do Nothing, Exel empty line --->
<cfelseif NOT isnumeric(arrData[i][1])>
<!--- error field ivalid data --->
<cfelse>
Do dtabase work.
</cfif>


Jun 2, 2008 at 11:23 AM // reply »
9 Comments

Thanks Steve.

If I want 5 columns, but one of my rows only has 4, is there away to add another column so tthat the insert doesn't have to fail?

Thanks,
Chris


Rob G.
Oct 1, 2008 at 10:25 AM // reply »
7 Comments

Ben,

I got this UDF working and it is very helpful. I just have a quick question maybe you can answer. I am using this as an include in a page which is calling it 2 times as a cfinclude.

It works when i call it one time but when i call it the second time i get this:
-----------------------------------------------------
Routines cannot be declared more than once.
The routine "CSVToArray" has been declared twice in different templates.

ColdFusion cannot determine the line of the template that caused this error. This is often caused by an error in the exception handling subsystem.
-----------------------------------------------------

Is there any way around this? Thanks in advance


Oct 1, 2008 at 10:57 AM // reply »
6,371 Comments

@Rob,

You can't include a UDF more than once per page request. To get around this, I would include it once in your Application.cfm or something. You need to find a way to only include it once.


tom
Jan 12, 2009 at 11:15 AM // reply »
1 Comments

amazing use of regular expressions.. this has made a simple load of ~2,500 lines that much easier!


Jan 12, 2009 at 11:18 AM // reply »
6,371 Comments

@Tom,

Glad to help. Regular expressions are quite amazing!


Jan 12, 2009 at 10:56 PM // reply »
1 Comments

I tried this function out today, and it rocks! Thank you!


Fred
Feb 25, 2009 at 6:29 AM // reply »
1 Comments

Can anyone tell me if this code will work with tabbed CSVs where there are multiple tabs to represent empty fields? The problem I chave is that i am given a CSV that has some column values missing, and those missing values are just represented as multiple tabs.


Feb 25, 2009 at 6:43 PM // reply »
6,371 Comments

@Fred,

Yeah, it should handle empty fiends just fine.


Dave
Apr 16, 2009 at 5:01 PM // reply »
1 Comments

I tried the function and it worked until I ran into the following error:

"The element at position 20 of dimension 1, of array variable "COLS," cannot be found."

Any idea what may be causing this. It was able to insery 98% of all the records in my [tab delimited] text file.


May 3, 2009 at 3:13 PM // reply »
6,371 Comments

@Dave,

COLS is not a variable in the UDF above (that I can see). Is COLS something in your calling code?


DLackey
May 6, 2009 at 1:29 PM // reply »
12 Comments

@Ben,

I love it when I see your site show up in Google Search results as I know I'm heading in the right direction. Dave (Apr 16, 2009 at 5:01 PM) issue is what brought me here, I believe, which utlimately displayed your CSVToArray function. To Dave, I suspect you are looping over your Array Elements and inserting that value into a table b/c that is what I'm attempting to do and I'm getting the a similar error. I believe what is generating the error in my array is the element located at CSVArray[x][8] (an empty value - supposed to contain a date). So, my question is, being inside of an element, what do you recommend checking for a valid date? I'm currently using <cfif IsDate(CSVArray[x][8])> ... </cfif> inside of my sql insert statement. Is there something better because this doesn't seem to be working... or there is another problem going on.


May 7, 2009 at 8:29 AM // reply »
6,371 Comments

@DLackey,

Have you tried CFDump'ing out the first X items in array to give it a visual check? Sometimes the data is not as clean as you might think.


DLackey
May 7, 2009 at 9:02 AM // reply »
12 Comments

@Ben Nadel,

Yes. Based on my understanding of the way arrays work, if the array is empty it doesn't really exists (e.g.

/start
Array[1][1] = "Adam"
Array[1][2] = "Microsoft Server 2003"
Array[1][3] = [empty string]
Array[1][4] = "Module 1"
...
Array[1][8] = [empty string]
Array[1][9] = [empty string]
/end

What I'm needing to do is insert the values of the array into my database and if the array doesn't exists, the insert fails. I found another posting discussing this and I found a UDF (http://www.cflib.org/udf/isDefinedValueMX) that I thought would do the trick but either I'm implementing it incorrectly or I heading down the wrong path.

The value for Array[1][3] doesnt' seem to cause any problems, it is only for the dates for [1][8] and [1][9].

My query is as follows:

INSERT INTO table
( UID,
COURSE_NAME,
COURSE_DURATION,
MODULE,
SCORE,
MINUTES_SPENT,
LESSON_STATUS,
<cfif isDefinedValueMX("arrData[x][8]")>DATE_STARTED,</cfif>
<cfif isDefinedValueMX("arrDate[x][9]")>DATE_COMPLETED,</cfif>
UPDATED,
UPDATED_BY )
VALUES
(
<cfqueryparam value="#arrData[x][1]#" cfsqltype="CF_SQL_VARCHAR">,
<cfqueryparam value="#arrData[x][2]#" cfsqltype="CF_SQL_VARCHAR">,
<cfqueryparam value="#arrData[x][3]#" cfsqltype="CF_SQL_VARCHAR">,
<cfqueryparam value="#arrData[x][4]#" cfsqltype="CF_SQL_VARCHAR">,
<cfqueryparam value="#arrData[x][5]#" cfsqltype="CF_SQL_VARCHAR">,
<cfqueryparam value="#arrData[x][6]#" cfsqltype="CF_SQL_VARCHAR">,
<cfqueryparam value="#arrData[x][7]#" cfsqltype="CF_SQL_VARCHAR">,
<cfif isDefinedValueMX("arrData[x][8]")>to_date(<cfqueryparam value="#dateformat(arrData[x][8],'mm/dd/yyyy')# #timeformat(arrData[x][8],'HH:MM:SS')#" cfsqltype="CF_SQL_VARCHAR">,'mm/dd/yyyy hh24:mi:ss'),</cfif>
<cfif isDefinedValueMX("arrData[x][9]")>to_date(<cfqueryparam value="#dateformat(arrData[x][9],'mm/dd/yyyy')# #timeformat(arrData[x][9],'HH:MM:SS')#" cfsqltype="CF_SQL_VARCHAR">,'mm/dd/yyyy hh24:mi:ss'),</cfif>
to_date(<cfqueryparam value="#dateformat(now(),'mm/dd/yyyy')# #timeformat(now(),'HH:MM:SS')#" cfsqltype="CF_SQL_VARCHAR">,'mm/dd/yyyy hh24:mi:ss'),
<cfqueryparam cfsqltype="cf_sql_varchar" value="#lcase(cookie.admin_id)#"> )

Any advice on how to handle null values in the array?


May 21, 2009 at 8:55 AM // reply »
6,371 Comments

@DLackey,

If I have an array with unknown index lengths, I will just CFParam the values with something I can easily check:

<cfparam name="arrData[ 8 ]" type="string" default="" />

Then, in the insert, I can do something like this:

<cfqueryparam
. . . . value="#arrData[ 8 ]#"
. . . . cfsqltype="cf_sql_timestamp"
. . . . null="#NOT IsNumericDate( arrData[ 8 ] )#"
. . . . />

The null="" attribute allows me to NULL'ify the date/time column for that row if the passed-in date is not actually a date.


BYJ
Aug 5, 2009 at 1:59 PM // reply »
4 Comments

Ben,
I experiences 2 things when applying this code to my tab delimited text file.
1.
I used your code to parse less than 6000 records and It took me forever to just see the cfdump. Finally I have to stop and killed my browser.
When I deleted those records and left only 4 records (lines) to test again, The code works!
So I'm not sure what do you mean when you wrote that using regex will speed up the process.

2.
I call the function this way:
Either of this one is working with small number of records:
(The diff is only on #chr(9)# or chr(9))

<cfset arrData = CSVToArray(File="#FeedPath##FileName#",Delimiter = "#chr(9)#") />

OR

<cfset arrData = CSVToArray(File="#FeedPath##FileName#",Delimiter = Chr( 9 )) />

BUT this one showed error:
<cfset arrData = CSVToArray(File="#FeedPath##FileName#", CSV = strCSVTab,Delimiter = Chr( 9 )) />

The error says:
Variable STRCSVTAB is undefined.


DLackey
Aug 5, 2009 at 2:08 PM // reply »
12 Comments

@Ben,

Sorry Ben, I didn't see your reply until BYJ just replied. I ended up using the cfparam approach and it worked (removing the isDefinedValueMX udf).


BYJ
Aug 5, 2009 at 2:56 PM // reply »
4 Comments

Ben,
To add on my previous comment. I have just finished parsing 375 records(lines) in tab delimited text file.

Total Time Avg Time Count
--------- -------- -----
130394 ms 130394 ms 1
131722 ms TOTAL EXECUTION TIME

This is just to create the array, I think it took too much time.
May be I did something wrong? all I did was copy and pasted your function and then write the following to call the function:

<cfset arrData = CSVToArray(File="#FeedPath##FileName#",Delimiter = "#chr(9)#") />

<cfdump var="#arrData#">
<cfabort>


Aug 5, 2009 at 7:03 PM // reply »
6,371 Comments

@BYJ,

You can't pass in both a File and a CSV value. It's one or the other. As far as the test you ran, that is a LONG time to parse just a few hundred records. Are you doing anything else other than the parsing?


BYJ
Aug 6, 2009 at 9:58 AM // reply »
4 Comments

Ben,
As far far as my codes, other than this function, I only have CFSET to get the file location as: #FeedPath##FileName#
Then I do:
<cfset arrData = CSVToArray(File="#FeedPath##FileName#",Delimiter = "#chr(9)#") />
To call the function, and I do cfdump:
<cfdump var="#arrData#">

I need to pass the File and delimiter to the function, it is working but not with the speed I'm expecting.

I'm not too sure what is this argument for and when will I need to pass this argument to the function?
I thought I just have to pass the file location and delimiter.

<cfargument name="CSV" type="string" required="false" default=""
hint="The CSV text data (if the file was not used)." />


BYJ
Aug 6, 2009 at 10:17 AM // reply »
4 Comments

Hi Ben,
I found another bug on the function.
I used a different tab delimited file, one of the row is an empty space row:
AAAAAAA-tab-BBBB-tab-1234-tab-CCCCCCCC
--empty new row---
XXXXXXX-tab-YYYY-tab-5456-tab-ZZZZZZZZ
I got this result:
array 1 has an array elements AAAAAA, BBBB, etc. but
array 2 is an empty array.

I thought this code will catch this kind of problem but it is not. The result that I got was like this:

<!---Check to see if we need to trim the data. By default,we are going to pull off any new line and carraige returns that are at the end of the file (we do NOT want to strip spaces or tabs).--->

<cfif ARGUMENTS.Trim>
<!--- Remove trailing returns. --->
<cfset ARGUMENTS.CSV = REReplace(ARGUMENTS.CSV,"[\r\n]+$"," ","ALL") />
</cfif>

I realized that this file is a bad file, I could have emailed the client to re-send the file. But the function has removing training returns ability, which I personaly think, is a very good idea to have except it is not working.
Have you ran into this kind of file while testing?


Jonathan Slattery
Aug 10, 2009 at 6:01 AM // reply »
1 Comments

@BYJ: The "TRIM/Remove trailing returns" check is only applying that check to the VERY END of the CSV file contents because the $ in the Regular Expression means 'match only at the end of the string'.
In this case, the end of the string is the end of the file content. (the file is one big long string).
It was not intended to remove blank lines in the middle.

However, you could probably add an additional boolean parameter to the UDF to called stripBlankLines or something analagous, and run it just before the trim check to strip out extra lines in the middle...

# <cfif ARGUMENTS.stripBlankLines>
#
# <!--- Remove blank lines from the middle of the file by consolidating them to a single \r\n. --->

# <cfset ARGUMENTS.CSV = REReplace(
# ARGUMENTS.CSV,
# "(\r?\n)(?:\r?\n)+",
# "$1",
# "ALL"
# ) />
#
# </cfif>

be careful with that though, as it can mess up quote-qualified string fields that contain carriage returns/line feeds, etc., but something like that should work as long as you're not worried about such outlier cases.

@Ben or any Regex Experts, please double-check my work on that... It's late and I don't have time to verify with any testing right now...

@Ben, one question on my mind: You didn't explicitly discredit the CSV-reader Query Datasource implementation method, but i assume there was a reason for creating this UDF... How well does this beat the query version? That's usually done the trick for me faster than attempting to parse the CSV file, unless I needed to do some initial massaging of the file, etc.
Although, i haven't ever dropped down to Java level code like this in CF, as I don't have any Java experience. Or is this mainly aimed for non-windows platorms?

JS


Aug 31, 2009 at 6:18 PM // reply »
21 Comments

Going to try this out. Initial look seems very promising. I've got some very dirty data however... will have to proceed cautiously.


Sep 2, 2009 at 8:47 AM // reply »
6,371 Comments

@BYJ,

As Jonathan said, the trimming is only for the end of the file (so people don't get random records at the end). Empty lines, mid-file are acceptable and just treated as empty records.

@Jonathan,

I have nothing against the datasource CSV file read - I just have never tried it before, that's all. I would love to see if there was a way to set up a data source for a single file, then be able to save each CSV to the same location such that you don't have to set up a datasource more than once.

@Brian,

Good luck! As long as the data fits CSV formatting standards, it should be ok.


Post Comment  |  Ask Ben

Recent Blog Comments
Nov 7, 2009 at 5:53 PM
Ask Ben: Javascript String Replace Method
You can find here an advanced function that prepared with javascript replace function. This can make the first letters of words, sentences, lines and whatever you define automatically: http://www.m ... read »
Andrew Neely
Nov 7, 2009 at 4:56 PM
A Moment That Touched Me - The Fountainhead
Ben, Glad you enjoyed the podcast. Yeah, the Tank Riot guys can get really chatty during the episodes, but that's part of the charm of it for me. They've covered everything from Nichola Tesla to Cha ... read »
Nov 7, 2009 at 4:43 PM
Building A Fixed-Position Bottom Menu Bar (ala FaceBook)
Is it possible to make some more MenĂ¼`s ? ... read »
Jill
Nov 7, 2009 at 11:40 AM
How To Unformat Your Code (Like A Pro)
Derek, I think you might be right - sweet! Thanks for the link :) ... read »
Nov 7, 2009 at 11:25 AM
How To Unformat Your Code (Like A Pro)
I think it would be way easier to just use this http://www.logichammer.com/html-formatter/ He just released v3 and it rocks. ... read »
Jill
Nov 7, 2009 at 7:58 AM
How To Unformat Your Code (Like A Pro)
LMAO - this was pretty funny! I have to admit - I also love to reformat code so I can read it. My boss used to tell me to leave my OCD at home. Now I don't feel so bad after reading everyone else' ... read »
Nov 6, 2009 at 10:10 PM
How To Unformat Your Code (Like A Pro)
The timing of this post is just uncanny. I spent the last 15-20 minutes manually un-formatting my "Ben Nadel" style code within a CFC of mine. I was really digging the readability a few weeks ago, bu ... read »
Roe
Nov 6, 2009 at 5:11 PM
Passing Arrays By Reference In ColdFusion - SWEEET!
ArraySort also reorders the results of these java obj's ... read »