That was a huge pain point in a previous work that I did.
Just to be clear - you can then save the xls/xlsx to csv and the csv won't contain the leading apostrophe, but will show the whole number.
The issue here was that there's no way to let Excel know that this value in this CSV file is not a number. The only way around it that I know of is to stick with xlsx, which has its own pain points.
What you can do is put 2 apostrophes in front of the number in xls/xlsx and save it to csv. The conversion will drop one apostrophe and when you open the csv it will still have one apostrophe in front of it followed by the whole number. Then you can use a formula like =RIGHT(A1,LEN(A1)-1) to remove the leading apostrophe. The number generated by the formula appears as a proper number again and is usable in calculations.
No, you can set the import to treat it as a text field. It is really easy and this should not be a problem. The import can define field by field what it should be treated as (most often used with dates).
The real issue here is that Excel doesn't respect quotes on a CSV file for some inane reason. That is a bug on Microsoft's end.
Another way is to rename the .csv file to be .txt instead, and load it in normally. This triggers the Text Import Wizard upon opening, and the rest is as above.
Also to do this programmatically, one can use the VBA function Workbook.OpenText, and specify the data types via the FieldInfo parameter.
I guess the modern equivalent is SuperUser stack exchange?
So far there's this, which doesn't answer the question: http://superuser.com/questions/355108/csv-error-on-phone-num...
And this, which converts numbers into text, but maybe there's an option to keep them as regular numbers not scientific notation?:
http://superuser.com/questions/586306/save-data-exactly-at-o...
> To do this, open Excel first.
> Click on Data > Import from text.
> You will get a window where you'll have to pick the import type. Choose Delimited then next.
> In the next screen, check only comma, then next.
> On this screen, click the first column in the preview box, scroll to the last column. Hold SHIFT and click the last column. This should make all the columns 'black' (you actually selected all the columns).
> Now, click the radio button Text. After that, click Finish and OK to get your data as you wanted it to be!