Import and export of SQLite CSV

-----------------------------------The following paragraph, is very easy to Baidu to, but it is not a CSV import and export of the applicable method, more suitable for formatted output, white is convenient to see, It adds spaces and appropriate truncation to the long text field data . These are fatal for export operations that require subsequent program processing.

Import
Command:. Import
sqlite>. import FileName Table name
Note 1: Don't forget the beginning of the point
NOTE 2: This statement cannot end with a semicolon. Non-SQL does not require a semicolon to end.
Note 3: You need to see the default delimiter separator. Must be consistent. If the inconsistency may cause the SQLite field to be split incorrectly.
View delimiter use commands. Show, if inconsistencies can be directly modified, such as:
Sqlite>.separator ","
Convert the delimiter to a comma.
Example 1:
Import the data from the file a.txt into the table tab_xx. (fields in A.csv are separated by commas)
sqlite>. Separator ","
sqlite>. Import A.txt tab_xx
Sqlite>
End of import.

Export
Implementation: Redirects the output to a file.
Command:. Output
sqlite>. Output A.txt
Then enter the SQL statement to query the data to be directed. After the query, the data is not displayed on the screen and is written directly to the file.
When you are finished, enter
sqlite>. Output stdout
Redirects the output to the screen.
Example 2:
Export the data in tab_xx to a file a.txt
sqlite>. Output A.txt
Sqlite> select * from Tab_xx;
sqlite>. Output stdout
The export is complete.

To convert the file a.txt into a CSV format, you can change it to a.csv directly.

-----------------------------------According to the official documents of the website should use the following methods

CSV Import

Use the '. Import ' command to import CSV (comma separated value) data into an SQLite table. The ". Import" command takes arguments which is the name of the disk file from which CSV data was to be read and the NA Me of the SQLite table into which the CSV data are to be inserted.

Note that it was important to set the "mode" to "CSV" before running the ". Import" command. This was necessary to prevent the command-line shell from trying to interpret the input file text as some other format.

. Mode CSV . Import C:/work/somedata.csv tab1

There is cases to consider: (1) The table "TAB1" does not previously exist and (2) Table "TAB1" does already exist.

In the first case, when the table does isn't previously exist, the table is automatically created and the content of the FIR St row of the input CSV file is used to determine the name of the "the" the columns in the table. In other words, if the table does not previously exist, the first row of the CSV file was interpreted to being column names an D 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, was assumed to BES a Ctual content. If The CSV file contains an initial row of column labels, that row would be read as data and inserted into the table. To avoid this, make sure that table does not previously exist.

CSV Export

To export an SQLite table (or part of a table) as CSV, simply set the ' mode ' to ' CSV ' and then run a query to extract the Desired rows of the table.

. Header on . Mode CSV . Once C:/work/dataout.csv SELECT * from TAB1; . System C:/work/dataout.csv

In the example above, the '. Header on ' line causes column labels to be printed as the first row of output. This means the first row of the resulting CSV file would contain column labels. If column labels is not desired, set ". Header off" instead. (the ". Header off" setting is the default and can omitted if the headers has not been previously turned on.)

The line '. Once FILENAME' causes all query output to go into the ' named file instead of being printed on the Conso Le. In the example above, then line causes the CSV content to is written into a file named "C:/work/dataout.csv".

The final line of the example (the ". System C:/work/dataout.csv") had the same effect as double-clicking on the C:/work/da Taout.csv file in Windows. This would typically bring up a spreadsheet program to display the CSV file. That command is only works as shown on Windows. The equivalent line on a MAC would is ". System Open/work/dataout.csv". On Linux and other UNIX systems you'll need to enter something like ". System Libreoffice/work/dataout.csv", Substitutin G your preferred CSV viewing program for "LibreOffice".

Import and export of SQLite CSV