How to loop through CSV file

[Like2Flex]

Hi,

I want to loop through each column of a CSV file.
And while looping each column, I want to loop each row starting with the 2st row.

I wrote the code as below, but it is looping through the rows starting from row 1.
How do I make it start the loop as from row 2 in the CSV ?
(see code below)

Can someone tell me how to do this?

Thx




<!--- get and read the CSV-TXT file --->
<cffile action="read" file="C:\Documents and Settings\user\Desktop\EM-CSV\test.csv" variable="csvfile">

<!--- loop through the CSV-TXT file on line breaks and insert into database --->
<table border="1">


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

<!--- <cfoutput>#listgetAt('#index#',1, ',')#</cfoutput><br> --->
<tr>
<td><cfoutput>#index#</cfoutput></td>
<td><cfoutput>#listgetAt('#index#',2, ',')#</cfoutput></td>
<td><cfoutput>#listgetAt('#index#',3, ',')#</cfoutput></td>
</tr>

</cfloop>
</table>

[BKBK]

It often helps to use a suggestive name like row.

[Like2Flex]

Hello, your code works perfectly.
Now I am having a slight problem with it.
I have added the code to check whether a particular cell of the CSV is blank. (see code below)

But it is giving me error message.

Can you please tell me what am doing wrong?

Thx

<!--- loop through the CSV-TXT file on line breaks and insert into database --->

<cfset isRowTwoOrLater=false>
<table border="1">
<cfloop index="row" list="#csvfile#" delimiters="#chr(10)##chr(13)#">
<cfif isRowTwoOrLater>
<tr>
<cfif #listgetAt(row,1, ',')# neq ''> <!--- I HAVE ADDED THIS PART TO CHECK WHETHER IT IS BLANK --->
<td><cfoutput>#listgetAt(row,1, ',')#</cfoutput></td>
<cfelse>
<td><cfoutput>It is blank</cfoutput></td>
</cfif>

<td><cfoutput>#listgetAt(row,2, ',')#</cfoutput></td>
<td><cfoutput>#listgetAt(row,3, ',')#</cfoutput></td>
</tr>
<cfelse>
<cfset isRowTwoOrLater = true>
</cfif>


</cfloop>
</table>

[BKBK]

What error message did you get? Was it perhaps about an empty row? If so, here

[Like2Flex]

Hi,

I tried the code but still it does not work (but there is no error message now , just to let you know, the error message was for empty cell,you were right)

Lets come back to my problem.

I have 5 columns in the CSV, with multiple rows. And it can happen that one of the cell is empty.
So when I loop through the rows the, <cfoutput>#listlen(row,',')#</cfoutput> varies. So if the first row has 5 items length is 5 and if the second row has 4 items, length is 4.

Is there a way to get Length of Lists with blank items as well. That is, whatever the no of items in the row, it should give 5 ?

So when I include this part, it ignores the blank cell and jumps to the next cell.
<cfif Len(Trim(row)) EQ 0 OR Len(Trim(listgetAt(row,1, ','))) EQ 0>
<td>It is blank</td>
<cfelse>

I need your idea / help here.

Rgds

[BKBK · Accepted solution]

Hi Like2Flex,

The problem is that coldfusion doesn't see

list="a,b,,,,,c,,d"

as a list of 9 elements as you would expect. It sees just the 4 elements a,b,c,d because Coldfusion doesn't include two delimiters next to each other.

One solution to the problem is to manually insert a space character between two delimiters that are next to each other. Here is an example of such a solution

<cfset list="a,b,,,,,c,,d">
<cfset list=replace(list,",,",", ,","all")>
<cfset list=replace(list,",,",", ,","all")>
list length: <cfoutput>#listlen(list)#</cfoutput>

[Like2Flex]

Hello BKBK,

It works perfectly your piece of example, But I don't get it when you put this code twice :

<cfset list="a,b,,,,,c,,d">
<cfset list=replace(list,",,",", ,","all")>
<cfset list=replace(list,",,",", ,","all")>
list length: <cfoutput>#listlen(list)#</cfoutput>


The only issue is that if the list starts with ,comma,
we have to manipulate this part separately. Easy this part.

<cfset list=",a,b,,,,,c,,d">


But Thx and Cheers for everything 🙂

[BKBK]

> But I don't get it when you put this code twice
That is necessary. Coldfusion makes the substitution stepwise, from left to right. For example, to replace ",," by ",[space]," in ",,,,," Coldfusion would do the replacement for the first and second commas, and then for the third and fourth commas. The result would then be ",[space],,[space],,". As this contains delimiters next to each other, you need to repeat the procedure.

[Like2Flex]

Ok Thx.

How bright..and I've been fighting with this for hours !!

Many thx again. It was simple logic.

[azadisg]

I have not tried it myself, but theoretically you can do a Replace(row, ",,", ", ,") first, adding a space (or whatever you want, even your "It is blank" phrase) between consecutive commas in the list so that the list item will not be blank and will be picked up by the list functions...

[Like2Flex]

Hello,

I did :
<cfoutput>#Replace(row, ",,", ", ,", "ALL")#</cfoutput>
Replace(row, ",,", ", ,")

[Like2Flex]

Hello,

This does not work to 100%.

I did :
<cfoutput>#Replace(row, ",,", ", ,","ALL")#</cfoutput>

So if I have in my list -

A,,,B the result I get is A, ,, ,B which is not good.

Any other idea?

Thx

[rizalfir]

Just an idea:
replace empty cell (,,) with a special characters, such as: replace(myCSV,",,",",#specialchars#,","ALL")
and then when outputting the result, make sure you replace those special characters with blank.
Make sure the special characters is really special that it will not be exist in the original CSV.

I think my cf_csv2query tag uses the same technique for this, altough it's much more complicated cause we have to support quoted cells too.

[Like2Flex]

Hello,

could you help with an example pls.
I fail to understand your idea?

Thx

[azadisg]

quote:

---

Originally posted by: Like2Flex
Hello,

This does not work to 100%.

I did :
<cfoutput>#Replace(row, ",,", ", ,","ALL")#</cfoutput>

So if I have in my list -

A,,,B the result I get is A, ,, ,B which is not good.

Any other idea?

Thx

---

It sure looks like it works exactly as you want it to work: from a list A,,,B,C which to CF has 3 elements you get a list A, ,, ,B,C which to CF has 5 elements. Isn't that what you want?

I just suggest you do not do <cfoutput>#Replace(....)#</cfoutput>; instead do a <cfset row = Replace(...)> inside your inside loop.

[Like2Flex]

ya u r rite.

i made a mistake but i got the answer.

thx buddy.