Need help/advice on CFLOOP and arrays

Hey all, here's my situation:

1) The user saves a 4 column (as many rows as they want) excel file into a tab-delimited text file.
2) The user uploads that file, using CFFILE.
3) For each row, I create 4 variables, and upload those into a 4 column temp table.

1 & 2 are no problem, and I can read the file they uploaded just fine, but I can't wrap my head around #3. How can I, or what is the best approach, to snag the 4 fields from each row and assign each a variable? Here's a 2 row example of what the user will be uploading:

row1col1 tab row1col2 tab row1col3 tab row1col4
row2col1 tab row2col2 tab row2col3 tab row2col4

I've played with loops and arrays before, but not to this extent. When I create an array using the break (#chr(13)#) as a delimiter, my array has 2 entries (for the 2 rows), and I can list the uploaded text file as the seperate rows but I still need to individualize each entry in that row into its own variable. And if I use the tab as my delimiter, I get an array with 8 entries, with each entry individualized, but then I need to make sure I know which ones are in which row.

I feel like with CFLOOP there's a semi-easy way to attack this, and was hoping someone could point me in the right direction. I also hope my explanation made sense

Thanks.

I'll keep my mouth shut about the Excel, but it sounds like you've got it pretty much figured out already.

If the 8 entries are correct, then you should be good to go.
You can either push some sort of identifying variable on the end of the array telling you what it is, or you can have a separate array storing the indentifier in indices corresponding to your split array.

You want two loops. The outer loop uses the break as the delimiter. So for each iteration of the loop you're dealing with one row. The inner loop take that one row and then uses tab as the delimiter. So for each iteration of the inner loop you're dealing with one "cell". Make sense?

I'd use a two dimensional array. So as you do the 2 loops you should get a new element in the first dimension for each row, and a new element in the second dimension for each value...like this:

[1][1] = row1col1
[1][2] = row1col2
[1][3] = row1col3
[1][4] = row1col4
[2][1] = row2col1
[2][2] = row2col2
...etc.

Yeah, the excel thing is a user thing, no way around that. But saving their reports as tab-delimited files seems to work ok, so I can't complain too much.

Thanks to both of you. I tried the multidimensional array yesterday, but I just didn't "get" it. I'll read up some more on that.

One thing I couldn't figure out is, displaying the array wasn't a problem, but how do you assign each element to a variable? Once I get this figured out, and know where and what everything is, I'm going to be uploading it to a temp table...I imagine I'll be looping through the INSERT query, and...well nevermind. I'll worry about that when I get there

<cfset yourVar = yourArray[1]>

Right. But it will be pretty straight-forward since the VALUES portion of the query is comma-deliminated anyway, and CF has the built-in ArrayToList function that does just that.

Argh. I swear the more I play with this, the more complicated I make it for myself, and then I wind up back at square one. I know I'm missing something so simple, and it's killing me.

First of all, about two loops, this:produces the same exact output as this:So I'm guessing the point of two loops is to build the 2 dimensional array. What I fail to see is HOW to build that 2 dimensional array. I know that, before the looping, I use <CFSET aUploadArray = ArrayNew(2)> to create the array...but to add elements to the array, you need to cfset each element with the [1][1], [1][2], etc like you said above...do/can you create the first dimension within the first loop, and the 2nd dimension within the 2nd loop? And how would I automate it? It's not always going to 2 rows. Sometimes it'll be 8, sometimes 20...so I can't hard code the [1][1] and so on when setting the array's elements. The only thing I can think of is even MORE looping, and that makes my brain explode.

Thanks for the help.

First, you can't make the delimiter the tab unless you use the ASCII code like this #chr(9)#.

The easiest way to build up the array would be something like this (note that I'm using carriage return and line feed instead of just carriage return as the outer loop delimiter):

<cfset myArray = arrayNew(2) />

<CFLOOP INDEX="breaks" LIST="#UploadedFile#" DELIMITERS="#chr(13)##chr(10)#">
<cfset arrayAppend( myArray, arrayNew(1) ) />
<CFLOOP INDEX="tabs" LIST="#breaks#" DELIMITERS="#chr(9)">
<cfset arrayAppend( myArray[arrayLen(myArray)], tabs ) />
<CFOUTPUT>#tabs#<BR></CFOUTPUT>
</CFLOOP>
</CFLOOP>

Thank you, that's exactly what I was looking for. The arrayAppend( myArray[arrayLen(myArray)] is what I was missing/messing up.