조회 수 12952 댓글 0
?

단축키

Prev이전 문서

Next다음 문서

+ - Up Down Comment Print
?

단축키

Prev이전 문서

Next다음 문서

+ - Up Down Comment Print
sqlite3 -header -csv my_db.db "select * from my_table;" > out.csv

This makes it a one-liner.

Also, you can run a sql script file:

sqlite3 -header -csv my_db.db < my_script.sql > out.csv

Use sqlite3 -help to see the list of available options.



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 csv
sqlite> .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 on
sqlite> .mode csv
sqlite> .once c:/work/dataout.csv
sqlite> 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".


Dreamy의 코드 스크랩

내가 모으고 내가 보는

List of Articles
번호 분류 제목 날짜 조회 수 추천 수
313 PHP Header for XML content in PHP file 2014.06.02 12354 0
312 Android tag 없이 repo sync 후 특정 tag 만 당겨오기 2014.09.18 12498 0
311 LINUX linux 버전 확인하기 2013.03.29 12517 0
310 C# DataGridView 컨트롤 Tutorial 2014.02.19 12542 0
309 Python C, Python and swig on Windows with Visual Studio 2014.05.29 12608 0
308 Android git 기초 - 원격 저장소 만들고 로그인하기, 소스 되돌리기 2016.10.29 12615 0
307 Python 유용한 Python 함수 및 기능들 2014.04.30 12632 0
306 일반 cmd.exe 쉘 환경변수 추출 및 처리 2014.03.20 12648 0
305 C# How to speed adding items to a ListView? 1 2012.12.12 12756 0
» Android SQLite database를 csv로 저장하기, 읽어오기 2015.03.04 12952 0
303 개념 EXIF - Exchangeable Image File Format(교환 이미지 파일 형식) 1 2013.11.19 12985 0
302 C# 사용자 정의 이벤트(event) 예제 2013.08.08 13018 0
301 C# C# 자주 쓰는 코드 2012.12.10 13038 0
300 Pi 라즈베리 파이 B+ GPIO file 2015.07.12 13062 0
299 Pi 아두이노 레오나르도 보드 기본 사용법 2016.11.22 13107 0
목록
Board Pagination ‹ Prev 1 ... 9 10 11 12 13 14 15 16 17 18 ... 34 Next ›
/ 34

나눔글꼴 설치 안내


이 PC에는 나눔글꼴이 설치되어 있지 않습니다.

이 사이트를 나눔글꼴로 보기 위해서는
나눔글꼴을 설치해야 합니다.

설치 취소

Designed by sketchbooks.co.kr / sketchbook5 board skin

Sketchbook5, 스케치북5

Sketchbook5, 스케치북5

Sketchbook5, 스케치북5

Sketchbook5, 스케치북5