Maximum Number Of Rows Exceeded - Different Countries?

[swecs]

Hi there, we're trying to open a CSV file in Calc that has about 3,000 lines, but when we get the error message: maximum number of rows exceeded, excess rows not imported.

I know this can happen if there is a problem with the file, but the thing is the provider of the file can open it fine in Calc with no error message. We are using the same settings (Character Set: Unicode UTF-8, Language: English USA), the only difference is that they are located in the USA, we are in Australia. We can email the same file back to them and they can open it fine, but we get an error message. We have tried on 4 different computers (Mac and Windows) but always get that message.

Does anyone know of a reason why someone would be able to open the file, but we can't?

Cheers
Graeme

[Zizi64]

can you upload the CSV file here?
(We can try it to open in various versions of the AOO and LO on many op.sys, and with many locale settings.)

[RoryOF]

Differing input parameters? tabs instead of commas or something like that.

[swecs]

Sure! Attached...

G

[swecs]

Differing input parameters? tabs instead of commas or something like that.

Sorry, no, I should have mentioned that we are both only using Commas as the separators. In fact I've attached here a screenshot of the parameters we're using.

Cheers
G

[RoryOF]

Only loaded 574 lines for me. Can you not get them to send file in .ods format?

[swecs]

Unfortunately this is a report that is produced by an Inventory management system. So basically it outputs in CSV format, that's it. No other options available.

So did you get the error message about excess rows? The file actually has 1116 rows.

[coray80]

at row 133 the inventory goes off to the right (missing new line character??)
I grabbed whats there into a new sheet
543 lines
plus original 573
1116 rows

[swecs]

Yes, mine cuts off at Row 573 too.

The weird thing is, the tech team who provides the report don't get any errors at all, Calc opens up the full 1116 rows without any error message, using the same settings. So we can't work out why some people can, some people can't.

[coray80]

change your text delimiter to single quote!

[RoryOF]

Single quote works OK, coray80. One might need to Find and Replace to remove double quotes

[swecs]

OK. The report provider has their text delimiter as double quotes and it works fine, but yes if we change it to single quotes then it does open without the error message.

Doing a Find and Replace for double quotes though won't really work for us, as the file already has some double quotes in it, which need to remain in there when the file gets re-imported.

G

[Zizi64]

Your attahed forum.csv file works for me in my (Hungarian) LO4.4.7 with Hun or Eng(UK) settings (1116 rows has been loaded from Variant ID value 1002 to 3484 and the header row too. The ID colunm has not a continous string of numbers, there are missing numbers in the series),

but it not work in my AOO 4.1.2 (the importing procedure stopped with "The maximum lines exceed..." Error message).

Edit: The Text delimiter was double quotes in the import procedure of both softwares

forum_LO.ods

(87.06 KiB) Downloaded 128 times

Workaround tip:
Try the LibreOffice

[keme]

The culprit is in row 133, double quote inside string using a "C-style" backslash escape, instead of the "BASIC-style" approach (insert two consecutive double-quotes) which is used elsewhere. This throws string delimiter pairing off track, but it doesn't cause major errors until several hundred rows down.

Replace the \" with "".

Why it is not an error for everyone, I don't know. Perhaps some versions of Calc support the backslash-escape on imports, or something to do with regex handling?
Import dialog insists on Korean character set. Can't say how that should mean anything in this case, though...

[swecs]

The culprit is in row 133, double quote inside string using a "C-style" backslash escape, instead of the "BASIC-style" approach (insert two consecutive double-quotes) which is used elsewhere. This throws string delimiter pairing off track, but it doesn't cause major errors until several hundred rows down.

Replace the \" with "".

Why it is not an error for everyone, I don't know. Perhaps some versions of Calc support the backslash-escape on imports, or something to do with regex handling?
Import dialog insists on Korean character set. Can't say how that should mean anything in this case, though...

Thanks. I'll feed that back to the report provider, although I'm guessing they can't/won't change the way their system exports to CSV. And yes very strange as to why this doesn't happen to everyone. My guess is that we won't be able to use Calc for these reports and will have to find another program.

Cheers
G

[keme]

A simple batch job should suffice if you have a command line tool to replace text inside plaintext files.

Consider:
https://sourceforge.net/projects/findandreplace/
... or something else ...
https://www.smashingmagazine.com/2009/0 ... -reviewed/

I am reluctant to using GUI tools for automated batch processing. Old dog/new tricks, you know how the saying goes...
However, there are tools available:
http://www.rlvision.com/genius/about.asp

Note that I haven't tried those downloads.
A sourceforge download (first link above) typically displays prominent "download" links which you should avoid, just wait for the 5 sec. countdown.
For the downloads in the other links above, proceed with due caution...

[B Marcelly]

There are two consecutive errors in the original csv, line 133 and line 134.

• Line 133 \"
• Line 134 \"

Bad typing of inches

Ecran001.png (11.69 KiB) Viewed 3965 times

In each case the person filling data wanted to indicate a length in inches.
Probably from the same person, other errors like &xxx; appear in lines 167, 174, 631, 654, 908, 1045.
This is not a bug of automatic encoding, but wrong inputs from a human (copy/paste from html ?).

[keme]

The \" does not cause any problem with import in this case, because it does not contain any double-quote character. However, depending on further processing it may be a problem with future "human interpretation".

The find/replace workaround I suggested above is a "hit and miss" strategy. With each new set of data you need to examine the result carefully to determine whether errors occurred. Optimally, the configuration at the source should be changed to avoid the potential export/import ambiguities. Hence, you need text delimiters and/or field separators which will never occur within the data. The default text delimiters are single and double quote, both of which commonly occur as measurement (feet and inches) as well as punctuation. You can insert other characters for both purposes.

E.g.: If no semicolons will ever appear in the text fields, they can use semicolon separators. Similarly, underscore might be used to delimit text content.

Failing that, there are other characters rarely used in plain text, such as the pipe character ("|", aka vertical bar), unknown/generic currency symbol ("¤", aka "lozenge") and caret ("^", aka circumflex accent or "hat").

Edit: Come to think of it... Circumflex and caret are actually different characters even though the appearance is almost identical (truly identical with some fonts), so perhaps not the best choice.