Home   Archive   Permalink

How to connect to excel?

How do I connect to an excel file via odbc without a dsn?
Using the following connection string for ex.
Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=C:\MyExcel.xls;DefaultDir=c:\mypath;
it normally works with a dsn:
p: open odbc://my-excel-dsn

posted by:   Endo       28-Jun-2010/11:37:43-7:00

I typically just export to .csv, and then parse that file:
; Read and parse the CSV formatted file:
filename: %filename.csv
data: copy []
lines: read/lines filename
foreach line lines [
     append/only data parse/all line ","
; Add headers from sections of the spreadsheet to each line item:
info: copy ""
foreach line data [
     either find "Header" line/1 [
         info: line/1
         append line info
; Remove the unwanted descriptive header lines:
remove-each line data [find "Header" line/1/1]
remove-each line data [
     (line/3 = "TITLE") or (line/3 = "DESCRIPTION")

posted by:   Nick       28-Jun-2010/14:32:30-7:00

I haven't used it, but be sure to check out this Excel dialect:

posted by:   Nick       28-Jun-2010/14:50:12-7:00

I need to connect to a real excel file, but thanks for the tip & link.

posted by:   Endo       29-Jun-2010/3:38:58-7:00

Graham answered this on Altme, here is the solution:
p: open [
     scheme: 'ODBC
     target: "Driver={Microsoft Excel Driver (*.xls)};DriverId=790;Dbq=e:\test.xls"
conn: first p
insert conn "select * from [Sheet1$]"
result: copy conn

posted by:   Endo       29-Jun-2010/3:40:43-7:00