0

I had been facing this abnormal issue quite a while and yet to debug the real reason behind this why CSVs act so weird when they work with long numbers like Mobile Numbers. suppose, I was working with a CSV file which had one column with values as Mobile numbers. column--> msisdn:

017110*****

013181*****

etc.

after editing the file (such as adding another column) I see the whole CSV file gets corrupted like: msisdn:

01700000000

01300000000

I guess a lot of Business Intelligence guy face this issue time to time. We already have a solution to work with .xlsx file or text file which is actually a way around solution not an actual problem solver. Excel files are way too time consuming to be dumped from Databases!

Any suggestion to mitigate this weird CSV behavior?

Md. Rezaul Karim
  • 97
  • 1
  • 6
  • CSV files are just text files with a different extension, so I'm guessing this is an issue with whatever program you're using to view/open the CSV file in – dwb Mar 28 '21 at 15:13
  • What program are you using to manipulate the CSV files ? If you are using Excel (typically .CSV may be assigned by Microsoft to Excel), it could be that Excel tries to be "friendly" and see your number as a large number. Another variant ie phone numbers turning into something like 1.341837e9 (converted into scientific notation). – MyICQ Mar 28 '21 at 15:35
  • @dantechguy but there should be a definite explanation of this weird behavior with long numbers (integers). I want to know that – Md. Rezaul Karim Mar 28 '21 at 18:47
  • @MyICQ yes I was using Excel. Excel could see that as long number but how come 8 of my digits are directly converted to 0 being a total corrupted file. I have seen this many a lot times but didn't have a clue – Md. Rezaul Karim Mar 28 '21 at 18:49
  • As this seems to be related to excel, Stackoverflow is probably the wrong place to ask. I'd recommend taking a look at [SuperUser](https://superuser.com/) StackExchange – dwb Mar 28 '21 at 18:56
  • What do you mean with editing (in excel or in the csv file)? One possibly solution could be to import the columns form the text file as text. When you use general excel sometimes try to suggest other format such as number or date. If you import them as text they will have their original values from the source. Another explanation can be in which format you save the .csv file. – Wizhi Mar 28 '21 at 20:46
  • You don't show your code or representative source data, making a specific explanation difficult. In general, though, if you **open** a CSV file using Excel, Excel will intepret values that consist of digits as numbers. Excel's numeric precision is about 15 digits and numbers outside of this range will be rounded, or converted to scientific notation. The workaround, if you are using Excel, is to **import** the file. That process allows you to tell Excel that the column is Text, and then the long integers will not be altered. – Ron Rosenfeld Mar 29 '21 at 00:53

0 Answers0