Using awk and SQLite

I have recently had the opportunity to use awk and sqlite on a project that I’m working on.

My first thought was that awk should be able to do that. I found some code that someone else did that parses quote and comma delimited.

I started off installing sqlite on cygwin.

After getting sqlite installed, I felt that I needed some test data in order to work with so I did a search and found this site:

https://www.briandunning.com/sample-data/

I grabbed the free file for testing.

The file is comma delimited file that has these fields:

  • First Name
  • Last Name
  • Company
  • Address
  • City
  • County (where applicable)
  • State/Province (where applicable)
  • ZIP/Postal Code
  • Phone 1
  • Phone 2
  • Email
  • Web

Here I used the SQLite command line to issue the command to create the table.

I attempted to import the csv file but got an error.

I can tell that the records are from a Macintosh system because the file has a carriage return record delimiter.

so I wanted to view a hexdump of the file.  I used cat, tr , head and hexdump.

You can see in the HEX dump below that the file now has hex 09 line feeds.

The problem is how to handle quote comma delimited files. I did a search to see if someone already had a solution for that and found this:

AWK CSV Parser

The code contains a function called parse_csv(). You can look at the usage of the parameters on the link above. The important part is how to call this function.

I’m using AWK to convert the CSV file into a pipe delimited file.

I then imported the data into the database.

I’m seeing the first row has the column titles. I’ll look into how to import without that line later.