How do I read from an MS Excel file?

Posted on 2007-08-08
Last Modified: 2013-11-05
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.
Question by:New2CF
    LVL 1

    Expert Comment

    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.
    LVL 1

    Accepted Solution

    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

    Expert Comment

    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

    How to run any project with ease

    Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
    - Combine task lists, docs, spreadsheets, and chat in one
    - View and edit from mobile/offline
    - Cut down on emails

    Join & Write a Comment

    I spent nearly three days trying to figure out how incorporate OAuth in Coldfusion for the Eventful API. Hopefully, this article will allow Coldfusion Programmers to buzz through the API when they need to. Basically, what this script does is authori…
    Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
    This Micro Tutorial demonstrate the bugs in Microsoft Excel for Mac with Pivot Charts.
    This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.

    734 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    25 Experts available now in Live!

    Get 1:1 Help Now