Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

I export listview data to csv file. There is a column, 'Contact No'. This column's data type is 20 field size of text in MS access. Opening this csv with Notepad, it shows the actual data value, 12345678901234567890. But opening it with MS Excel, it shows 12345678901234500000. :O

Why are the last 5 characters changed to all zero? In Excel, I re-format this column to 'Custom' or 'Number' category format. After this, it shows 12345678901234500000. If not re-format, normally it shows 1.23457E+19.

share|improve this question

migrated from stackoverflow.com Jun 17 '11 at 8:21

This question came from our site for professional and enthusiast programmers.

    
I already read through this - stackoverflow.com/questions/137359/excel-csv-number-cell-format. I'm still finding the solution without doing extra steps to user. –  soclose Jun 17 '11 at 6:05
    
This question is not in the least bit programming-related. Should be moved to Superuser. –  Jean-François Corbett Jun 17 '11 at 6:45
1  
Just to clarify. Is the problem you have with the Access or Excel? Is it a problem in viewing the data on the Access side? Are you importing to Access from Excel or is the file linked? It seems everyone suggests making the content a string but you have a problem with that in Access, but Access will allow strings, so what is the issue? –  datatoo Jun 17 '11 at 17:13
    
Currently, this issue is about opening csv file in excel. I just imagined about importing to access in future. –  soclose Jun 19 '11 at 4:44
    
Dear All, I follow this way ="..." Excel CSV - Number cell format. Because currently I just focus that user can see all value in excel, not consider importing to ms access. Thank you, All. –  soclose Jun 22 '11 at 3:15

7 Answers 7

One suggestion is to put an apostrophe in front of the number when you export the data to CSV format. So instead of 12345678901234567890 export '12345678901234567890.

share|improve this answer
2  
Using a single quote in front, it works. But importing this csv to ms access, system need to remove it :( –  soclose Jun 17 '11 at 5:08

Excel is treating the data as a floating point number, which it is able to approximate as 1.23457E+19.

Floating point does not give 20 digits of precision.

It is a limitation of Floating Point. See this page

share|improve this answer
1  
My data is just numbers only, not including any decimal point. Does Excel treat it as floating? –  soclose Jun 17 '11 at 5:18
2  
Well, it's a number but there are too many digits for Excel to consider it a simple integer, so it's treated as floating point. well.. that may be a simplified explanation, but support.microsoft.com/kb/158071 says 15 digits is the maximum precision in Excel, so you were never going to get 20 digits. That's why people have suggested making it a string. –  pavium Jun 17 '11 at 6:51
    
Hi pavium, is 15 digits for telephone number enough for worldwide usage? If so, I will suggest my user to accept this 15 digits only? Thank you. –  soclose Jun 19 '11 at 4:48
    
It may well be enough - I'm not an expert on International telephone standards. But using a number rather than a string might be problematic as @SpliFF pointed out, because numbers with leading zeroes (used where I am, for international calls and mobiles) will lose those leading zeroes. I think it's better to use a string to represent a telephone number. –  pavium Jun 19 '11 at 6:48

Firstly, you don't want to treat this as a number because 0123 would become 123 which is a totally different phone number. Wrapping the values in double quotes when you export should be enough to make Excel treat the column as text.

I've noticed in the past that Excel provides very little control over the CSV import process so depending on your requirements you could consider using VBA for more control over the import, or post-processing the CSV, or using Open Office.

share|improve this answer
    
Putting them in double quotes, it works. But importing this csv to ms access, system need to remove these quotes :( –  soclose Jun 17 '11 at 5:06
1  
Are you looking for a solution for importing to Access, or Excel - or both? If you just want it to import into Access, do you experience any issues with the original csv, i.e. without opening in Excel? –  Diem Jun 17 '11 at 18:07
    
@soclose even in Access phone numbers should be treated as string/varchar –  mbx Mar 15 '12 at 9:26

If you open a CSV file from the explorer window (by double-clicking on the file), Excel will attempt to automatically determine the fields for you.

If you open Excel, then open the CSV file you have the option of choosing the delimiters and field types yourself. This would allow you to set you 'Contact No' as text, and it will import correctly.

share|improve this answer
    
I open this csv with MS Excel 2003. There is no wizard appearing to choose the delimiters. User wanna open without doing this. how to fix? –  soclose Jun 17 '11 at 4:26

The answer, as has been suggested here already, is that the value has to be a string.

This is only an attempt to help you understand why. This means it will be treated as a string in Access as well, which you now seem to be concerned with. The best way to see the difference and maybe understand what is being said is to format some different cells in an excel sheet.

Forma one as general format, one as a number, and one as text. (Format>Cells>Number Category) Then type a 20 character number such as 99999999999999999999. Save the file as csv format and open in notepad to see the difference. Preceding an entry with ' just tells excel the cell format in another way.

You mention exporting listview data to csv, so you must control the listview data which you want to save as text, not numeric. Access will not have a problem with this ever, unless you try to use the values as numbers, but they appear to be used as identifying numbers, not values.

What is the source of the listview data? Handling the cell format before the data is entering your sheet should solve your problem.

share|improve this answer

Just use "=0123" in the CSV file, quotes included. Beware that re-saving this file as another CSV will undo the =" ... " part.

There is also an HTML way if you want to use a simple table as your spreadsheet which will probably preserve its state when re-saved, just that it's no longer CSV:

<TD style="mso-number-format:@;">0123</TD>

Don't use the single quote (') trick to solve the import issue because it is only applicable when editing fields while you're already in Excel. Stated differently, if you try to import a CSV file with a field of '0123, you'll end up with an actual single quote in your string field too.

share|improve this answer

From my experiments Excel will auto-detect the format of a CSV field unless you specify it like

="Data Here"

20,       5.5%,      "0404 123 351", "3-6",  ="123"
[number]  [percent]  [number]        [date]  [string]  <-- how Excel interprets

I'm not sure about how MS Access will interpret the above fields, however.

share|improve this answer
    
That last column should be "=""123""" otherwise it's badly formed. And Access 2003 will read the first field as a number and the rest as text, unless you tell it otherwise. It fails on the last one with a parsing error, but if the field is corrected as suggested then Access will read it in as the string ="123" - it doesn't make any attempt to interpret the formula. –  DMA57361 Aug 3 '11 at 9:25

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.