Professional Web Applications Themes

Leading Zeros not being read from Excel Query - Coldfusion Database Access

We are having an odd problem when trying to read in a query from Excel. What we are trying to do: We have a page where users will upload an Excel file through a form, we make a copy of the file on the server and then try to read in that data to write to our database. Our Problem: Our CFQUERY reads almost everything correctly. The only problem is that one of the fields in the Excel file contains a nine digit number (SSN). This nine digit number will sometimes contain leading zeros (ex. 012345678). In excel, if the ...

  1. #1

    Default Leading Zeros not being read from Excel Query

    We are having an odd problem when trying to read in a query from Excel.

    What we are trying to do: We have a page where users will upload an Excel file
    through a form, we make a copy of the file on the server and then try to read
    in that data to write to our database.

    Our Problem: Our CFQUERY reads almost everything correctly. The only problem
    is that one of the fields in the Excel file contains a nine digit number (SSN).
    This nine digit number will sometimes contain leading zeros (ex. 012345678). In
    excel, if the cell is a number, it tries to strip the leading zeros. If you
    change the cell to a text field, it displays in excel exactly the way it was
    typed in. However, a CFDUMP of our query reveals that those numbers that start
    with a zero, comes in as "empty string".

    What we have tried: We have tried putting in alphanumeric strings that start
    with zero (ex. 0ABCD) with the same result. We tried changing the formatting in
    excel to a special/custom type which only changed the display in Excel. We
    tried saving the file out as various versions (95, 97, etc.).

    Has anybody else had this problem or have any ideas??

    cmkelly Guest

  2. #2

    Default Re: Leading Zeros not being read from Excel Query

    That is an old issue with querying excel files that goes way back. If you
    search the old forum archives for EXCEL there are many posts on this topic. If
    you cannot access the archives through this site, you might want to try
    searching through google groups instead.

    The basic problem is caused by how the driver determines the column data
    types. When you query a database table, each column has a single, known, data
    type. When you query an Excel file, the spreadsheet columns do not have data
    types. The driver has to guess each column's type by using the values in that
    column. Sometimes it guesses correctly and other times it doesn't.

    These links do not refer to cfquery, but the do describe the problem
    [url]http://support.microsoft.com/default.aspx?scid=kb;en-us;257819[/url]


    There were a number of suggested work arounds, such as:
    - Setting the column in excel to type text and re-pasting the values
    - Setting the "rows to scan" value
    - Using a setting called IMEX
    - Saving the work sheet as a text file and using a "schema.ini" file when
    reading in the file

    I don't remember the specifics about which method... if any of them ...
    actually worked, but this may at least give you somewhere to start looking.

    Good luck



    mxstu Guest

  3. #3

    Default Re: Leading Zeros not being read from Excel Query

    This is an old topic but here's how I was able to solve it (my problem was with
    zip codes starting with a 0 and some of the data being 10 digit zip codes):
    select * from `sheet_name_here$` IN 'C:\SomeDirectory\myfile.xls' 'EXCEL
    5.0;IMEX=1'

    the 'EXCEL 5.0;IMEX=1' forced everything to be a string i believe.

    belie Guest

Similar Threads

  1. preserve leading zeros in alphanumeric values from doc.info
    By Bruce_Hensley@adobeforums.com in forum Adobe Acrobat SDK
    Replies: 0
    Last Post: February 15th, 12:12 AM
  2. Disappering leading zeros
    By elvis is dead in forum Macromedia ColdFusion
    Replies: 0
    Last Post: May 13th, 11:23 AM
  3. CSV file - Leading Zeros
    By Paul Kraus in forum PERL Beginners
    Replies: 5
    Last Post: December 15th, 10:22 PM
  4. Insert leading zeros
    By Matt Palermo in forum PHP Development
    Replies: 2
    Last Post: July 16th, 12:48 AM
  5. CSV for Excel - Problem with Leading Zeros
    By Luke Zhang [MSFT] in forum ASP.NET General
    Replies: 0
    Last Post: June 26th, 01:34 PM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139