3

When I open a csv file in excel, it break up the lines longer than 32760 characters, put the rest on the next line, and delete at least 2 characters in the process.

There is no special characters at the break up place.

Is it a normal behaviour? Where does it come from? Can I change that in any way?

Thanks for help.

Liad
  • 340
  • 4
  • 15
  • 2
    [Excel specifications and limits](https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3#ID0EBABAAA=Excel_2016-2013): Total number of characters that a cell can contain: 32,767 characters – Axel Richter May 15 '17 at 09:46
  • @Axel Richter Thanks for the information, didn't know that. – Liad May 16 '17 at 09:03

1 Answers1

4

Resolve the problem of cell length using the right tool for the job.

Excel has limit for cell length as you described, but it is OK, because Excel has never supposed to be a CSV editor.

I strongly recommend to never use Excel for CSV files. Reason:

It will change CSV values like 0053 into 53 without warning you. This can have serious impact on your data, because especially in ERP systems, values like 0053 are common and leading zeros are often meaningful. Or just think about ZIP codes: You can find ZIP 040 12 converted by Excel into 4012 without warning you. Excel also introduces other unexpected behaviors (from CSV viewpoint) like the one you described in the question.

Use some dedicated CSV editors instead. Some of them are freeware. This will also resolve your problem with cell length and also other potential problems like problems with quotes, with line breaks inside the cells, with character encoding, with column headers etc.

miroxlav
  • 11,796
  • 5
  • 58
  • 99
  • The problem is that I deal with user who are bind to excel for work. When they are loading csv file comma separated, excel don't understand the separator and load everything in the first column. They have to split the data with the convert options, and here some data disappear cause of the excel cell length limit. I just found a solution by switching the windows separator to ',' instead of ';', but I'm not very happy about that, I expected an excel only modification. It still solve my problem btw. Thanks for your answer. – Liad May 16 '17 at 09:03
  • 2
    @Liad: Import the `CSV` file using the [Text Import wizzard](https://support.office.com/en-us/article/Text-Import-Wizard-c5b02af6-fda1-4440-899f-f78bafe41857?ui=en-US&rs=en-US&ad=US) instead of doubleclicking the file. There you can select the delimiter in step 2. Also you can select the column data formats in step 3 to prevent changings like text `00123` to number `123`. – Axel Richter May 16 '17 at 09:14
  • @AxelRichter Didn't know that either, thank you. But my users will have to do that each time they want to read a csv files... Still useful I guess. – Liad May 16 '17 at 09:22
  • @Liad: Yes users have to be introduced about this. But it is the one and only way to properly get `CSV` into `Excel` when users from multiple locales are involved. – Axel Richter May 16 '17 at 09:27
  • @Liad – the decision what to use should depend on who your users are and in what environment they work. In most of cases I would introduce them to some different editor for CSV's than the Excel. It could save them many problems down the road. Why they would need to learn how to work them around in Excel if they can simply avoid them all using different editor? Of course, if you have strict policy against using anything else than Excel, then Excel could be reasonable, but then more training needs to be put to users on how to properly edit CSV files. – miroxlav May 18 '17 at 16:03