Need help with csv read.

Explorer ,
Jan 09, 2017 Jan 09, 2017

Copy link to clipboard

Copied

I have some csv files that are in this format:

"Source","Requester ID","ID","Start Date","End Date","location","Value","TimeStamp"

"DCA","tedkrasav","9913457","12/4/2016 12:00:00 AM","12/18/2016 12:00:00 AM","Room_121",,"12/3/2016 2:50:03 PM"

What I am trying to do is read the file and insert all of the individual columns into a DB table.

I am trying to do this method just to see the individual columns but I keep getting an error that states "Invalid list index 2.In function ListGetAt(list, index [, delimiters]), the value of index, 2, is not a valid as the first argument (this list has 1 elements). Valid indexes are in the range 1 through the number of elements in the list" :

<cffile action="read" file="csvdemo.csv" variable="csvfile">


<cfloop index="index" list="#csvfile#" delimiters="#chr(10)##chr(13)#">

<cfoutput>

#listgetAt('#index#',1, ',')# #listgetAt('#index#',2, ',')# #listgetAt('#index#',3, ',')# #listgetAt('#index#',4, ',')#

</cfoutput>
</cfloop>

Does anyone know what I am doing wrong?




Views

300

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 09, 2017 Jan 09, 2017

Copy link to clipboard

Copied

My assessment could be wrong, but I believe that the delimiter should be #chr(13)##chr(10)#, not the other way around.

HTH,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jan 09, 2017 Jan 09, 2017

Copy link to clipboard

Copied

I changed that around but I still received the same error.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 09, 2017 Jan 09, 2017

Copy link to clipboard

Copied

Odd.  Okay, try this.  Set the delimiter to #chr(13) & chr(10)# and see if that fixes it.

HTH,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jan 09, 2017 Jan 09, 2017

Copy link to clipboard

Copied

Still the same error. It keeps stating the list only has 1 element when I try to access the  additional columns.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 09, 2017 Jan 09, 2017

Copy link to clipboard

Copied

Just out of curiosity, is either the server or your workstation _not_ a Windows system?

chr(10) is a linefeed (new line) character.  chr(13) is the carriage return.  chr(13) & chr(10) is a Windows new line.  If you're using a Mac or Linux, that may be why it's not working.  Just a guess.

HTH,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jan 09, 2017 Jan 09, 2017

Copy link to clipboard

Copied

No it's windows.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 09, 2017 Jan 09, 2017

Copy link to clipboard

Copied

I'm at a loss.  The issue is probably not the new line delimiters.  For some reason, your code isn't seeing the first comma, so it thinks the whole line is the only index.

Are you checking the .csv file prior to processing?  Are you sure that these files are in exactly the format you stated?

V/r,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Explorer ,
Jan 09, 2017 Jan 09, 2017

Copy link to clipboard

Copied

Ok I think I have made some progress. The issue was that I was not fully qualifying where the name of the csv was coming from. When I manually typed it in it started working. So I have fixed that and I am now getting the correct columns.

So the only other thing I would like to do is to not read the first row of each file since those are the headers. How do I disregard the first row of the read?

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
LEGEND ,
Jan 09, 2017 Jan 09, 2017

Copy link to clipboard

Copied

Mmmm.. what I would do (not what most people would do) is to use ListDeleteAt() to remove the first index, then loop through the list.

HTH,

^_^

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines
Advocate ,
Jan 11, 2017 Jan 11, 2017

Copy link to clipboard

Copied

LATEST

There is a java csv reader that may make this easier for you. I'm on the road and I don't have access to example code but you could google "coldfusion java csv" and you'll probably find examples.

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
community guidelines