New2CF
asked on
How do I read from an MS Excel file?
I have read from an Excel sheet before by setting up a datasource via CF Administrator. I can read from my original Excel spreadsheet; however, it was created by me w/o much formatting. I now have to read from a sheet that has much more formatting (i.e. I could reference a column by "Cat" because I named the column as such; however, the new format calls for "I. Category Identifier:").
Original Query:
SELECT * FROM [Sheet1B4:N76]
IN '#fullPath#' 'EXCEL 5.0;'
From this query, I referenced values as, #query.Cat#; however, I'm not sure how to reference the newly formated column names. BTW, this is for a client, and I'm certain they don't want to purchase any custom tags.
Original Query:
SELECT * FROM [Sheet1B4:N76]
IN '#fullPath#' 'EXCEL 5.0;'
From this query, I referenced values as, #query.Cat#; however, I'm not sure how to reference the newly formated column names. BTW, this is for a client, and I'm certain they don't want to purchase any custom tags.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
an easy thing would be to define a range where your data is stored and then specify in a different workbook (destination) inside the query something like this:
select * from "Range name"
If you want a vbscript I can help, or if you want to stay in excel just use the external data source and do a query
Good luck
select * from "Range name"
If you want a vbscript I can help, or if you want to stay in excel just use the external data source and do a query
Good luck
how about importing your excel file into microsoft access? There you probably can define your excel file data into named columns and you can run your select statements. First, you create a new database and then from the file menu, you import your excel file.