Query an Excel file via ODBC 6

[cf2001]

I have a requirement where contents of an Excel file will be uploaded to a database (which will be done frequently). I'm trying to use the ODBC connection to an Excel and it does not work. Does anyone have a working syntax? Thanks.

 

[sknyppy]

Just select driver type as excel and it should work. Don't use any $signs in your sheetnames.

~Dave

 

[JHoarty]

Try selecting the area of the worksheet you plan to use as your "table", then select INSERT, NAME, DEFINE and give the selected area a name. Type the name and click "Add". That becomes your tablename.

Then point your datasource to that spreadsheet using the Excel driver. Verify the datasource. Note that the spreadsheet should not be open or it won't verify. Say your datasource is "spreadsheet".

Now, suppose you named your selected area "chartdata". The first row should be labels, like "People", "Dogs", "Cats".
Subsequent rows are numbers, "10", "5", "3".

<cfquery datasource=&quot;spreadsheet&quot; name=&quot;getChartData&quot;>
SELECT * FROM chartdata
</cfquery>

<cfoutput query=&quot;getChartData&quot;>
#People#, #Dogs#, #Cats#
</cfoutput>

would return:

10.0, 5.0, 3.0 John Hoarty
jhoarty@quickestore.com

http://www.quickestore.com

 

[cf2001]

John,

Cool! It works! You've been very helpful on this. I've search all over and can't seem to find somebody with the right answer. You did. Thanks a lot!