Export to Excel Converts String Values to Numbers

[Mvaldesi]

Good afternoon, Gurus and Experts,

I’m not sure if this is the correct section of the forum, but it feels like a “general” question. I’m working with a “big data” prep platform called Datameer; within it, I am able to see/specify/change datatypes. I’ve got a bunch of 20-digit carton numbers, each starting with 4 leading zeros (“0000”,) and Datameer has designated them a “string” format, which is what I want. However, when I go to export from Datameer to Excel (into a *.csv,) Excel converts the carton numbers from the string format to “General,” dropping the leading zeros and listing them in scientific notation. This wouldn’t be a problem as I could normally change the formatting to number with zero decimal places to break the number back out to its true form and add the leading zeros manually, but when I do this, it changes all of the last digits of the numbers to “0.” My short term solution is to add a leading alpha (“X”) to the carton numbers to ensure they’re stay as strings, then find/replace the “X” with an apostrophe, effectively converting the full 20-digit numbers to strings, but I’d like to know why this is happening, and is there a way to prevent Excel from doing it?

Thanks in advance!

[dosydos]

=TEXT(A2,"00000000000000000000")

This will add the leading 0000 to your carton ID even if it shows scientific notation. There are 20 0's in that formula above.

[dosydos]

You could also use this formula

="0000"&A2

[Mvaldesi]

Thank you for your response, but adding the leading zeros isn't my problem. The issue occurs when I export the 20-digit carton numbers in a string format and Excel changes them to numbers ("General" format,) and converting them from the scientific notation back to a legible format, I lose the trailing digit. Example:

- In Datameer, I've got "00007644150013427176" as a String
- It exports to Excel as "7.64415E+15"
- I convert that to a number with zero decimal places, and I get "7644150013427170" where I've lost the leading zeros (which I know how to fix,) but the last digit is now "0," which I CAN"T fix.

[MrShorty]

Exactly how are you importing the data into Excel? Excel will always try to import data as "helpfully" as it can -- which means that if Excel sees something in the import that it can interpret as a number, it will (by default) try to convert it to a number. You will need to do something during the import step to try to prevent or override this default behavior. For example, if you are importing from a text file, there is a step in the text import wizard where you can tell Excel to treat a column as text to override its default behavior of converting anything that looks like a number to a number. How are you exporting from Datameer/importing into Excel -- and what options do you have during that process to override Excel's default behavior?

[Fluff13]

Rather than opening the csv in excel, you need to import it, via the data tab.
Then on the 3rd page of the impost wizard, you can select the relevant column & click "Text".
This should keep the data intact.

[Mvaldesi]

Thank you both, MrShorty and Fluff13; I will have to look at what options I have in Datameer; I just had a co-worker telling me it was behavior within Excel (as you suggest, MrShorty,) so I thought the solution would need to be on that end.

Fluff13, I can't "import" it into Excel because the export doesn't exist until Datameer creates it (the *.csv file.) But perhaps I can export it to a text file, then import it to Excel?

Seems I have some options/experimenting to do; I'm going to leave the thread unsolved for now, but will close it if one of the solutions you've suggested works out. Thanks!

[MrShorty]

I somehow missed that the data was passing through a .csv (comma separate values text) file between Datameer and Excel. Fluff13's suggestion is probably the one you need, just be sure to check the "text" option at the appropriate step in the text import wizard. Another option, if Datameer will allow, is to save as a regular text .txt file. Excel bypasses the text import wizard when opening .csv files, but will bring up the text import wizard when opening .txt files.

[dosydos]

if datameer creates the .csv file.
First open the csv file in Notepad or Wordpad. This will reassure you that datameer is indeed exporting the data correctly.
I believe Fluff was suggesting you open a blank excel then select the Data ribbon. Then select "From Test/Csv" and find your csv file and import it into excel that way using TEXT

[Fluff13]

I believe Fluff was suggesting you open a blank excel then select the Data ribbon. Then select "From Test/Csv" and find your csv file and import it into excel that way using TEXT

I was indeed suggesting that, just obviously didn't do a very good job of it

@Mvaldesi
Thanks for the Rep.

[Mvaldesi]

Thank you all; I figured out how best (if cumbersomely) to do it. Datameer only has three options to export: *.csv, a *.tdsx (a Tableau file type,) or to a *.zip. I had to use the last type, then import from an open session of Excel where, as MrSHorty predicted, I'm able to specify my data types and maintain the integrity of my data. It's a pain, but it works; for all Datameer can do, it really drops the ball in some really basic areas.

Thanks again; marking the thread solved now!

[Fluff13]

Have you seen what dosydos said in post#9?

[Mvaldesi]

I was indeed suggesting that, just obviously didn't do a very good job of it

Haha! No, since your suggestion was perfectly clear to someone other than me, I think the onus of misunderstanding lies with this dimwit right here! Thanks a ton!