How to Upload Excel With ColdFusion

Adobe ColdFusion is a rich web application development platform designed to simplify the interaction between HTML files and databases. With it, you can create, update, retrieve, and delete records from a database using the platform's built in tags, rather than longer form query statements and operators. One of the functions that available with ColdFusion is the insertion of records to a database that are stored in a comma-delimited file, including data from an Excel spreadsheet.

Instructions

1

Open the Excel file you wish to upload and save it as a .CSV file.

2

Upload the .CSV file to a public area of your web server.

3

Create a new ColdFusion web page in your preferred text editor. Insert the following code into it:

Substitute the name you gave to your .CSV file in Step 1 for mycsvfile.csv. This will read the text of the .CSV file and save it as a temporary variable called csvfile.

4

Insert the following code below the code in Step 3:

INSERT INTO mytable (header1,header2,header3,header4)

VALUES

('listgetAt('index',1, ',')',

'listgetAt('index',2, ',')',

'listgetAt('index',3, ',')',

'listgetAt('index',4)'

)

Substitute the name of your data source for mydatasource, the name of your table for mytable, the list of your column headers for the values in the parentheses for the INSERT INTO statement, and the number of values in the listgetAt array for the four values seen above. This will loop through the text of your .CSV and file insert the values on each line into the database, separating them at the commas that delimit the records.

5

Save and publish your web page in the same directory as your .CSV file. Access the page to perform the import.