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
|