Loading multiple CSV files with SQLite

Question

I'm using SQLite, and I need to load hundreds of CSV files into one table. I didn't manage to find such a thing in the web. Is it possible?

Please note that in the beginning i used Oracle, but since Oracle have a 1000 columns limitation per table, and my CSV files have more than 1500 columns each one, i had to find another solution. I wan't to try SQLite, since i can install it fast and easily. These CSV files have been supplied with such as amount of columns and i can't change or split them (nevermind why).

Please advise.

Answer 1

I ran into a similar problem and the comments to your question actually gave me the answer that finally worked for me

Step 1: merge the multiple csv's into a single file. Exclude the header for most of them but write down the header from one of them in the beginning.

Step 2: Load the single merged csv into SQLite.

For step 1 I used:

$ head -1 one.csv > all_combined.csv
$ tail -n +2 -q *.csv >> all_combined.csv

The first command writes only the first line of the csv file (you can choose whichever one file), the second command writes the whole document starting from line 2 and therefore excluding the header. The -q option makes sure that tail never writes the file name as a header.

Make sure to put all_combined.csv in a separate folder, or in some distributions, it will be included recursively!

To load into SQLite (Step 2) the answer given by Hot Licks worked for me:

 sqlite> .mode csv
 sqlite> .import all_combined.csv my_new_table

This assumes that my_new_table hasn't been created. Alternatively you can create beforehand and then load, but in that case exclude the header from Step 1.