1

I have a spreadsheet of international phone numbers. When I open the file in Python or Excel, it zero's the numbers meaning: "119780000000000" instead of the real number.

The only thing that fixes it is going into Excel, formatting the column "Custom" and selecting "0".

I've tried reading the file into Python as a string:

myfile = "File_Output"
df = pd.read_csv(myfile+'.csv', dtype={'id':int, 'Code':str, 'Person':str, 'Phone Number':str})

This is an example set of rows from the output:


id | Code | Person | Phone Number    |
1  | 100  | Guy    | 119780000000000 |
2  | 200  | Girl   | 115780000000000 |
3  | 100  | Guy    | 113740000000000 |

The zeroes should not be there, it should be a full number. Why is this happening and is there any way to fix this programmatically?

The desired output is the real number:


id | Code | Person | Phone Number    |
1  | 100  | Guy    | 119781212412414 |
2  | 200  | Girl   | 115784564564621 |
3  | 100  | Guy    | 113747645754745 |
Alex
  • 73
  • 6
  • 2
    Please read this [post](https://stackoverflow.com/help/how-to-ask) and provide sample rows of your csv along with your explanation and expected output to improve your chances of getting a response – Balaji Ambresh Jul 23 '20 at 13:12
  • also, bear in mind that CSV does not hold cell formatting; mentioning Excell in this question looks irrelevant – Shadi Naif Jul 23 '20 at 13:16
  • @BalajiAmbresh I believe I've edited it properly. – Alex Jul 23 '20 at 14:19
  • I don't see any representation of your csv file. – Ron Rosenfeld Jul 23 '20 at 14:40
  • I cannot reproduce your problem. In Excel, if I **open** the file, Excel displays the values in Scientific Notation. If your CSV has the correct values, you can **import** it into Excel and specify that column as "Text". – Ron Rosenfeld Jul 23 '20 at 14:53
  • In Pandas, according to the documentation I've read, `str` is setting the datatype to `object`, not to string. That may be your problem. And there are answered questions in this forum as to converting to actual strings (pandas version dependent) which you should be able to adapt to your problem. – Ron Rosenfeld Jul 23 '20 at 15:06
  • @RonRosenfeld this is the issue. There is no solution for this question either: https://stackoverflow.com/questions/58067051/pandas-read-csv-scientific-notation-large-number – Alex Jul 23 '20 at 16:23
  • Apparently you need to do some conversions. Look at **other than the primary** answer to [Pandas reading csv as string type ](https://stackoverflow.com/questions/16988526/pandas-reading-csv-as-string-type) – Ron Rosenfeld Jul 23 '20 at 19:27
  • Have you tried opening a blank Excel and selecting Data > From Text and following the import wizard? – Martin Evans Jul 24 '20 at 15:04

0 Answers0