Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 240
  • Last Modified:

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.
1 Solution
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.
Hi, there's no need to export to access, only add $ between Sheet1 and the Range specified like this:

'SELECT * FROM [Sheet1$B4:N76]
IN '#fullPath#' 'EXCEL 5.0;'

PD. Sorry i don't speak english very well
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now