I have 2 columns of select boxes. The first (left) is populated by all columns of an uploaded CSV file. The second (right) is all of the columns of a "Clients" table that they can import to. The number of pairs is determined by the number of total columns in the uploaded file.
Users can then go through and set what columns of their data will update which columns in our Clients table. For instance, they would set the first box in the left to "Email" and the first box on the right to "Email" and their emails would be updated to the email column in our DB.
If they have a column called "Organization" and we only have "Company" then they can set it accordingly to update. Basically mapping their imported clients, so they can use a wider range of column name convention.
I already have the loops setup to populate from some help here.
Now I'm trying to update the query. Here's the selectboxes after the file is uploaded.
<form class="formContent960" id="csvmap" name="csvmap" method="post" action="custom_upload_update.cfm">
<table class="form960" cellpadding="5">
<tbody>
<!--- Set Uploaded file to Array --->
<cfset arrCSV = CSVToArray(CSVFilePath = #form.UploadedFile#,Delimiter = ",",Qualifier = """") />
<!--- Create Key array from column names --->
<cfloop from="1" to="#ArrayLen(arrCSV[1])#" index="t">
<!--- Variable Headers --->
<cfif Len(form.UploadedFile) GTE 5>
<cfoutput>
<select name="upfield[#t#]" class="search" id="Header">
</cfoutput>
<option selected value="">--- Headers Uploaded ---</option>
<cfoutput>
<cfloop from="1" to="1" index="i">
<cfloop from="1" to="#ArrayLen(arrCSV[i])#" index="j">
<option value="#arrCSV[i][j]#">#arrCSV[i][j]#</option>
</cfloop>
</cfloop>
</cfoutput>
</select> =
</cfif>
<!---Column Constants--->
<cfoutput>
<select name="bofield[#t#]" class="search" id="Column">
</cfoutput>
<option selected value="">--- Headers Clients ---</option>
<cfoutput>
<cfloop query="clientsCols">
<option value="#Column_name#">#Column_name#</option>
</cfloop>
</cfoutput>
</select><br /><br />
</cfloop>
</tbody>
<cfoutput>
<input type="hidden" name="filelength" id="filelength" value="#ArrayLen(arrCSV[1])#">
</cfoutput>
<input type="submit" name="csvmapsubmit" id="csvmapsubmit">
</table>
</form>
So I'm thinking I need to set a variable containing the values of the Clients(Right) columns select string to set which columns to update in the query inside of a loop.
Then set the uploaded fields to update the data in those rows inside a sub loop for the values.
Like:
<cfloop>
<cfset bostring = "#bofields#"/>
</cfloop>
<cfloop>
<cfquery name="addclientubmit" datasource="#request.dsn#">
INSERT INTO Clients
(
#bostring#
)
VALUES
(
<cfloop>
#uploaded Values#
</cfloop>
)
</cfquery>
</cfloop>
Not working with proper syntax, just trying to include my general logic of the issue for discussion purposes.
Any help would be appreciated. Thank you in Advance,
Steve
Before I get to your current form, let me mention another option: using your database's import tools, like OPENROWSET or BULK INSERT. The former is a little more flexible it can be used from a SELECT statement. So you could do a direct insert from the CSV file, no looping. (I usually prefer to insert into a temp table first. Run a few validation queries, then insert/select the data into the primary table. But it depends on the application ..)
Anyway, once you have validated the column names, the insert with OPENROWSET is just a single query:
<!--- see below for how to validate list of column names --->
<cfquery name="insertRawData" datasource="yourDSN">
INSERT INTO YourTable ( #theSelectedColumnNames# )
SELECT *
FROM OPENROWSET( 'Microsoft.Jet.OLEDB.4.0'
,'text;HDR=YES;Database=c:\some\path\'
, 'SELECT * FROM [yourFileName.csv]' )
</cfquery>
Form:
Using your current method you would need to read the CSV file twice: once on the "mapping" page and again on the action page. Technically it could be as simple as giving the db column select lists the same name. So the names would be submitted as a comma delimited list:
<cfset csvHeaders = csvData[1]>
<cfloop array="#csvHeaders#" index="headerName">
<cfoutput>
Map file header: #headerName#
to column:
<select name="targetColumns">
<option value="" selected>--- column name---</option>
<cfloop query="getColumnNames">
<option value="#column_name#">#column_name#</option>
</cfloop>
</select>
</cfoutput>
<br>
</cfloop>
Validate Columns:
Then re-validate the list of column names against your db metadata to prevent sql injection. Do not skip that step!. (You could also use a separate mapping table instead, so as not to expose the db schema. That is my preference.)
<cfquery name="qVerify" datasource="yourDSN">
SELECT COUNT(COLUMN_NAME) AS NumberOfColumns
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'YourTableName'
AND COLUMN_NAME IN
(
<cfqueryparam value="#form.targetColumns#" cfsqltype="cf_sql_varchar">
)
</cfquery>
<cfif qVerify.recordCount eq 0 OR qVerify.NumberOfColumns neq listLen(form.targetColumns)>
ERROR. Missing or invalid column name(s) detected
<cfabort>
</cfif>
Insert Data:
Finally re-read the CSV file and loop to insert each row. Your actual code should contain a LOT more validation (handling of invalid column names, etcetera) but this is the basic idea:
<cfset csvData = CSVToArray(....)>
<!--- deduct one to skip header row --->
<cfset numberOfRows = arrayLen(csvData) - 1>
<cfset numberOfColumns = arrayLen(csvData[1])>
<cfif numberOfColumns eq 0 OR numberOfColumns neq listLen(form.targetColumns)>
ERROR. Missing or invalid column name(s) detected
<cfabort>
</cfif>
<cfloop from="1" to="#numberOfRows#" index="rowIndex">
<cfquery ...>
INSERT INTO ClientColumnMappings ( #form.targetColumns# )
VALUES
(
<cfloop from="1" to="#numberOfColumns#" index="colIndex">
<cfif colIndex gt 1>,</cfif>
<cfqueryparam value="#csvData[rowIndex][colIndex]#" cfsqltype="cf_sql_varchar">
</cfloop>
)
</cfquery>
</cfloop>