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 »

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 »

Download Code Snippet ZIP File

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

• Add To Del.icio.us
• Add To Digg
• Add to Spurl.net
• Add to Wists
• Add to Simpy
• Add to Newsvine
• Add to BlinkList
• Add to Furl
• Add to reddit
• Add to FARK
• Add to BlogMarks
• Add to Yahoo! My Web
• Add to smarking
• Add to Ma.gnolia
• Add to Segnalo

Reader 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]

@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.

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)##">

@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 )#">

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.

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

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?

@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.

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

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

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

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!

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.

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.

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.

@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.

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?

@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.

@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>

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

@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>

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

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

@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.

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

@Tom,

Glad to help. Regular expressions are quite amazing!

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

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.

@Fred,

Yeah, it should handle empty fiends just fine.

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.

@Dave,

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

@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.

@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.

@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?

@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.

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.

@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).

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>

@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?

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)." />

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?

@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

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

@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

Post A Comment

Please review the following issues:

Author Name:
Author Email:
Author Website:
Comment:	Formatting: <strong>bold</strong> <em>italic<em>
	Remember my information
	Subscribe to entry *
	Send me a copy of this comment (as part of the comment thread)

 

* If you subscribe, any new posts to this thread will be sent to your email address.