6

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.

5
  • 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, 2011 at 6:05
  • This question is not in the least bit programming-related. Should be moved to Superuser.
    – Jean-François Corbett
    Jun 17, 2011 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, 2011 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, 2011 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, 2011 at 3:15

8 Answers 8

5

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.

1
  • 2
    Using a single quote in front, it works. But importing this csv to ms access, system need to remove it :(
    – soclose
    Jun 17, 2011 at 5:08
3

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

4
  • 1
    My data is just numbers only, not including any decimal point. Does Excel treat it as floating?
    – soclose
    Jun 17, 2011 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, 2011 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, 2011 at 4:48
  • 1
    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, 2011 at 6:48
3

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.

3
  • Putting them in double quotes, it works. But importing this csv to ms access, system need to remove these quotes :(
    – soclose
    Jun 17, 2011 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, 2011 at 18:07
  • @soclose even in Access phone numbers should be treated as string/varchar
    – mbx
    Mar 15, 2012 at 9:26
3

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.

1
  • 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, 2011 at 4:26
3

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.

1
  • 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, 2011 at 9:25
3

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.

1

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.

0

Might be too late here but I've been having the same problem and the best solution I came up with was to use brackets() or dashes - to separate the phone number into multiple sections eg. Country code, area code, and number...

The CSV would look like:

... ,(1)123-123-1234, ...

Rather than pure decimal numbers like:

... ,+11231231234, ...

I tried this because that's how phone numbers look like when they're automatically formatted in Excel.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .