sqlite的csv的匯入，匯出

標籤：

-----------------------------------以下這段，是十分容易百度到的，但它不是csv的匯入匯出的適用方法，更適合格式化輸出，說白了就是方便人看，它會給長文本的欄位資料添加空格和適當截斷。這些對於需要後續程式處理的匯出操作是致命的。

匯入
命令: .import
sqlite> .import 檔案名稱 表名
注1: 不要忘了開頭的點
注2: 這條語句不能用分號結束. 非SQL不需要分號結束.
注3: 需要查看預設的分隔字元separator. 必須一致. 如果不一致可能導致sqlite欄位分割錯誤.
查看分隔字元使用命令 .show , 如果不一致可直接修改, 比如:
sqlite>.separator ","
將分隔字元轉為逗號.
舉例1:
將檔案a.txt中的資料匯入表 tab_xx. (a.csv中欄位以逗號分割)
sqlite> .separator ","
sqlite> .import a.txt tab_xx
sqlite>
匯入結束.

匯出
實現方式: 將輸出重新導向至檔案.
命令: .output
sqlite> .output a.txt
然後輸入sql語句, 查詢出要導的資料. 查詢後,資料不會顯示在螢幕上,而直接寫入檔案.
結束後,輸入
sqlite> .output stdout
將輸出重新導向至螢幕.
舉例2:
將 tab_xx 中的資料匯出到檔案a.txt
sqlite> .output a.txt
sqlite> select * from tab_xx;
sqlite> .output stdout
匯出完畢.

如需導成csv格式，直接將檔案a.txt換成a.csv即可

-----------------------------------根據官網的documents 應該用以下方法

CSV Import

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

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

sqlite> .mode csvsqlite> .import C:/work/somedata.csv tab1

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.

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.

sqlite> .header onsqlite> .mode csvsqlite> .once c:/work/dataout.csvsqlite> SELECT * FROM tab1;sqlite> .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 that the first row of the resulting CSV file will contain column labels. If column labels are not desired, set ".header off" instead. (The ".header off" setting is the default and can be omitted if the headers have 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 console. In the example above, that line causes the CSV content to be written into a file named "C:/work/dataout.csv".

The final line of the example (the ".system c:/work/dataout.csv") has the same effect as double-clicking on the c:/work/dataout.csv file in windows. This will typically bring up a spreadsheet program to display the CSV file. That command only works as shown on Windows. The equivalent line on a Mac would be ".system open /work/dataout.csv". On Linux and other unix systems you will need to enter something like ".system libreoffice /work/dataout.csv", substituting your preferred CSV viewing program for "libreoffice".

sqlite的csv的匯入，匯出