Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2008
    Posts
    2

    Unanswered: Maintain Leading Zeros when exporting to .csv using SQL Server 2000 DTS

    I am trying to export the result of a select into a .csv file using SQL Server 2000 DTS. The data for varchar fields has leading zeroes in the database, which is very much required in the csv file.

    But, the .csv file trims the leading zeroes. How do we force to maintain the same data as in source?

    I had used Text File Destination Connection as the destination, with the below options
    File Extension: .csv
    File Format: Delimited
    File Type: ANSI
    Text Qualifier: Double Quotes ("")
    Row Delimiter: {CR}{LF}
    Column Delimiter: comma

    Source Data: 0123
    Target Data (Requirement): 0123

    The data in .csv: 123 (This is the issue)

    When I open this file in a Text Editor, I do see the data in double quotes..."0123".

    Thanks in advance.
    Last edited by LearnSQL; 01-13-08 at 09:23.

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Quote Originally Posted by LearnSQL
    The data in .csv: 123 (This is the issue)

    When I open this file in a Text Editor, I do see the data in double quotes..."0123".
    Contradictory statements. Please clarify.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Are you, perchance, opening the CSV file in Excel - which is foolish and displays it contrary to the file format?

    If the zeroes are in the file then there's no problem.
    George
    Home | Blog

  4. #4
    Join Date
    Jan 2008
    Posts
    2
    I opened it in Excel and found the data trimmed.

    But, in notepad, it appears fine.

    So, I guess to open a CSV file in Excel and look at the data is not correct.

  5. #5
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Yuppers

    Excel tries to be all clever and lovely... but in reality it is a poor misguided fool of a CSV opening tool.
    George
    Home | Blog

  6. #6
    Join Date
    Jul 2003
    Location
    Amsterdam, Nederland
    Posts
    450

    Format / Number decleration

    Excel automaticly sees 000000001234 as a number and will strip the leading zero's. As you would do with a currency.

    If you want to see a .csv file as it is allways open it with a note/textpad
    like app. and never with a spreadsheet app.
    That will allways try to convert something into something it's not.
    Try to type 6-12 in excel cell and it will try to change it to a date (6 Dec.), while you want it to be a office/home-address-number.


    Same if you try to import a .csv in to acces and import 0000123 as a numer it will strip of the leading 0000

    if you still want to open it in excel then first open a empty sheet,
    and import your file and difine each column first

  7. #7
    Join Date
    Jun 2004
    Location
    Long Island
    Posts
    696
    In Excel do a format->cells->text for the entire sheet, then paste
    and you will see the leading zeros if they exist.

    or simply type filename from DOS prompt and you will see the true contents of the file

Posting Permissions

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