Excel to Database

[mariebmk]

Hello! I'm developing an application where the user can upload an excel file, which is then inserted into a database. I have the code written that I can user the excel file as a datasource and query from it. . .the only problem being I need to know the worksheet name to do so. I have no guarantee that they will use a standard worksheet name. Is there any way I can either read the name before querying or that I can change it to something standard?

Thanks!!!

 

[ericaalicen]

I'm not sure there is a way to get a worksheet name. You would likely have more control if you could get the user to export the excel file to a .csv file before uploading. You could then user a LOAD DATA INFILE statement to import the data.

<cfquery name="updateTable" datasource="datasource">
LOAD DATA INFILE 'path/filename.csv' INTO TABLE tablename
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\r\n'
</cfquery>

This will import a comma delimited list with single quotes as text qualifier.

 

[ericaalicen]

I don't think LOAD DATA INFILE works with Access if that's what you're using for a database though.