CODEWITHSUNDEEP
loopscsvcoldfusionupload
Steve Ontologic
Steve Ontologic

Reputation: 199

Loop by number of Columns in CSV upload Coldfusion

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>

Upvotes: 3

Views: 410

Answers (1)

Steve Ontologic
Steve Ontologic

Reputation: 199

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>

Upvotes: 1

Related Questions