{"id":1105,"date":"2016-05-28T22:37:15","date_gmt":"2016-05-28T15:37:15","guid":{"rendered":"https:\/\/web.archive.org\/web\/20230708205754\/http:\/\/www.sqlitetutorial.net\/?page_id=1105"},"modified":"2022-04-03T15:06:31","modified_gmt":"2022-04-03T08:06:31","slug":"sqlite-import-csv","status":"publish","type":"page","link":"https:\/\/web.archive.org\/web\/20230708205754\/https:\/\/www.sqlitetutorial.net\/sqlite-import-csv\/","title":{"rendered":"Import a CSV File Into an SQLite Table"},"content":{"rendered":"\r\n

Summary<\/strong>: in this tutorial, you will learn various ways to import CSV data into an SQLite table using sqlite3 and SQLite Studio tools.<\/p>\r\n\r\n\r\n\r\n

Importing a CSV file into a table using sqlite3 tool<\/h2>\r\n\r\n\r\n\r\n

In the first scenario, you want to import data from CSV file into a table that does not exist in the SQLite database.<\/p>\r\n\r\n\r\n\r\n

  1. First, the sqlite3 tool creates the table. The sqlite3 tool uses the first row of the CSV file as the names of the columns of the table.<\/li>
  2. Second, the sqlite3 tool import data from the second row of the CSV file into the table.<\/li><\/ol>\r\n\r\n\r\n\r\n

    We will import a CSV file named city.csv<\/code> with two columns: name and population. You can download it here for practicing.<\/p>\r\n\r\n\r\n\r\n

    Download the city.csv file<\/a><\/p>\r\n\r\n\r\n\r\n

    To import the c:\\sqlite\\city.csv<\/code> file into the cities table:<\/p>\r\n\r\n\r\n\r\n

    First, set the mode to CSV to instruct the command-line shell program to interpret the input file as a CSV file. To do this, you use the .mode<\/code> command as follows:<\/p>\r\n\r\n\r\n\r\n

    sqlite> .mode csv<\/code><\/pre>\r\n\r\n\r\n\r\n
    Second, use the command .import FILE TABLE<\/code> to import the data from the city.csv<\/code> file into the cities table.<\/p>\r\n\r\n\r\n\r\n
    sqlite>.import c:\/sqlite\/city.csv cities<\/code><\/pre>\r\n\r\n\r\n\r\n
    To verify the import, you use the command .schema<\/code> to display the structure of the cities<\/code> table.<\/p>\r\n\r\n\r\n
    sqlite> .schema cities\r\nCREATE<\/span> TABLE<\/span> cities(\r\n\u00a0 \"name\"<\/span> TEXT<\/span>,\r\n\u00a0 \"population\"<\/span> TEXT<\/span>\r\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    To view the data of the cities<\/code> table, you use the following SELECT<\/code> statement.<\/p>\r\n\r\n\r\n
    SELECT<\/span> \r\n   name<\/span>, \r\n   population\r\nFROM<\/span> \r\n   cities;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    In the second scenario, the table is already available in the database and you just need to import the data.<\/p>\r\n\r\n\r\n\r\n
    DROP<\/span> TABLE<\/span> IF<\/span> EXISTS<\/span> cities;<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    CREATE<\/span> TABLE<\/span> cities(\r\n  name<\/span> TEXT<\/span> NOT<\/span> NULL<\/span>,\r\n  population INTEGER<\/span> NOT<\/span> NULL<\/span> \r\n);<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    If the table already exists, the sqlite3 tool uses all the rows, including the first row, in the CSV file as the actual data to import. Therefore, you should delete the first row of the CSV file.<\/p>\r\n\r\n\r\n\r\n
    The following commands import the city_without_header.csv<\/code> file into the cities<\/code> table.<\/p>\r\n\r\n\r\n
    sqlite> .mode csv\r\nsqlite> .import c:\/sqlite\/city_no_header.csv cities<\/code><\/span>Code language:<\/span> SQL (Structured Query Language)<\/span> (<\/span>sql<\/span>)<\/span><\/small><\/pre>\r\n\r\n\r\n
    Import a CSV file into a table using SQLite Studio<\/h2>\r\n\r\n\r\n\r\n
    Most SQLite GUI tools provide the import function that allows you to import data from a file in CSV format, tab-delimited format, etc., into a table.<\/p>\r\n\r\n\r\n\r\n
    We will use the SQLite Studio to show you how to import a CSV file into a table with the assumption that the target table already exists in the database.<\/p>\r\n\r\n\r\n\r\n
    First, from the menu choose tool menu item.<\/p>\r\n\r\n\r\n\r\n
    \"SQLite<\/figure>\r\n\r\n\r\n\r\n
    Second, choose the database and table that you want to import data then click the Next<\/strong> button.<\/p>\r\n\r\n\r\n\r\n
    \"SQLite<\/figure>\r\n\r\n\r\n\r\n
    Third, choose CSV as the data source type, choose the CSV file in the Input file<\/strong> field, and choose the ,(comma)<\/strong> option as the Field separator<\/strong> as shown in the picture below. Then click the Finish<\/strong> button to import the data.<\/p>\r\n\r\n\r\n\r\n
    \"SQLite<\/figure>\r\n\r\n\r\n\r\n
    In this tutorial, you have learned how to use the sqlite3 and SQLite Studio to import data from a CSV file into a table in the SQLite database.<\/p>\r\n
    \n\t
    \n\t\t
    Was this tutorial helpful ?<\/div>\n\t\t
    \n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t Yes <\/span>\n\t\t\t<\/button>\n\n\t\t\t\n\t\t\t\t\n\t\t\t\t\t<\/path>\n\t\t\t\t<\/svg>\n\t\t\t\t No <\/span>\n\t\t\t<\/button>\n\t\t<\/div>\n\t<\/header>\n\n\t
    \n\t\t
    \n\t\t\t
    <\/div>\n\t\t\t
    Second, use the following CREATE TABLE<\/a> statement to create the table cities<\/code>.<\/p>\r\n\r\n\r\n
    First, drop<\/a> the cities<\/code> table that you have created.<\/p>\r\n\r\n\r\n