I’m attempting to convert an excel to a csv. However, in doing so, the numerical format changes to scientific notation, and removes leading zeros. Is there a way to convert the data table values to strings before moving them to a csv? Also, alternative ideas are welcome.
From what I have noticed is that whatever the value looks like in Excel when you save it to CSV, is how the value will be in CSV.
So you can try changing column width and/or Cell Formats first before saving it to CSV.
You can also try manipulating the data table outside of Excel then use Write CSV or Write Text File (with .CSV extension). That way Excel is not autoformatting large numbers on you.
I use Append to CSV and it won’t change the format, still leave zero in some account number starting with zero.
Only for date, you may need to specify the format in the excel using TEXT(value,format_text) function before the dt is read.