Memory needed

[okn...@gmail.com]

Hi, I am trying to clean a csv file that has 300K rows. I have config 16G in my server allocated to Open Refine. I'm able to upload the file but it won't show any columns. How much memory it's needed for a file this size in rows? Thanks for the help.

[Ettore Rizza]

Hi,

It depends on the number of columns, but 16Gb is certainly more than enough. I am working on a file of 3 million rows with 12Gb. The problem is probably elsewhere.

[Martin Magdinier]

Otto,

I agree with Ettore, 16GB of RAM should be enough. 

What is the format of the import (csv, xls) ?  (I got issue loading poorly formatted csv previously)

What is the total size of your file? How many columns does it has?

--
You received this message because you are subscribed to the Google Groups "OpenRefine" group.
To unsubscribe from this group and stop receiving emails from it, send an email to openrefine+unsubscribe@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[okn...@gmail.com]

Hi Martin and Ettore,

 I have upgraded to Open Refine version 2.8 and still, the issue remains. It is taking forever to load the columns. my file is cvs type and yes it is poorly formatted.-Drefine.memory=31744M Any idea?

To unsubscribe from this group and stop receiving emails from it, send an email to openrefine+...@googlegroups.com.

[Martin Magdinier]

Otto, 

Some check you can do:

• encoding of your csv 
  
• delimiters are consistent through the file (comma tab or something else). 
  
• cell enclosing with quote are consistent through the file
  

I will suggest doing that with the command line or a text editor that will not crash when you load the file. 

If you have access to the system that generated the data, I will see if you can get a dump in another format

Martin

To unsubscribe from this group and stop receiving emails from it, send an email to openrefine+unsubscribe@googlegroups.com.

[Ettore RIZZA]

(Maybe stupid question) are you sure you have a 64-bits Java installed, as indicated in the documentation? 

The 32-bits version cannot go beyond 4GB RAM, even if you specify 36Gb in -Xmx.

[Otto Knoke]

Martin and Ettore, yes I have 64-bits Java. Which text editor you recommend for that large file? Yes, it’s comma separated.

[Ettore Rizza]

If there is no privacy concerns, could you send me the zipped file at ettorerizza[at]gmail.com ? (note : I'm not Cambridge Analytica ^^ )

[Thad Guidry]

Use line based importer and then manually "Add new column based on" and split on comma char and as you do create each column make facets to spot outlying problem rows.

If not utf8 needed (potentially doubles memory needed) then use ascii as encoding. Although later this might be regrettable if you did really have utf8 chars. Just check for utf8 chars in file first with other tools or use our special facet on your line based rows before splitting and it shows counts of utf8 chars...then just import again with utf8 encoding if that is the case.

Lots of other tips but try those first.

Ettore we should make a wiki page for this info !

-Thad

[okn...@gmail.com]

It took 5 hours to display the columns. Now it's impossible to work on Open Refine. Temporarily my sysadmin has allocated 32G of memory to Open Refine.

[Thad Guidry]

If you could privately share this file with us on Google drive (use my email address) that would be most welcome. We could see where potential issues are and perhaps make improvements in the future for what we find are the reasons causing you difficulty. 

Thanks,

-Thad

[Ettore Rizza]

@thad : I was able to reproduce the issue. Indeed, it comes from commas inside text fields. Simplest solution: open the file with LibreOffice (which has a better CSV parser) and export it in xlsx. 

[Ettore Rizza]

Mmh, looks like the parsing is not perfect even with LibreOffice.

[Otto Knoke]

Tks Ettore.

[Ettore RIZZA]

Not sure the result is good enough. I will try something else this evening.

To unsubscribe from this group and stop receiving emails from it, send an email to openrefine+unsubscribe@googlegroups.com.

For more options, visit https://groups.google.com/d/optout.

--
You received this message because you are subscribed to the Google Groups "OpenRefine" group.

To unsubscribe from this group and stop receiving emails from it, send an email to openrefine+unsubscribe@googlegroups.com.

[Otto Knoke]

Thanks to Thad, Ettore and Martin,

 

I did as instructed by Ettore to open the CSV file and save it as xlms. That solved my problem of loading and seeing the columns in Open Refine. It’s a parsing issue from the source when the CSV file it was generated. Thanks a million for aiding me.

[okn...@gmail.com]

I found the issue in my file. Plese see the below line

221802223","Apr/30/2018","96689917","96689917","CHEVROLET"," SPARK",2015,"BISAGR.INF.PUER.DL.I","LOWER HINGE","24,119.20","","","","","Not Awarded","Not Awarded","0.00","0.00","","0.00","","","Taller: Tres Erres SA Suc Tibas Telefono: 22429090 Direccion: 250m N y 75m E del cruce de Llorente de Tibas frente al edificio Alfredo Sasso, San Juan, Tibas, San","KL1CM6CD6FC796724"

 

Then Year does not have Quotes (") on both sides. Now everything is in one column. I have 262,404 rows with this issue. How can I fix it? Then I will do a split on every ","? You guys have been great!

[Ettore Rizza]

Re Otto,

Your file is badly formatted, it will not be easy to parse as csv. Here is a regex that should do the trick for 99% of the rows.

I suggest using it to replace commas with something else, for example |||. This little Jython script should help you (use "Transform", then select "Pyhon / Jython"):

import re

regex = re.compile(r'("(?:[^"]|"")*"|[^,"\n\r]*)(,|\r?\n|\r)')

return re.sub(regex, r"\1|||", value)

The result will be something like this :

1.

"221802223"|||"Apr/30/2018"|||"KNPL0"|||"KNPL0"|||"CHEVROLET"|||" SPARK"|||2015|||"PORTAPLACA DEL."|||""|||""|||""|||""|||""|||""|||"Not Awarded"|||"Not Awarded"|||"0.00"|||"0.00"|||""|||"0.00"|||""|||""|||"Taller: Tres Erres SA Suc Tibas Telefono: 22429090 Direccion: 250m N y 75m E del cruce de Llorente de Tibas frente al edificio Alfredo Sasso, San Juan, Tibas, San"|||"KL1CM6CD6FC796724"

You can then use the separator (|||) to split your unique column. If the result is right for you, you can delete the leading and trailing quotation marks by going into "All", then "Transform", then using this GREL formula:

value.replace(/(^"|"$)/, '').trim()

(Don't be scared if the screen goes grey or black during the operation)

Good luck !

[Otto Knoke]

Hi Ettore,

 

Your solution worked like a charm. Now the file is 99.5% clean. You are the man!