Increase size limit of data import from csv into Excel

Solution 1:

There is a weird bug in Excel. I'll explain here how to avoid (not fix, just avoid) it, and maybe it will fixed soon by MS Office programmers. Maybe it even WAS fixed in Excel 2013, I did not open it yet.

So, this is the problem.

The maximum length of the text cell is 32767, and it's OK.

The maximum length of the number cell is 255, and it's OK.

But if you have a cell that calls to number parser, fails and then calls to text parser, here will be the big mess.

For example:

...,"This is a cell with 30,000 characters........",...

will work.

But if you'll put a little minus at the start, as in

...,"-This is a cell with 30,000 characters........",...

you'll see only 255 first text characters, because EXCEL thinks it's a negative number.

Hope it helps.

Solution 2:

I ran into this problem with a csv file that had some long text fields that sometimes began with a dash. It seems that Excel's import tries to process the field as a number because of the dash, and the number import trims it to 255 characters. When that fails, it imports it as text, but only the first 255 characters.

To avoid the problem, I had to import the csv instead of just opening it. In the import wizard, I selected the column that was having trouble and switched its type from General to Text. Then Excel won't even try to parse it as a number, and I get the full column width.