September 6, 2024 at 5:08 am
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
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 6, 2024 at 2:02 pm
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.
September 6, 2024 at 2:03 pm
Thanks Phil for your quick response and I attached the file which has steps.
September 6, 2024 at 2:10 pm
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 6, 2024 at 5:26 pm
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
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
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
September 6, 2024 at 6:13 pm
Thanks Phil. Appreciate for your fast respsone.
September 6, 2024 at 6:23 pm
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
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
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