Mar 22 2018
02:59 AM
- last edited on
Jul 12 2019
11:05 AM
by
TechCommunityAP
Mar 22 2018
02:59 AM
- last edited on
Jul 12 2019
11:05 AM
by
TechCommunityAP
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
Mar 22 2018 04:54 AM
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?
Mar 22 2018 07:18 AM
Hi Sergie, Thanks for the assitance. Here´s the original file .
Mar 22 2018 07:42 AM
SolutionHi 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
Mar 22 2018 10:15 AM
Feb 26 2019 06:36 PM
Mar 22 2018 07:42 AM
SolutionHi 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