Loop by number of Columns in CSV upload Coldfusion

Question

I'm customizing a CSV uploader to allow for column mapping. I've got a function that parses it into an Array, then I'm looping through it to generate select boxes with the uploaded file column names to map to existing column names in the corresponding table in my DB.

I can get the select drop downs to populate with the column names, but I can't figure out how to loop through the amount of columns in the uploaded file to limit the total amount of select dropdowns.

Function after it's parsed:

<!--- Get Value from Array based on Column Name --->
<cffunction name="ValByColName" access="public" returntype="string" output="false">
    <cfargument name="ColName"type="string" required="true" default=""/>
    <cfargument name="DataArray" type="array" required="true" default=""/>
    <cfset findValue = keyArray.indexOf(#ColName#) + 1>
    <cfreturn(#DataArray[findValue]#) /> 
</cffunction>

LOOP:

<!--- Set Uploaded file to Array --->
                <cfset arrCSV = CSVToArray(CSVFilePath = #form.UploadedFile#,Delimiter = ",",Qualifier = """") />
                <!--- Create Key array from column names --->
                <cfset keyArray = ArrayNew(1)>
                <cfloop from="1" to="#DON'T KNOW WHAT TO PUT HERE" index="t">
                    <!--- Variable Headers --->
                    <cfif Len(form.UploadedFile) GTE 5>
                        <cftry>

                            <select name="HeaderID" class="search" id="Header">
                            <option selected value="">--- Headers Uploaded ---</option>
                            <cfoutput>
                            <cfloop from="1" to="1" index="i">

                                <cfloop from="1" to="#ArrayLen(arrCSV[i])#" index="j">

                                     <option name="HeaderID" value="#j#">#arrCSV[i][j]#</option>

                                </cfloop>
                            </cfloop>
                            </cfoutput>
                            </select> =
                    </cftry> 

                    </cfif>
                    <cfquery name="clientsCols" datasource="#request.dsn#">
                        select Column_name 
                        from Information_schema.columns 
                        where Table_name like 'Clients'
                    </cfquery>
                    <!--- Constants--->
                    <cfif Len(form.UploadedFile) GTE 5>
                        <cftry>
                            <select name="ColumnID" class="search" id="Column">
                            <option selected value="">--- Headers Clients ---</option>
                            <cfoutput>
                                <cfloop query="clientsCols">
                                <option name="ColumnID" value="#Column_name#">#Column_name#</option>
                                </cfloop>
                            </cfoutput>
                            </select><br /><br />
                    </cftry> 

                    </cfif>
                </cfloop>

(Edit) how to loop through the amount of columns .. to limit the total amount of select dropdowns If you have an array of the data/headers, you can use arrayLen to get the total number of columns. That said .. your loop code looks more bulky/complex than is necessary. What is an example of your array data/headers and what should the form should look like? — Leigh
– Leigh, Commented May 22, 2013 at 16:30
I always preferred using cfhttp and working with a query object for stuff like this. That's just me though. — genericHCU
– genericHCU, Commented May 22, 2013 at 16:43
If the file is well formed, and web accessible, cfhttp is an option (though it can sometimes be a little picky about what it will parse). But the question of creating a simple column mapper still stands. Whichever method is used to parse the csv, the form code should be simpler than the loop above .. — Leigh
– Leigh, Commented May 22, 2013 at 17:40
RE: keyArray.indexOf( ColName ) .. As an side be very careful using undocumented methods like indexOf. Unlike most CF functions, indexOf is both case and data type sensitive. So unless you fully understand the nuances, it can lead to some unexpected results .. For CF9+, I would recommend using ArrayFind/FindNoCase instead. — Leigh
– Leigh, Commented May 22, 2013 at 19:58
@SteveOntologic - You should post the final code as an answer, so it is easier to find than sifting through the comments. — Leigh
– Leigh, Commented May 24, 2013 at 0:44

Miguel-F · Accepted Answer · 2013-06-04 18:47:12Z

If you compare this to the original just above, you will see that using cfloop from="1" to="#ArrayLen(arrCSV[1])#" index="t" got me exactly what I needed (5th line down in solution). After upload, the amount of select box pairs is the number of column headers in the uploaded file.

Thanks for everybody's responses.

<!--- Set Uploaded file to Array --->
<cfset arrCSV = CSVToArray(CSVFilePath = #form.UploadedFile#,Delimiter = ",",Qualifier = """") />
<!--- Create Key array from column names --->
<cfset keyArray = ArrayNew(1)>
<cfloop from="1" to="#ArrayLen(arrCSV[1])#" index="t">
    <!--- Variable Headers --->
    <cfif Len(form.UploadedFile) GTE 5>
        <select name="HeaderID" class="search" id="Header">
            <option selected value="">--- Headers Uploaded ---</option>
            <cfoutput>
            <cfloop from="1" to="1" index="i">
                <cfloop from="1" to="#ArrayLen(arrCSV[i])#" index="j">
                    <option name="HeaderID#t#" value="#j#">#arrCSV[i][j]#</option>
                </cfloop>
            </cfloop>
            </cfoutput>
        </select> =
    </cfif>
    <!---Column Constants--->
    <select name="ColumnID" class="search" id="Column">
        <option selected value="">--- Headers Clients ---</option>
        <cfoutput>
        <cfloop query="clientsCols">
            <option name="ColumnID#T#" value="#Column_name#">#Column_name#</option>
        </cfloop>
        </cfoutput>
    </select><br /><br />
</cfloop>

Collectives™ on Stack Overflow

Loop by number of Columns in CSV upload Coldfusion

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related