Exported phone numbers change to scientific format in .csv

Lena Payne · November 13

Can anyone advise why when we export the phone numbers, some of them convert to the scientific format in .csv

Even when I manually adjust them to the actual phone number, and format the cell as text, they still change the format when saved in csv

Has anyone else had this happen, and what did you do to resolve it?

Austen Brown · November 13

Are you exporting out of WV Query, DBV Query, or DBV Export?

Lena Payne · November 13

https://community.blackbaud.com/discussion/comment/308595#Comment_308595

I've tried DBV Query and DBV Export, thanks

Will Hoban · November 13

Are you opening the files in Excel (or another spreadsheet program) and it is showing "12345678910" as "1.23E+10" in the spreadsheet?

Lena Payne · November 13

https://community.blackbaud.com/discussion/comment/308612#Comment_308612

Yes, I’m exporting into Excel. If I export as a CSV, the phone numbers immediately appear in scientific format, and changing the cell format doesn’t make any difference. If I export as an XLS and then save it as a CSV, Excel automatically converts the phone numbers to scientific notation (e.g. 1.23E+10) when the file is reopened.

Will Hoban · November 13

.xls / .xlsx formatted files contain formatting data about how the contents of cells should be displayed, in addition to the values of the cells (and the formulas / macros / data model etc)

Comma Separated Value (.csv) files don't contain any formatting data, they contain only the values. So how they appear will depend on what software you are using to view/edit the file. Spreadsheet tools (Excel) do okay on some data types and not great on others.

Changing the cell format in Excel will fix the display but it will only retain this "corret" formatting for the next time you open the file if you save it in an Excel format. CSV has no formatting data and as such the next time you open the CSV Excel will resume guessing (badly) at what is in the field and how to display it.

To answer your question about how I resolve this, generally if the output of the RE Export is going to be used and distributed in Excel I just export it in .xls format to start with. If there are too many results for .xls, I export in .csv, fix the formatting in Excel and save as .xlsx to distribute.

If I am working with the CSV data and am planning to use the data as a flat file to import back to RE or into one of our other systems with or without changes, I use a different tool that handles CSV more gracefully to view/edit instead of Excel. (Visual Studio Code is my personal preference)

Aldera Chisholm · November 17

I have made it a habit that instead of clicking on a CSV to open it, I go to excel first. Then, go to Data, Get External Data, and pull in the csv. You can then ensure it comes in how you want it.

Brian Gray · November 17

We're a Google school, so I use Google spreadsheets for many things. I import the CSV directly into the sheet (File > Import, then Upload tab).

In addition to not messing with phone numbers, it also cleanly imports accented characters in names.

Aldera Chisholm · November 17

Hi Brian! Yes, I spend half my time in Google sheets, and half in Excel. For both, I find importing is so much better than opening. It's just a couple of seconds more work, but it makes a big impact.

Lena Payne · November 18

https://community.blackbaud.com/discussion/comment/308679#Comment_308679

@Aldera Chisholm - thank you so much! With a bit of fiddling this worked!

Aldera Chisholm · November 18

https://community.blackbaud.com/discussion/comment/308733#Comment_308733

I'm glad to hear it! I know it's an extra step, but once you get into the habit, you'll find there are a few other nice features to doing it this way as well. :)

Exported phone numbers change to scientific format in .csv

Answers

Categories