Thread Rating:
  • 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Excel database (enumerating database tables, fields, etc)
#1
How can I get the headers and sheet name of a Excel file using Database Class?
#2
An Excel sheet is the same as a table in a database. I don't know what is the SQL for enumerating tables. With Excel sheets can be used the same SQL as with Access, so I would try to find this information in MS Access Help.
#3
Didn't find how to get that info using MS Jet SQL. But can get using ADO.

Get only table names (or sheet names)
Code:
Copy      Help
out
Database d
d.Open(d.CsExcel("$personal$\book1.xls"))
ADO.Recordset rs=d.conn.OpenSchema(ADO.adSchemaTables)
;out rs.RecordCount
ARRAY(str) a
d.RsGetAll(rs a)

;;all info
;int i j
;for i 0 a.len

,;for j 0 a.len(1)
,,;out a[j i]
,;out "--"

;table names (or Excel sheet names)
int i
for i 0 a.len
,out a[2 i]

Get table names and column names
Code:
Copy      Help
out
Database d
d.Open(d.CsExcel("$personal$\book1.xls"))
ADO.Recordset rs=d.conn.OpenSchema(ADO.adSchemaColumns)
ARRAY(str) a
d.RsGetAll(rs a)

;;all info
;int i j
;for i 0 a.len

,;for j 0 a.len(1)
,,;out a[j i]
,;out "--"

;table names (or sheet names), column positions and column names
int i
for i 0 a.len
,out "%s %i %s" a[2 i] val(a[6 i]) a[3 i]

ADO reference is available in MSDN Library (search for OpenSchema). The same information also is available in MS Access Help.
#4
Thanks.
#5
and for CSV files?
#6
When using text/csv files:
database = folder,
table = file,
header = first line.

Code:
Copy      Help
out
Database d
d.Open(d.CsText("$personal$"))
ADO.Recordset rs=d.conn.OpenSchema(ADO.adSchemaColumns)
ARRAY(str) a
d.RsGetAll(rs a)

;;all info
;int i j
;for i 0 a.len

,;for j 0 a.len(1)
,,;out a[j i]
,;out "--"

;table names, column positions and column names
int i
for i 0 a.len
,out "%s %i %s" a[2 i] val(a[6 i]) a[3 i]
#7
Thanks again.
#8
This code shows me the first row but NOT header.
#9
I confused it with something. Use the same code as with Excel database. Only change d.Open line (see updated example).
#10
Ok.
Thanks for all.


Forum Jump:


Users browsing this thread: 1 Guest(s)