Page 1 of 2 12 LastLast
Results 1 to 15 of 26

Thread: Truncation of leading zeros

  1. #1
    Join Date
    Jun 2004
    Posts
    14

    Truncation of leading zeros

    I have a problem while importing data from Excel to SQL Server.The leading zeros in data get truncated.Even if I try and change the excel data column as 'Text' and copy paste the data back into the Text column, the problem persists.Does any one have any thoughts about this problem?

  2. #2
    Join Date
    Sep 2002
    Posts
    5,938
    Did you load them into column with char data type in sql table?

  3. #3
    Join Date
    Jun 2004
    Posts
    14
    I had loaded them into varchar datatype

  4. #4
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    When importing both XLS and CSV to SQL Server works for me. Leading zeroes are not truncated.

    Can you see the leading zeroes in Excel?

    If problem still exist, please upload a sample Excel sheet that you are using and the SQL Server table structure.

  5. #5
    Join Date
    Jun 2004
    Posts
    14
    I have attached the sample excel file and the script to create the SQL table.Hope this helps.
    Attached Files Attached Files

  6. #6
    Join Date
    Jun 2004
    Posts
    14
    Can't we upload .xls files in the forum?

  7. #7
    Join Date
    Jun 2004
    Posts
    14
    Lemme try this.I have the sample excel and SQL table script in this zip file.
    Attached Files Attached Files

  8. #8
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    I dont see any leading zeroes in the Excel file.

  9. #9
    Join Date
    Jun 2004
    Posts
    14
    This is strange I have leading zeros in all the rows of the ID column.The data of concern to me is the 'ID' column in the excel sheet.When i import this excel file to the SQL table I see the ID column with values like '35000000' , '217123456' as opposed to seeing it as '0035000000' and '0217123456'.

  10. #10
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    You are right. ID column has leading zeroes.

    When I checked the properties of the Column A (ID) (Format cells ) it was Custom with value "0000000000"

    When importing it truncates all the leading zeroes.

    Trying to use disconnect edit. Will let you know.

  11. #11
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

  12. #12
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    You can use OpenRow


    insert into VendSpendsDirectNew ([SAP_vndr_nbr]
    )
    select ID FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
    'Excel 8.0;DATABASE=c:\sample.xls', 'Select * from [Sheet1$]')


    refer:
    http://www.databasejournal.com/featu...le.php/3331881

  13. #13
    Join Date
    Jun 2004
    Posts
    14
    Thanks! Will try this and let you know the results

  14. #14
    Join Date
    Jun 2004
    Posts
    14
    When I tried the OpenRowset insert, the following was the error that I obtained:

    Server: Msg 7415, Level 16, State 1, Line 1
    Ad hoc access to OLE DB provider 'Microsoft.Jet.OLEDB.4.0' has been denied. You must access this provider through a linked server.

  15. #15
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Copy the file to you server's c:\ and then query it

Posting Permissions

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