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:
    
http://www.robertmuench.de/download/

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