Page 2 of 2 FirstFirst 12
Results 16 to 26 of 26

Thread: Truncation of leading zeros

  1. #16
    Join Date
    Jun 2004
    Posts
    14
    That is exactly what I had done
    . I copied my excel file to C: and ran the the following queries:

    CREATE TABLE [dbo].[VSD] (
    [SAP_vndr_nbr] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [vndr_nm] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Min_Invc_Pmt] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Max_Invc_Pmt] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Avg_Invc_Pmt] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Sum_Invc_Pmt] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Count_Checks] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Min_Pay_Date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
    [Max_Pay_Date] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
    ) ON [PRIMARY]
    GO





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

  2. #17
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Are you SA on the SQL Server?

  3. #18
    Join Date
    Jun 2004
    Posts
    14
    no i'm a user

  4. #19
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Either one of these will do

    1. Talk to you DBA about giving access to you reg this.

    from BOL
    "OPENROWSET can be used to access remote data from OLE DB data sources only if the DisallowAdhocAccess registry option is explicitly set to 0. When this option is not set, the default behavior does not allow ad hoc access."

    2. Talkto you DBA in creating Linked server to this XL file

    3. Create ActiveX script in DTS package to read the EXCel sheet row by row.

    http://support.microsoft.com/default...86&Product=sql

    4. Import Excel into a temp table and then prefix it with '00000'

    eg:
    create table x123(id varchar(10))
    insert into x123 select '35000000'
    insert into x123 select '217123456'
    insert into x123 select '1712'

    update x123 set [id]=right('00000000000'+[id],10)

    select * from x123

    --results
    id
    0035000000
    0217123456
    0000001712

  5. #20
    Join Date
    Jun 2004
    Posts
    14
    ok i tried importing the excel file into my local server on my laptop and the import was successful, but when i queried the table the results shows id column with values in exponential form.I have attached a screenshot of the query analyzer for you.
    Attached Files Attached Files

  6. #21
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Did you change that column to a TEXT data type
    ?

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

    should show you what you are importing

  7. #22
    Join Date
    Jun 2004
    Posts
    14
    I have tried changing the ID column to text format, but the moment i do that, the leading zeros get truncated in my excel sheet itself, even before my import into SQL table.

    I tried a couple of options, like CSV, Tab delimited, but nothing worked.The only think tht worked was your option 4 which was creating the temp table and concatenating leading zeros and another one was to create a blank spreadsheet and format the ID column as 'Text" and then re-key in the ID data and then import to SQL.However re-keying in 70,000 records is really tedious.

  8. #23
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254
    Then Option 4 it is.

  9. #24
    Join Date
    Jun 2004
    Posts
    14
    Thanks a bunch.Appreciate your help!

  10. #25
    Join Date
    Jun 2004
    Posts
    1

    Re: Truncation of leading zeros

    Originally posted by Raveen
    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?
    The problem is that the zeroes don't actually exist in Excel. The visible leading zeroes you see in your spreadsheet are only appearing because of the custom number format of 0000000000.

    So you must at some point add the leading zeroes yourself, as other people have suggested. You can modify the spreadsheet to actually contain the zeroes if you like. Add a new column B in the worksheet (so it is right next to the ID column) and insert this formula in cell B2: "=Right(Rept("0",9)&A2,10)". (Note: the column must be in general or a number format in order for the formula to work.)

    Then copy and paste this or fill down for the whole column. You may either name the column and query it directly (I think) or you can choose Copy, then Paste Special|Values to turn the formulas into 'real' text. You can choose to change the data type to text at this point so any further data entry can allow the leading zeroes to 'stick.'

    It's probably easiest just to add the zeroes in SQL Server, after all. :-)

  11. #26
    Join Date
    Jun 2004
    Posts
    14
    Thanks! Learnt something new about excel's capabilities, but again agree with your point that it is easier to do it in SQL.

Posting Permissions

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