LibreOffice Calc datafile too big...

Questions about applications and software
Forum rules
Before you post please read how to get help
Post Reply
tenfoot
Level 5
Level 5
Posts: 993
Joined: Sun Jun 03, 2007 4:12 am
Location: Waipapa, Bay of Islands, New Zealand

LibreOffice Calc datafile too big...

Post by tenfoot » Thu Sep 05, 2019 5:59 pm

Using LM19.2 Mate and LibreOffice v6.0.7.3

A friend is the patron of the local Citroen Club. He owns a Citroen C6 and is trying to establish how many are registered in New Zealand. After some investigation he found he could download a data file of all vehicles registered in the country but not just of a particular model.

As he is not particularly computer literate he asked me to download the file and extract the data relating to the Citroen C6. The zip file downloaded successfully and, after extraction, was 1.2GB of data. Opening this file with LibreOffice Calc presented a .CSV file of 33 columns. I hid all but four of those columns and the resulting spreadsheet showed 10,485,760 rows of data. Sorting that data on Make and Model revealed only seven Citroen C6 cars registered in New Zealand. This is ludicrous as there are more than that number registered in Auckland, where my friend lives.

I suspect that the problem is the fact that not all data is loaded because of the number of lines and so my question is, simply: Is there any way I can access the lines which have not been loaded? Obviously they are contained within the 1.2GB CSV file but LibreOffice is not able to load them.

The following screenshots show the:
(a) error returned when loading from csv file
Screenshot at 2019-09-06 09-07-48.png
(b) result of search for Citroen C6
Screenshot at 2019-09-06 08-59-31.png
tenfoot
Death does not send a letter giving three week' notice. It arrives when you are eating raspberry sweets (Lars Mytting - The Sixteen Trees on the Somme)

User avatar
Flemur
Level 17
Level 17
Posts: 7079
Joined: Mon Aug 20, 2012 9:41 pm
Location: Potemkin Village

Re: LibreOffice Calc datafile too big...

Post by Flemur » Thu Sep 05, 2019 6:07 pm

Opening this file with LibreOffice Calc presented a .CSV file of 33 columns.
Try in a terminal

Code: Select all

grep -i Citroen filename.csv | grep -i c6
Please edit your original post title to include [SOLVED] if/when it is solved!
Your data and OS are backed up....right?
Mint 19.1 Xfce/fluxbox
Manjaro openbox/fluxbox

tenfoot
Level 5
Level 5
Posts: 993
Joined: Sun Jun 03, 2007 4:12 am
Location: Waipapa, Bay of Islands, New Zealand

Re: LibreOffice Calc datafile too big...

Post by tenfoot » Fri Sep 06, 2019 5:23 pm

Flemur wrote:
Thu Sep 05, 2019 6:07 pm
Opening this file with LibreOffice Calc presented a .CSV file of 33 columns.
Try in a terminal

Code: Select all

grep -i Citroen filename.csv | grep -i c6
Thank you for the solution. You're a hero! The problem is now solved and I've racked up some "brownie" points. Didn't even think of grep as always considered it beyond my limited technical abilities. Note to self: "Take a long look at grep commands and man up to the possibility that you will understand them"
tenfoot
Death does not send a letter giving three week' notice. It arrives when you are eating raspberry sweets (Lars Mytting - The Sixteen Trees on the Somme)

User avatar
Flemur
Level 17
Level 17
Posts: 7079
Joined: Mon Aug 20, 2012 9:41 pm
Location: Potemkin Village

Re: LibreOffice Calc datafile too big...

Post by Flemur » Sat Sep 07, 2019 8:20 am

tenfoot wrote:
Fri Sep 06, 2019 5:23 pm
Flemur wrote:
Thu Sep 05, 2019 6:07 pm
Opening this file with LibreOffice Calc presented a .CSV file of 33 columns.
Try in a terminal

Code: Select all

grep -i Citroen filename.csv | grep -i c6
Thank you for the solution. You're a hero! The problem is now solved and I've racked up some "brownie" points. Didn't even think of grep as always considered it beyond my limited technical abilities. Note to self: "Take a long look at grep commands and man up to the possibility that you will understand them"
Great!
I forgot to mention that if you do this (the > thing) :

Code: Select all

grep -i Citroen filename.csv | grep -i c6 > newfile.csv
you *might* get a valid, new .csv file, containing only the Citroen entries ... assuming the file doesn't need a special header (I haven't used a csv file in years, but I think that'll work).
Please edit your original post title to include [SOLVED] if/when it is solved!
Your data and OS are backed up....right?
Mint 19.1 Xfce/fluxbox
Manjaro openbox/fluxbox

tenfoot
Level 5
Level 5
Posts: 993
Joined: Sun Jun 03, 2007 4:12 am
Location: Waipapa, Bay of Islands, New Zealand

Re: LibreOffice Calc datafile too big...

Post by tenfoot » Sun Sep 08, 2019 4:15 pm

Thanks! I'll give it a go and report back.
tenfoot
Death does not send a letter giving three week' notice. It arrives when you are eating raspberry sweets (Lars Mytting - The Sixteen Trees on the Somme)

tenfoot
Level 5
Level 5
Posts: 993
Joined: Sun Jun 03, 2007 4:12 am
Location: Waipapa, Bay of Islands, New Zealand

Re: LibreOffice Calc datafile too big...

Post by tenfoot » Tue Sep 10, 2019 5:04 pm

Great!
I forgot to mention that if you do this (the > thing) :

Code: Select all

grep -i Citroen filename.csv | grep -i c6 > newfile.csv

you *might* get a valid, new .csv file, containing only the Citroen entries ... assuming the file doesn't need a special header (I haven't used a csv file in years, but I think that'll work).
Delighted to tell you that it worked perfectly, in that only Citroen vehicles were displayed. Again, Thank you for your helpt. It is very much appreciated.
tenfoot
Death does not send a letter giving three week' notice. It arrives when you are eating raspberry sweets (Lars Mytting - The Sixteen Trees on the Somme)

Post Reply

Return to “Software & Applications”