I have a situation where I've been given a spreadsheet with bank account numbers which have leading zeros. These are retained in the input since the column is formatted to text.
I have converted the excel to a comma delimited file with the text enclosed in double quote so the zeros are still retained.
So I want to run this through a tmap and output a comma delimited file, which is fine. When I open my output file in notepad the leading zeros are retained & the values are in double quotes. But when I open it in excel the leading zeros are dropped.
Do I need to make my input excel instead of cvs? And how do I get it to retain the leading zeros in my csv output file? I've tried using both string and integer in the tmap and the result is the same
Excel is trying to be smarter than you, and automatically converts field type to numeric. Maybe it's not the best solution, but when I'm trying to open csv file in excel, I'm doing this by Import data from CSV file, where I can choose - Do not detect data types. Then it works fine 🙂
