Post reply

SSIS - Leading zeros omitted from OLEDB source to Flat FIle Destination Connect

  • September 6, 2024 at 5:08 am

    #4454512

    Hi,

    I am trying to export the data from OLEDB source connection to Flat FIle destination (.CSV) and for the SSN field which character length 9 is omitting leading zeros

    after the data load. Please let me know the correct soultion.

    Original Data: 001234567

    Required Data: 001234567

    Omitted Data with leading zero's issue: 1234567

    I have used data conversion by converting it to STR and WSTR and also derived column with below code but still having the same issue.

    Derived COlumn Logic: RIGHT(REPLICATE("0",9) + SSN,9) or RIGHT("000000000" + [Copy of SSN],9)

    Appreciate if anyone can help resolve te issue.

    Thanks,

    Sandeep

  • September 6, 2024 at 9:49 am

    #4454575

    As long as the datatype of the SSN remains as string throughout the export, this should not be happening.

    Somewhere in your process, it is being treated as an INT and that's why the leading zeros are being dropped.

    If you add Viewers in your data flow, you should be able to work out the point at which this is happening.

    It's difficult to be more specific than that, given the information you have provided.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • September 6, 2024 at 2:02 pm

    #4454655

    I am using DT_STR with length (9) from beginning till last step of the process.

     

    Step 1: OLEDB Source

     

    Select RIGHT(REPLICATE('0', 9) + CAST(SSN AS VARCHAR(9)), 9) AS SSN

    From dbo.XYZ

     

    Step: 2 Flat File Destination:

     

    Data viewer is showing leading zeros but the final output in .csv it’s omitting leading zeros.

    Attachments:
    You must be logged in to view attached files.

  • September 6, 2024 at 2:03 pm

    #4454660

    Thanks Phil for your quick response and I attached the file which has steps.

  • September 6, 2024 at 2:10 pm

    #4454661

    Just a quick question: are you opening the .CSV in a text editor (eg, Notepad ++)?

    If you open it in Excel, Excel will 'helpfully' guess the datatype to be int and strip the zeros off for you.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • September 6, 2024 at 5:26 pm

    #4454718

    I opened the file in text editor and able to see leading zeros and when I tried to open the same .csv file in excel leading zeros are missing.

    I  am not sure how my client is processing .csv file but they complained about leading zeros missing in .csv file.

    Note: I am also able to see in data viewer with correct data including leading zeros. For some reason, client wants to see when they open the file in excel.

    Thanks,

    Sandeep

     

  • September 6, 2024 at 5:54 pm

    #4454724

    Prove to the client that the zeros are there. Do not use Excel as a data import tool, it messes with the data before you even see it.

    The absence of evidence is not evidence of absence.
    Martin Rees

    You can lead a horse to water, but a pencil must be lead.
    Stan Laurel

  • September 6, 2024 at 6:13 pm

    #4454729

    Thanks Phil. Appreciate for your fast respsone.

  • September 6, 2024 at 6:23 pm

    #4454730

    Can you export it as .txt not .csv? This might encourage them to use a more appropriate tool. You have no control over their ability to use Excel.

    If they insist on handling the file with Excel, the only way I have managed to solve the problem is by keeping a template file with the columns explicitly formatted as text. You can copy/rename the template into the destination folder then export directly into the new Excel file. This is a horrible solution, but Excel is the devil.

  • September 6, 2024 at 10:50 pm

    #4455308

    Hi Ed,

    Thanks for your response. Client is insisting me to export the data in .csv only.

    It works when I export the data using .txt with delimiter but this client requirement to export in .CSV.

    I did create temporary folder with same xyz.csv file by changing the SSN column to TEXT and overwriting the main file but still the same issue. I tried to open the main file and see SSN column as General instead of text even though I copied using temporary file with SSN column as Text.

    When I opened the .CSV file in text editor it is perfectly showing leading zeros. For Example : "ABC","XYZ","001234567" but when I open the same .CSV directly which uses Excel then it shows as "ABC","XYZ","1234567" omitting leading zeros.

    Note: I used derived column with below statement and SSN shows as '123456789' with quotes in .CSV file and if you replace with empty values then 001234567.

    ISNULL(SSN) || LEN(TRIM(SSN)) == 0 ? "" : "'" + RIGHT(REPLICATE("0",9) + (DT_WSTR,9)SSN,9) + "'"

    Thanks,

    Sandeep

     

     

     

     

     

  • September 6, 2024 at 11:29 pm

    #4455316

    then file is working as expected and you are getting the leading zeros - if you need them to be opened in excel as text you need to do a few more things - a potential one is to enclose that SSN is a combination of "=" +doublequotes (e.g. xxx,="SSN",xxx) which will trick Excel to treat that column as a string instead of an int ( but if this file will be read by another system it will fail without further processing), the other is to use a macro to open the file, where you have full control of how each column is defined.

    Excel is very picky and in many cases you don't have any control of HOW it decides to interpret data - so if your client wishes to open the file in Excel (by double click on it, or file->open) then tell that is not possible and point out that there are other ways to do it - but it will involve some excel code/vba/powerquery.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply