Using AWK To Process Huge CSV Files
Tags: awk, shell
Published: 2020-07-08 16:17:28

---
title: Using AWK To Process Huge CSV Files
published: true
date: 2020-07-08 15:33:45 UTC
tags: awk, shell
canonical_url: https://www.rasjonell.tech/posts/2020-03-26-awk_to_csv.html
---

<main><article><div>
<header>

</header><ul id="toc">
<li><a href="#problem"><span>1 </span>Problem</a></li>
<li><a href="#approach"><span>2 </span>Approach</a></li>
<li><a href="#solution"><span>3 </span>Solution</a></li>
<li><a href="#conclusion"><span>4 </span>Conclusion</a></li>
</ul>
<h2 id="problem">Problem</h2>
<p>I had 38 separate <code>.csv</code> files each consisted of at least 40K lines of data. Overall there were ≥ 2.6 million rows of information. The problem was that the data was not in the ideal form.</p>
<p>Each file had 5 columns. One of the columns was called <em>birthday</em>. It had a conventional(at least for the most of the world) format: <code>DD/MM/YYYY</code>. However I was going to put this data in a DB(more on that later!) and I needed to have 3 separate properties(ie. <code>{ day: DD, month: MM, year: YYYY }</code>). Also there were some columns that were not useful so I had to get rid of them. In addition, I also wanted to reduce the number of files to one.</p>
<h2 id="approach">Approach</h2>
<p>I know some python and from a couple of data science courses I was introduced to <a href="https://pandas.pydata.org/">Pandas</a>, a well-known tool for data analysis built on top of python. It had a straightforward way of reading, manipulating, and writing csv files. I was going to use that, but didn’t really want to download this tool to use only one of the features and never use it again. So I choose something that comes with nearly all modern unix-like systems. <a href="https://en.wikipedia.org/wiki/AWK">AWK</a> is <em>designed</em> for text processing and much like other programs in the unix-like world it does one thing well and produces fascinating results in regards to simplicity and performance when composed with other unix tools.</p>
<h2 id="solution">Solution</h2>
<p>Now we need to make AWK process <code>.csv</code> files.
AWK uses <em>field separators</em> to split an input into fields. It may either be a character or a RegExp.
So all we need to do to make AWK become a <code>.csv</code> processor is to set FS(field separator) to be <code>","</code>.</p>
<p>We can achieve this using AWK’s <a href="https://www.gnu.org/software/gawk/manual/html_node/Using-BEGIN_002fEND.html#Using-BEGIN_002fEND">Startup and Cleanup Actions</a>.</p>
<pre><code># clean_csv.awk

BEGIN {
  FS = ","
}
{
  print $1
}
END {
}</code></pre>
<p>Then if we pass an input such as <code>one, two, three</code> to the script, it will print <code>one</code>:</p>
<pre><code>$ echo "one, two, three" | awk -f csv.awk

one</code></pre>
<p>Now that we have this, we can finally solve our problems: splitting <code>DD/MM/YYYY</code> column into three separate columns and removing the useless column.
AWK has a <code>split</code> function that takes a field, a new variable, and a separator and puts the separated values into the new variable.</p>
<p>The birthday column was #3 so to create the separate fields we need to make the following modifications:</p>
<pre><code># clean_csv.awk

BEGIN {
  FS = ","
}
{
  # split DD/MM/YYY into an array of values
  split($4, dob, "/")

  # turn these values into separate columns
  total = dob[1] "," dob[2] "," dob[3]

  # finalize the row, omitting the useless #4 column.
  row = $1 "," $2 "," total "," $5

  print row
}
END {
}</code></pre>
<p>This mainly solves most of our problems, except for the first row of the input. <code>.csv</code> files have headers as a set of labels on the first row, thus we need to skip every first line of every <code>.csv</code> file.</p>
<p>In AWK <code>NR</code> and <code>FNR</code> represent the record number. The latter shows the record number of the current file, while the first holds the total count. Since we are going to use this script on more then one file, we’ll stick with <code>FNR</code>.</p>
<p>So the final script looks like so:</p>
<pre><code># clean_csv.awk

BEGIN {
  FS = ","

  # create a new header with correct labels
  print "last_name,first_name,birth_day,birth_month,birth_year,country"
}
{
  if (FNR &gt; 1) { # omit every first line
    # split DD/MM/YYY into an array of values
    split($4, dob, "/")

    # turn these values into separate columns
    total = dob[1] "," dob[2] "," dob[3]

    # finalize the row, omitting the useless #4 column.
    row = $1 "," $2 "," total "," $5

    print row
  }
}
END {
}</code></pre>
<p>At last, to execute this on our 38 <code>.csv</code> files we need to run this command:</p>
<pre><code>awk -f clean_csv *.csv &gt; clean.csv</code></pre>
<h2 id="conclusion">Conclusion</h2>
<p>We read and manipulated over 30 <code>.csv</code> files with more than 2.6 million rows of data combined and reduced them into a single clean file without installing any software. So if you’re faced with a problem, you may not need to install a bloated software to solve it when the solution comes pre-installed with your system.</p>
</div></article></main>