0

I have a .csv file of 6.5 GB. I need to sort the files with 1st, 2nd, 3rd, 4th, 5th and 13th column and need to get the unique records. Also I need to get the duplicate records and redirect it to some other file.

I am using the following command to get the unique lines:

awk -F',' 'a[$1,$2,$3,$4,$5,$13]++' /var/tmp/Data.txt > unique.txt

But the above process is consuming more than 3.5 GB memory and eventually gets terminated. Producing the following error in the log file:

sh: line 1:  7895 Killed                  awk -F',' 'a[$1,$2,$3,$4,$5,$13]++' /var/tmp/Data.txt > unique.txt  

How can I produce the required output?

  • 2
    In general I would not recommend parsing .csv files with regular expressions. Some files have quoted values, and sometimes span multiple lines. Some implementations of Excel produce semi-colon separated values. IMHO you better use Python/Perl/Ruby and a real CSV parsing library, that will save you time. – Timo Jan 22 '14 at 7:13
  • What operating system and version are you using? Why are you surprised about the memory usage — each line creates a new entry in a table and it adds up. You mention sorting, are you doing this on a sorted file? – Gilles Jan 22 '14 at 23:44
  • 1
    Why don't you use sort? Reorder the fields using awk (move field 13 to position 6), then sort -k 1,6. – jcsahnwaldt Feb 1 '16 at 21:38
2

For each line where the specified fields are not unique, you store some data in memory that contains the specified field plus a small overhead. So unless you have a lot of duplicates or the other fields are very large, it's unsurprising that a 6.5GB file would cause more than 3.5GB to be used.

If the file is already sorted, you don't need to store all that data, only to compare adjacent lines.

awk -F ',' '
    {current = $1","$2","$3","$4","$5","$13}
    previous == current {print}
    {previous = current}
'

or

sed -n -e 'x' -e 'G' -e '/^\(.*)\n\1$/ { s/\n.*//; p; }' \

If the file isn't sorted, sorting is likely to be the fastest way of getting the information. Typical sort implementations are good with very large files.

0

Try : Did you missed ! before a ?

  $ awk -F',' '!a[$1,$2,$3,$4,$5,$13]++' < /var/tmp/Data.txt > unique.txt

If your OS or any commands you use to parse it is not 64 bits nor compiled or designed to support such kind of architecture, you will surely have some problem to handle a 6.5Gb files (process won't be able to adress the required memory). if 32bit : 2^32 which is around 4Gb will be the limit.(could be even less with signed interger for example limit will be 2Gb) man largefile Man largefile may also give you some clue.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.