0

How to load a CSV file into the table using the console? The problem is that I have to somehow omit the headers from the CSV file (I can not delete them manually).

Qlikeers
  • 3
  • 1
  • 5
  • Possible duplicate of [Import CSV to SQLite](https://stackoverflow.com/questions/14947916/import-csv-to-sqlite) – AJS Apr 16 '19 at 11:41

2 Answers2

1

From the sqlite3 doc on CSV Import:

There are two cases to consider: (1) Table "tab1" does not previously exist and (2) table "tab1" does already exist.

In the first case, when the table does not previously exist, the table is automatically created and the content of the first row of the input CSV file is used to determine the name of all the columns in the table. In other words, if the table does not previously exist, the first row of the CSV file is interpreted to be column names and the actual data starts on the second row of the CSV file.

For the second case, when the table already exists, every row of the CSV file, including the first row, is assumed to be actual content. If the CSV file contains an initial row of column labels, that row will be read as data and inserted into the table. To avoid this, make sure that table does not previously exist.

It is either/or. You will have to outsmart it.

Assuming "I can not delete them manually" means from the csv, not from the table, you could possibly sql delete the header line after the import.

Or: Import into a temp table in the target database, insert into target table from the temp table, drop the temp table.

Or:

  • connect to an in-memory database
  • import the CSV into a table
  • attach the target database
  • insert into target table from the imported in-memory table
DinoCoderSaurus
  • 6,110
  • 2
  • 10
  • 15
  • Thanks! Definitely option 2. That's because I have all data fields defined in the table (text, date, numeric, etc.). By default, everything is loaded as TEXT (to the new table) - right? – Qlikeers Apr 17 '19 at 13:16
1

just add option --skip 1, see https://www.sqlite.org/cli.html#importing_csv_files

Zim
  • 1,528
  • 1
  • 10
  • 6
  • Welcome to StackOverflow. While this code may solve the question, including an explanation of how and why this solves the problem would really help to improve the quality of your post, and probably result in more up-votes. Remember that you are answering the question for readers in the future, not just the person asking now. Please edit your answer to add explanations and give an indication of what limitations and assumptions apply. – Federico Baù Jan 08 '21 at 14:59