Thanks Thanks:  0
Likes Likes:  0
Results 1 to 4 of 4

Thread: Data Extraction from external source... HELP!

  1. #1
    Board Regular
    Join Date
    May 2002
    Location
    Dallas
    Posts
    366
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Ok, this one's driving me absolutely nuts:

    I've been using Excel's Query wizard for some time now to do SQL-type extractions, but I'm befuddled on this one. I'm attempting to query a .csv file, but for some reason all the cells are returning as text values, even though there are either numbers or dates ~only~ in them. If I go to one of these cells, push F2, then enter, it assumes its proper numerical value. If I open the .csv file into Excel, all the number-type values are reading as they should.

    Can anyone help on this? Is there a way to set up the text driver in the Data Extraction wizard to accept numerical values? I'd even settle for a macro or VBA code that activates when the data is pulled; the only issue with this is I'm pulling 45,000+ rows and up to 100 columns at a time.

    PLEASE HELP!

    Thanks in advance
    ~ Thomas

  2. #2
    MrExcel MVP
    Join Date
    Feb 2002
    Location
    Austin, Texas USA
    Posts
    11,654
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Make a copy of your .csv file and change it's extension to .txt. Edit the file. Are your values quoted?

  3. #3
    Board Regular
    Join Date
    May 2002
    Location
    Dallas
    Posts
    366
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    I tried converting it to a .txt file, but that doesn't seem to do the trick.

    The frustrating thing in this is when I look at the file that I am querying itself, all the formats are as they should be. I've done SQL extractions in the past that preserve the date/time and numerical formats as they should be, so I can't figure this one out.

    I also tried importing into an Access spreadsheet; the data read in fine, but when I attempt to query it through Excel, I get the same problems.

    Any other thoughts?

    Thanks.
    ~Thomas

    P.S. - FYI, what I'm doing here is creating a database in SAS, and exporting to a public directory as a .csv file. There is an auto FTP set up in the Excel workbook that automatically transfers the database from a UNIX server and posts the data into an Excel spreadsheet, then formats the date values to be read by Excel properly. Once all the auto-processing is complete, the user is allowed to query the data file.

  4. #4
    MrExcel MVP
    Join Date
    May 2002
    Posts
    14,211
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)

    Default

    Probably missing the point here, but if you're using SAS, why not use PROC DBLOAD to export directly to excel- you can then control all the formatting behaviour directly....

    Paddy

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •