Geek Answers Handbook

Import CSV to SQLite WITHOUT table schema

I know that I can import a CSV file into a previously existing table in a sqlite database using:

.import filename.csv tablename

However, is there such a method / library that can automatically create a table (and its schema), so I do not need to manually determine: column1 = string, column2 = int .... etc.

Or maybe we can import everything as a string. To my limited understanding, sqlite3 still treats all fields as a string?

Edit: The names of each column are not so important here (suppose we can get this data from the first line in the CSV file, or they can be arbitrary names). The key is to identify the value types of each column.

+4
source share
3 answers

You told yourself in a comment that its a non-trivial problem for defining column types. (Imagine a million lines that all look like numbers, but one of these lines has Z in it. - Now this line needs to be typed "line".)

Although non-trivial, it is also quite easy to get a 90% work scenario. I will just write a little Python script to do this. Python has a very good library for parsing CSV files, and its interface for sqlite is quite simple.

Just download the CSV, guess and check the column types. Create a create table that encapsulates this information and then issues insert intos . I can not imagine that this takes more than 20 lines of Python.

+1
source

This seems to work fine for me (in sqlite3 version 3.8.4):

 $ echo '.mode csv > .import data_with_header.csv some_table' | sqlite3 db

It creates a some_table with field names taken from the first line of the data_with_header.csv file. All fields are of type TEXT .

0
source

This is a little off topic, but it can help to use a tool that gives you all the SQL functionality in a separate csv file without actually using SQLite directly.

Take a look at TextQL - a utility that allows you to directly query the csv files that use the SQLite engine in memory: https://github.com/dinedal/textql

textql -header -sql "select * from tbl" -source some_file.csv

0
source

More articles:


All Articles