Parsing CSV Values In To A ColdFusion Query : Kinky Solutions : A Student's Perspective by Ben Nadel

Parsing CSV Values In To A ColdFusion Query

Posted January 30, 2007 at 3:42 PM

Earlier today I posted about how to parse CSV values into an array while allowing for embedded field qualifiers, field delimiters, and row delimiters. That all went quite nicely (for the 4th or 5th attempt at the problem). Now, I am taking that solution and extending it to convert the CSV values into a ColdFusion query object.

I am actually taking the previous algorithm and tacking on (and modifying) the query conversion. Now, you might think that that is a waste of time; why convert to an array first when you are about to convert to a query. Two reasons:

I have a CSV to Array algorithm that works quite nicely already.
Until we parse the entire CSV value into some sort of intermediary container, we won't know how big to make the query (in terms of columns and rows). And, since we are going to need an intermediary container anyway, might as well go with what already works.

Ok, so here it the ColdFusion user defined function, CSVToQuery(), in typical Kinky Solutions "robust commenting for teaching purposes" style:

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

<cffunction
name="CSVToQuery"
access="public"
returntype="query"
output="false"
hint="Converts the given CSV string to a query.">
<cfargument
name="CSV"
type="string"
required="true"
hint="This is the CSV string that will be manipulated."
/>
<cfargument
name="Delimiter"
type="string"
required="false"
default=","
hint="This is the delimiter that will separate the fields within the CSV value."
/>
<cfargument
name="Qualifier"
type="string"
required="false"
default=""""
hint="This is the qualifier that will wrap around fields that have special characters embeded."
/>
<cfset var LOCAL = StructNew() />
<!---
When accepting delimiters, we only want to use the first
character that we were passed. This is different than
standard ColdFusion, but I am trying to make this as
easy as possible.
--->
<cfset ARGUMENTS.Delimiter = Left( ARGUMENTS.Delimiter, 1 ) />
<!---
When accepting the qualifier, we only want to accept the
first character returned. Is is possible that there is
no qualifier being used. In that case, we can just store
the empty string (leave as-is).
--->
<cfif Len( ARGUMENTS.Qualifier )>
<cfset ARGUMENTS.Qualifier = Left( ARGUMENTS.Qualifier, 1 ) />
</cfif>
<!---
Set a variable to handle the new line. This will be the
character that acts as the record delimiter.
--->
<cfset LOCAL.LineDelimiter = Chr( 10 ) />
<!---
We want to standardize the line breaks in our CSV value.
A "line break" might be a return followed by a feed or
just a line feed. We want to standardize it so that it
is just a line feed. That way, it is easy to check
for later (and it is a single character which makes our
life 1000 times nicer).
--->
<cfset ARGUMENTS.CSV = ARGUMENTS.CSV.ReplaceAll(
"\r?\n",
LOCAL.LineDelimiter
) />
<!---
Let's get an array of delimiters. We will need this when
we are going throuth the tokens and building up field
values. To do this, we are going to strip out all
characters that are NOT delimiters and then get the
character array of the string. This should put each
delimiter at it's own index.
--->
<cfset LOCAL.Delimiters = ARGUMENTS.CSV.ReplaceAll(
"[^\#ARGUMENTS.Delimiter#\#LOCAL.LineDelimiter#]+",
""
)
<!---
Get character array of delimiters. This will put
each found delimiter in its own index (that should
correspond to the tokens).
--->
.ToCharArray()
/>
<!---
Add a blank space to the beginning of every theoretical
field. This will help in make sure that ColdFusion /
Java does not skip over any fields simply because they
do not have a value. We just have to be sure to strip
out this space later on.
First, add a space to the beginning of the string.
--->
<cfset ARGUMENTS.CSV = (" " & ARGUMENTS.CSV) />
<cfset ARGUMENTS.CSV = ARGUMENTS.CSV.ReplaceAll(
"([\#ARGUMENTS.Delimiter#\#LOCAL.LineDelimiter#]{1})",
"$1 "
) />
<!---
Break the CSV value up into raw tokens. Going forward,
some of these tokens may be merged, but doing it this
way will help us iterate over them. When splitting the
string, add a space to each token first to ensure that
the split works properly.
BE CAREFUL! Splitting a string into an array using the
Java String::Split method does not create a COLDFUSION
ARRAY. You cannot alter this array once it has been
created. It can merely be referenced (read only).
We are splitting the CSV value based on the BOTH the
field delimiter and the line delimiter. We will handle
this later as we build values (this is why we created
the array of delimiters above).
--->
<cfset LOCAL.Tokens = ARGUMENTS.CSV.Split(
"[\#ARGUMENTS.Delimiter#\#LOCAL.LineDelimiter#]{1}"
) />
<!---
Set up the default records array. This will be a full
array of arrays, but for now, just create the parent
array with no indexes.
--->
<cfset LOCAL.Rows = ArrayNew( 1 ) />
<!---
Create a new active row. Even if we don't end up adding
any values to this row, it is going to make our lives
more smiple to have it in existence.
--->
<cfset ArrayAppend(
LOCAL.Rows,
ArrayNew( 1 )
) />
<!---
Set up the row index. THis is the row to which we are
actively adding value.
--->
<cfset LOCAL.RowIndex = 1 />
<!---
Set the default flag for wether or not we are in the
middle of building a value across raw tokens.
--->
<cfset LOCAL.IsInValue = false />
<!---
Loop over the raw tokens to start building values. We
have no sense of any row delimiters yet. Those will
have to be checked for as we are building up each value.
--->
<cfloop
index="LOCAL.TokenIndex"
from="1"
to="#ArrayLen( LOCAL.Tokens )#"
step="1">
<!---
Get the current field index. This is the current
index of the array to which we might be appending
values (for a multi-token value).
--->
<cfset LOCAL.FieldIndex = ArrayLen(
LOCAL.Rows[ LOCAL.RowIndex ]
) />
<!---
Get the next token. Trim off the first character
which is the empty string that we added to ensure
proper splitting.
--->
<cfset LOCAL.Token = LOCAL.Tokens[ LOCAL.TokenIndex ].ReplaceFirst(
"^.{1}",
""
) />
<!---
Check to see if we have a field qualifier. If we do,
then we might have to build the value across
multiple fields. If we do not, then the raw tokens
should line up perfectly with the real tokens.
--->
<cfif Len( ARGUMENTS.Qualifier )>
<!---
Check to see if we are currently building a
field value that has been split up among
different delimiters.
--->
<cfif LOCAL.IsInValue>
<!---
ASSERT: Since we are in the middle of
building up a value across tokens, we can
assume that our parent FOR loop has already
executed at least once. Therefore, we can
assume that we have a previous token value
ALREADY in the row value array and that we
have access to a previous delimiter (in
our delimiter array).
--->
<!---
Since we are in the middle of building a
value, we replace out double qualifiers with
a constant. We don't care about the first
qualifier as it can ONLY be an escaped
qualifier (not a field qualifier).
--->
<cfset LOCAL.Token = LOCAL.Token.ReplaceAll(
"\#ARGUMENTS.Qualifier#{2}",
"{QUALIFIER}"
) />
<!---
Add the token to the value we are building.
While this is not easy to read, add it
directly to the results array as this will
allow us to forget about it later. Be sure
to add the PREVIOUS delimiter since it is
actually an embedded delimiter character
(part of the whole field value).
--->
<cfset LOCAL.Rows[ LOCAL.RowIndex ][ LOCAL.FieldIndex ] = (
LOCAL.Rows[ LOCAL.RowIndex ][ LOCAL.FieldIndex ] &
LOCAL.Delimiters[ LOCAL.TokenIndex - 1 ] &
LOCAL.Token
) />
<!---
Now that we have removed the possibly
escaped qualifiers, let's check to see if
this field is ending a multi-token
qualified value (its last character is a
field qualifier).
--->
<cfif (Right( LOCAL.Token, 1 ) EQ ARGUMENTS.Qualifier)>
<!---
Wooohoo! We have reached the end of a
qualified value. We can complete this
value and move onto the next field.
Remove the trailing quote.
Remember, we have already added to token
to the results array so we must now
manipulate the results array directly.
Any changes made to LOCAL.Token at this
point will not affect the results.
--->
<cfset LOCAL.Rows[ LOCAL.RowIndex ][ LOCAL.FieldIndex ] = LOCAL.Rows[ LOCAL.RowIndex ][ LOCAL.FieldIndex ].ReplaceFirst( ".{1}$", "" ) />
<!---
Set the flag to indicate that we are no
longer building a field value across
tokens.
--->
<cfset LOCAL.IsInValue = false />
</cfif>
<cfelse>
<!---
We are NOT in the middle of building a field
value which means that we have to be careful
of a few special token cases:
1. The field is qualified on both ends.
2. The field is qualified on the start end.
--->
<!---
Check to see if the beginning of the field
is qualified. If that is the case then either
this field is starting a multi-token value OR
this field has a completely qualified value.
--->
<cfif (Left( LOCAL.Token, 1 ) EQ ARGUMENTS.Qualifier)>
<!---
Delete the first character of the token.
This is the field qualifier and we do
NOT want to include it in the final value.
--->
<cfset LOCAL.Token = LOCAL.Token.ReplaceFirst(
"^.{1}",
""
) />
<!---
Remove all double qualifiers so that we
can test to see if the field has a
closing qualifier.
--->
<cfset LOCAL.Token = LOCAL.Token.ReplaceAll(
"\#ARGUMENTS.Qualifier#{2}",
"{QUALIFIER}"
) />
<!---
Check to see if this field is a
self-closer. If the first character is a
qualifier (already established) and the
last character is also a qualifier (what
we are about to test for), then this
token is a fully qualified value.
--->
<cfif (Right( LOCAL.Token, 1 ) EQ ARGUMENTS.Qualifier)>
<!---
This token is fully qualified.
Remove the end field qualifier and
append it to the row data.
--->
<cfset ArrayAppend(
LOCAL.Rows[ LOCAL.RowIndex ],
LOCAL.Token.ReplaceFirst(
".{1}$",
""
)
) />
<cfelse>
<!---
This token is not fully qualified
(but the first character was a
qualifier). We are buildling a value
up across differen tokens. Set the
flag for building the value.
--->
<cfset LOCAL.IsInValue = true />
<cfset ArrayAppend(
LOCAL.Rows[ LOCAL.RowIndex ],
LOCAL.Token
) />
</cfif>
<cfelse>
<!---
We are not dealing with a qualified
field (even though we are using field
qualifiers). Just add this token value
as the next value in the row.
--->
<cfset ArrayAppend(
LOCAL.Rows[ LOCAL.RowIndex ],
LOCAL.Token
) />
</cfif>
</cfif>
<!---
As a sort of catch-all, let's remove that
{QUALIFIER} constant that we may have thrown
into a field value. Do NOT use the FieldIndex
value as this might be a corrupt value at
this point in the token iteration.
--->
<cfset LOCAL.Rows[ LOCAL.RowIndex ][ ArrayLen( LOCAL.Rows[ LOCAL.RowIndex ] ) ] = Replace(
LOCAL.Rows[ LOCAL.RowIndex ][ ArrayLen( LOCAL.Rows[ LOCAL.RowIndex ] ) ],
"{QUALIFIER}",
ARGUMENTS.Qualifier,
"ALL"
) />
<cfelse>
<!---
Since we don't have a qualifier, just use the
current raw token as the actual value. We are
NOT going to have to worry about building values
across tokens.
--->
<cfset ArrayAppend(
LOCAL.Rows[ LOCAL.RowIndex ],
LOCAL.Token
) />
</cfif>
<!---
Check to see if we have a next delimiter and if we
do, is it going to start a new row? Be cautious that
we are NOT in the middle of building a value. If we
are building a value then the line delimiter is an
embedded value and should not percipitate a new row.
--->
<cfif (
(NOT LOCAL.IsInValue) AND
(LOCAL.TokenIndex LT ArrayLen( LOCAL.Tokens )) AND
(LOCAL.Delimiters[ LOCAL.TokenIndex ] EQ LOCAL.LineDelimiter)
)>
<!---
The next token is indicating that we are about
start a new row. Add a new array to the parent
and increment the row counter.
--->
<cfset ArrayAppend(
LOCAL.Rows,
ArrayNew( 1 )
) />
<cfset LOCAL.RowIndex = (LOCAL.RowIndex + 1) />
</cfif>
</cfloop>
<!---
ASSERT: At this point, we have parsed the CSV into an
array of arrays (LOCAL.Rows). Now, we can take that
array of arrays and convert it into a query.
--->
<!---
To create a query that fits this array of arrays, we
need to figure out the max length for each row as
well as the number of records.
The number of records is easy - it's the length of the
array. The max field count per row is not that easy. We
will have to iterate over each row to find the max.
However, this works to our advantage as we can use that
array iteration as an opportunity to build up a single
array of empty string that we will use to pre-populate
the query.
--->
<cfset LOCAL.MaxFieldCount = 0 />
<!---
Set up the array of empty values. As we iterate over
the rows, we are going to add an empty value to this
for each record (not field) that we find.
--->
<cfset LOCAL.EmptyArray = ArrayNew( 1 ) />
<cfloop
index="LOCAL.RowIndex"
from="1"
to="#ArrayLen( LOCAL.Rows )#"
step="1">
<cfset LOCAL.MaxFieldCount = Max(
LOCAL.MaxFieldCount,
ArrayLen(
LOCAL.Rows[ LOCAL.RowIndex ]
)
) />
<cfset ArrayAppend(
LOCAL.EmptyArray,
""
) />
</cfloop>
<!---
ASSERT: At this point, LOCAL.MaxFieldCount should hold
the number of fields in the widest row. Additionally,
the LOCAL.EmptyArray should have the same number of
indexes as the row array - each index containing an
empty string.
--->
<!---
Now, let's pre-populate the query with empty strings. We
are going to create the query as all VARCHAR data
fields, starting off with blank. Then we will override
these values shortly.
--->
<cfset LOCAL.Query = QueryNew( "" ) />
<!---
Loop over the max number of fields and create a column
for each records.
--->
<cfloop
index="LOCAL.FieldIndex"
from="1"
to="#LOCAL.MaxFieldCount#"
step="1">
<!---
Add a new query column. By using QueryAddColumn()
rather than QueryAddRow() we are able to leverage
ColdFusion's ability to add row values in bulk
based on an array of values. Since we are going to
pre-populate the query with empty values, we can
just send in the EmptyArray we built previously.
--->
<cfset QueryAddColumn(
LOCAL.Query,
"COLUMN_#LOCAL.FieldIndex#",
"CF_SQL_VARCHAR",
LOCAL.EmptyArray
) />
</cfloop>
<!---
ASSERT: At this point, our return query LOCAL.Query
contains enough columns and rows to handle all the
data that we have stored in our array of arrays.
--->
<!---
Loop over the array to populate the query with
actual data. We are going to have to loop over
each row and then each field.
--->
<cfloop
index="LOCAL.RowIndex"
from="1"
to="#ArrayLen( LOCAL.Rows )#"
step="1">
<cfloop
index="LOCAL.FieldIndex"
from="1"
to="#ArrayLen( LOCAL.Rows[ LOCAL.RowIndex ] )#"
step="1">
<!---
Update the query cell. Remember to cast string
to make sure that the underlying Java data
works properly.
--->
<cfset LOCAL.Query[ "COLUMN_#LOCAL.FieldIndex#" ][ LOCAL.RowIndex ] = JavaCast(
"string",
LOCAL.Rows[ LOCAL.RowIndex ][ LOCAL.FieldIndex ]
) />
</cfloop>
</cfloop>
<!---
Our query has been successfully populated.
Now, return it.
--->
<cfreturn LOCAL.Query />
</cffunction>

To demonstrate that this works with both simple values and with embedded delimiters (both field and record), as well as records with variable length fields, let's build a CSV value that has it all:

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

<!---
Build the CSV string value. This value should contain
both functional and character-literal delimiters.
--->
<cfsavecontent variable="strCSV">
Name,Nickname,Best Asset
Sarah,"""Stubbs""",Butt
Ashley,"Value with
embedded line break",Smile
Heather
"Kat",",",
</cfsavecontent>

Now, let's take that CSV value and pass it to the ColdFusion UDF, CSVToQuery():

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

<!---
Send the CSV value to the UDF. Be sure to trim the value
(leading and trailing spaces). We are going to leave the
default delimiter (,) and qualifier (").
--->
<cfset qResult = CSVToQuery(
CSV = strCSV.Trim()
) />
<cfdump
var="#qResult#"
label="CSV Results Query"
/>

This gives us the following CFDump output:

Notice that rows 4 and 5 which did not have a full 3 columns worth of data are filled out with empty strings. This is thanks to the fact that we built the initial query as all empty values based on a ROW x COLUMN set up that would house all existing CSV data. I think this is the best way to go because there is no hard-fast rule that says a CSV file has to contain a set number of fields per row (or is there????).

Now, if you want to compare this to the intermediary array of arrays value, here is the graphic from the previous post:

Next step, I am going to integrate this functionality with my POI Utility ColdFusion component. Sweeeeet!

Download Code Snippet ZIP File

Related Blog Entries

Replaced My Own iPod Battery With NuPower Battery

Internal Query Processor Error: The Query Processor Ran Out Of Stack Space During Query Optimization

Reader Comments

What a fabulous little function, thanks Ben, you've saved me hours of messing around!!

Posted by Ed on Feb 13, 2007 at 7:14 AM

No worries dude, always happy to help out.

Posted by Ben Nadel on Feb 13, 2007 at 7:25 AM

Very helpful!! Thanks a lot for posting this

Posted by Jimmy on May 8, 2007 at 12:06 AM

Great stuff man, thanx for this nice function. Very helpful! Much appreciated! Keep up the great work!!

Posted by Victor on Jul 6, 2007 at 6:09 AM

Nice work!
Is there a way to flag the first row data as column names?

Posted by JOSH on Jul 23, 2007 at 3:32 PM

@Josh,

Not currently, but I am gonna be trying to move this function into some sort of Project in my projects section. So hopefully soon.

Posted by Ben Nadel on Jul 23, 2007 at 3:36 PM

Bummer.
This would be a nice addition to allow arguments to be passed to flag the first row as column names.

Posted by JOSH on Jul 23, 2007 at 3:59 PM

Josh,

Changing the column names to reflect the first row of the csv file is very easy to do, using another of Bens functions incidently.

Thanks Ben!

Posted by Chris on Aug 25, 2007 at 6:20 PM

Great stuff Ben! I have a large csv file (126 columns) which this has worked great with. This is used on many files, which I need to dump into my sqlserver daily. I have my clients upload with a easy to use file and then this runs after the upload. I have one client which has 640 rows, which made this function time out. Any thoughts? (other than training my users;)

Posted by Matt on Aug 27, 2007 at 5:30 PM

@Matt,

Sorry, I don't have any suggestions off hand. I have not tested this thing on any larger files. If things get big, you might just have to find a real, third-party piece of software. Good luck!

Posted by Ben Nadel on Aug 28, 2007 at 7:26 AM

Ben,

Thanks for such a quick response. For anyone else that has had this problem, I changed things just a bit and made it all work great. I used your csv to array script and insead of adding the array into the master array and having an array with 650 records, I inserted each row seperatly into the table. Thanks for the great script! You saved me tons of time!

Matt

Posted by Matt on Aug 28, 2007 at 3:28 PM

@Matt,

Glad to have helped in some way.

Posted by Ben Nadel on Aug 28, 2007 at 3:31 PM

very helpful code.thanks

Posted by jinesh.k on Sep 6, 2007 at 1:09 AM

Hello all, i'm really new to coldfusion, and i was looking at this function and is very good, exactly what i need because i'm writing a billing solution for my company VoIP-OS (Over satellite) service and the Server it's giving me DAT files for each call, these files are like CSV files but it's using semicolons, viewing at youyr code i think is possible to read these files and save them into a DB.

so, i understand everything so far, but i got one little question, How can i use The function? i've search in the Adobe livedocs , and can't understand where to save it, and how to call it / include it in my template...

Any help would be appreciated

Thank you!

Posted by Victor on Sep 27, 2007 at 9:10 PM

Hi Ben

Great work, one things stop using (local) as variable name. It cause issues in QoQ.

Thanks

Posted by Sana on Nov 2, 2007 at 7:36 AM

@Sana,

Where is the conflict? I should be able to fix that, especially since the functionality is wrapped up in a user defined function, you shouldn't have to worry about the Query of Queries.

Also, there is now a faster version of this algorithm that uses regular expressions:

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

It creates an array of arrays rather than a query, but this, I think, is a better approach than creating a query.

Posted by Ben Nadel on Nov 2, 2007 at 7:40 AM

please let me know whether this will work in coldfusion 5 version.

Posted by swamy on Feb 3, 2008 at 8:28 PM

@swamy,

I am not sure. I haven't used or even seen ColdFusion 5 in years.

Posted by Ben Nadel on Feb 4, 2008 at 7:16 AM

you're a genius (and a good parent probably) thanks for sharing your code

Posted by sam on Apr 30, 2008 at 11:31 AM

Thanks for a great function Ben.

As a follow-up to the "first row is headings" issue, this is a solution.

Add the following argument:
<cfargument
name="FirstRowIsHeadings"
type="boolean"
required="false"
default="true"
hint="Set to false if the heading row is absent"
/>

I mostly have headings but if you dont, default it to "false"

Then right at the end, just before the query is returned add:

<cfif FirstRowIsHeadings>
<cfloop query="LOCAL.Query" startrow="1" endrow="1" >
<cfloop list="#LOCAL.Query.columnlist#" index="col_name">
<cfset field = evaluate("LOCAL.Query.#col_name#")>
<cfset field = replace(field,"-","","ALL")>
<cfset QueryChangeColumnName(LOCAL.Query,"#col_name#","#field#") >
</cfloop>
</cfloop>

Works for me.

Cheers,
Murray

Posted by Murray Hopkins on May 17, 2008 at 10:00 PM

@Murray,

Interesting solution. Glad you got it working nicely for your needs.

Posted by Ben Nadel on May 19, 2008 at 9:11 AM

When i use this UDF i get an error that says
The ReplaceAll method was not found.
Either there are no methods with the specified method name and argument types, or the ReplaceAll method is overloaded with argument types that ColdFusion cannot decipher reliably. ColdFusion found 0 methods that matched the provided arguments. If this is a Java object and you verified that the method exists, you may need to use the javacast function to reduce ambiguity.

what am i doing wrong?

Posted by Faisal Abid on Aug 25, 2008 at 6:57 PM

@Faisal,

That usually means that a value is not being cast properly for the Java method, ReplaceAll(). This method takes two strings. Double check to make sure that both arguments are properly using:

JavaCast( "string", VALUE )

The above demo is not doing that. That was a sloppy of me, but this was a long time ago :)

Posted by Ben Nadel on Aug 26, 2008 at 8:18 AM

Post Comment | Ask Ben

Parsing CSV Values In To A ColdFusion Query : Kinky Solutions : A Student's Perspective by Ben Nadel