SQLite3 : Import et export d une table

Export en CSV

To export data from the SQLite database to a CSV file, you use these steps:

Turn on the header of the result set using the .header on command.
Set the output mode to CSV to instruct the sqlite3 tool to issue the result in the CSV mode.
Send the output to a CSV file.
Issue the query to select data from the table to which you want to export.

The following commands select data from the customers table and export it to the data.csv file.

>sqlite3 c:/sqlite/chinook.db
sqlite> .headers on
sqlite> .mode csv
sqlite> .output data.csv
sqlite> SELECT * FROM customers;
sqlite> .quit

In the first scenario, you want to import data from CSV file into a table that does not exist in the SQLite database.

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.
Second, the sqlite3 tool import data from the second row of the CSV file into the table.

To import the c:\sqlite\city.csv file into the cities table:

sqlite> .mode csv
sqlite>.import c:/sqlite/data.csv table_name

In the second scenario, the table is already available in the database and you just need to import the data.

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.

The following commands import the city_without_header.csv file into the cities table.

sqlite> .mode csv
sqlite> .import c:/sqlite/city_no_header.csv cities

Export et import en mode INSERT

Create text file and place following statements there :

.mode insert  
.output insert.sql  
select * from TABLE where STATEMENT; -- place the needed select query here  
.output stdout

Feed this file to sqlite3:
```
SQLite> .read insert.sql
```

Publié le : 09-02-2023 - 20:58