Forum Discussion
Convert txt to excel
Hello,
I´m trying to convert a txt file which has numbers to an excel file so I can review the information easily. I'm using excel 2016 so I go to data, get data, from txt file and the information pops up. The problem is that the numbers change. For example in the original txt a number could be 4551932613 and then it populates in a different format like 4.5519E+18 and when I try to convert it to normal numbers using cell formatting, it changes the original number adding zeros to the end. Any suggestions or tips please? Thanks
Hi Gabriel,
Excel has 15 digit limit in representing the numbers, your ID:s are 19 character long. The workaround not to convert such values to numbers but keep them as text.
Importing your file don't directly load it into Excel, select Transform data first
In opened query editor at right pane remove Changed Type step
result will be like this
After that close the editor with loading table to Excel sheet. Please see attached
5 Replies
- Try converting the txt file to excel using 3rd party converters and it will keep its original formatting of numbers. You can try Doc Converter for this: https://www.coolutils.com/TotalDocConverter
Hi Gabriel,
It converted in another number, not format. 4551932613 is 4.5519E+9, not 4.5519E+18.
Could you please attach sample of your txt file with couple of records?
Hi Sergie, Thanks for the assitance. Here´s the original file .
Hi Gabriel,
Excel has 15 digit limit in representing the numbers, your ID:s are 19 character long. The workaround not to convert such values to numbers but keep them as text.
Importing your file don't directly load it into Excel, select Transform data first
In opened query editor at right pane remove Changed Type step
result will be like this
After that close the editor with loading table to Excel sheet. Please see attached
