Export Content of a SQLite3 Table in CSV

export SQLite empty tables to csv with headers

One option is to utilize pragmatable_info to get the column names, and then just append the rows' content:

for i in $tables ; do
    sqlite3 test.sqlite "pragma table_info($i)" | cut -d '|' -f 2 | paste -s -d, > "$i.csv"
    sqlite3 -csv test.sqlite "select * from $i;" >> "$i.csv"
done

Result:

$ cat tblA.csv
ID,Col01
$ cat tblB.csv
ID,Col01
1,AAA
2,BBB

Export content of a SQLite3 table in CSV

It is easy with Sequel and to_csv:

require 'sequel'
DB = Sequel.sqlite
# since Sequel 3.48.0 to_csv is deprecated, 
# we must load the to_csv feature via a extension
DB.extension(:sequel_3_dataset_methods) #define to_csv 
DB.create_table(:test){
  Fixnum :one
  Fixnum :two
  Fixnum :three
}
#Prepare some test data
5.times{|i|
  DB[:test].insert(i,i*2,i*3)
}

File.open('test.csv', 'w'){|f|
  f << DB[:test].to_csv
}

The result is:

one, two, three
0, 0, 0
1, 2, 3
2, 4, 6
3, 6, 9
4, 8, 12

In my test I had problems with line ends, so I needed an additional gsub:

File.open('test.csv', 'w'){|f|
  f << DB[:test].to_csv.gsub("\r\n","\n")
}

If you want the export without the header line, use to_csv(false)

Remarks:

• .to_csv is deprecated since Sequel 3.48.0 (2013-06-01).
  You may use an old version with gem 'sequel', '< 3.48.0' or load the extension sequel_3_dataset_methods).

---

To get support for other seperators and other CSV-features you may use a combination of Sequel and CSV:

require 'sequel'
require 'csv'
#Build test data
DB = Sequel.sqlite
DB.create_table(:test){
  Fixnum :one
  Fixnum :two
  Fixnum :three
  String  :four
}
#Prepare some test data
5.times{|i|
  DB[:test].insert(i,i*2,i*3, '<a href="www.test.com">test, no %i</a>' % i)
}

#Build csv-file
File.open('test.csv', 'w'){|f|
  DB[:test].each{|data| 
    f << data.values.to_csv(:col_sep=>';')
  }
}

Result:

0;0;0;"<a href=""www.test.com"">test, no 0</a>"
1;2;3;"<a href=""www.test.com"">test, no 1</a>"
2;4;6;"<a href=""www.test.com"">test, no 2</a>"
3;6;9;"<a href=""www.test.com"">test, no 3</a>"
4;8;12;"<a href=""www.test.com"">test, no 4</a>"

As an alternative you may patch Sequel::Dataset (modified code from a post of marcalc at Github):

class Sequel::Dataset
    require 'csv'
    #
    #Options: 
    #* include_column_titles: true/false. default true
    #* Other options are forwarded to CSV.generate
    def to_csv(options={})
      include_column_titles = options.delete(:include_column_titles){true}  #default: true
      n = naked
      cols = n.columns
      csv_string = CSV.generate(options) do |csv|
        csv << cols if include_column_titles
        n.each{|r| csv << cols.collect{|c| r[c] } }
      end
      csv_string
    end 
end

SQLITE3 database tables export in CSV

Or use pandas.

import pandas
rows = cur.fetchall()
df = pandas.DataFrame.from_records(rows)

Export SQLite table to csv file with double quotes in python

Change the query:

cur.execute("SELECT * FROM calculation")

to:

sql = """
SELECT
  ID,
  Station_ID,
  '"' || COALESCE(Virtual_Variable, '') || '"' Virtual_Variable,
  Variable_ID,
  Unit_ID,
  '"' || COALESCE(Formula, '') || '"' Formula,
  RoC_Active,
  RoC_Precision,
  RoC_Period_Value,
  RoC_Period_Type,
  RoC_Unit_Value,
  RoC_Unit_Type,
  Datum_Variable_ID,
  '"' || COALESCE(Datum_Timestamp, '') || '"' Datum_Timestamp,
  '"' || COALESCE(Datum_Information, '') || '"' Datum_Information,
  '"' || COALESCE(Constants, '') || '"' Constants
FROM calculation 
"""
cur.execute(sql)

so that all TEXT columns are returned enclosed in double quotes and then export the resultset.

Also change this line:

csv_writer = csv.writer(csv_file,delimiter=";")

with:

csv_writer = csv.writer(csv_file,delimiter=";",quotechar='',quoting=csv.QUOTE_NONE)

Export SQLite3 to CSV with text representation of GUID/BLOB

I found out what my error was - not why it doesn't work, but how I get around it.
So I tried to export my tables as staded in https://www.sqlite.org/cli.html , namely a multiline command, which didn't work:

sqlite3 'path_to_db'
.headers on`
.mode csv
.output outfile.csv
SELECT statement

and so on.

I was testing a few things and since I'm lazy while testing, I used the single line variant, which got the job done:

sqlite3 -header -csv 'path_to_db' "SELECT QUOTE (ID) AS Hex_ID, * FROM Table" > 'output_file.csv'

Of course it would be better if I would specify all column names instead of using *, but this sufices as an example.

Export sqlite3 table to csv or txt file with python

Well, here is the code i've done so far, its very simple and works well:

with open('output.csv', 'wb') as f:
    writer = csv.writer(f)
    writer.writerow(['Column 1', 'Column 2', ...])
    writer.writerows(data)

Thanks to @Dougal from here

SQLite: How do I save the result of a query as a CSV file?

From here and d5e5's comment:

You'll have to switch the output to csv-mode and switch to file output.

sqlite> .mode csv
sqlite> .output test.csv
sqlite> select * from tbl1;
sqlite> .output stdout

Sqlite3 / python - Export from sqlite to csv text file does not exceed 20k

You can try using pandas to load the sql data and then to dump it into a csv. You'd have to install the dependencies (notably NumPy) to use it though. It's really simple then:

import sqlite3
import pandas.io.sql as sql
con = sqlite3.connect('database.db')
table = sql.read_frame('select * from some_table', con)
table.to_csv('output.csv')

---

Related Topics