CODEWITHSUNDEEP
sqlitecsv
SamIAm
SamIAm

Reputation: 2489

How to properly CSV import without quotations in SQLite?

I would like to import a list of email from a CSV file into sqlite. The CSV does not contain quotations yet when I import it into sqlite, it would add quotations to the data. Is there a better way of importing?

So far, I have been doing:

 .mode csv
 .header on
 .import "email.csv" emailDatabase

However, by doing this, when I search emailDatabase, the data has " around the entries.

An example.

The CSV File contains the following email:

[email protected]
[email protected]

However, when imported using the above, the table would result in:

" [email protected]"
" [email protected]"

Note that " is used to wrap the data at each line, and a space is included after the first ". How do I do an import such that quotations would not be added?

Upvotes: 2

Views: 1416

Answers (2)

SamIAm
SamIAm

Reputation: 2489

I was doing some testing and realized that if the header of the CSV file contains a space in the beginning, sqlite will add quotations as it believes the space should be included in the name.

e.g.

 Email
 [email protected]
 [email protected]

(note the space before Email, and the data) will show up, if imported in sqlite as:

" Email"
" [email protected]"
" [email protected]"

However, if the header does not contain a single space before, it will not add quotations and would instead show up as:

Email
 [email protected]
 [email protected]

Note the space before the actual data. The data still contains spaces but sqlite does not add quotations around them.

I'm not really sure why this is the case but that's how it goes.

Upvotes: 2

Clarius
Clarius

Reputation: 1419

Had the same issue importing city names from a .csv file. Virginia Beach would be written by .import as "Virginia Beach". Wish I could come up with a better answer than a Python program, but (for what it's worth) here it is.

#!/usr/bin/python
import csv
import sqlite3

conn = sqlite3.connect("test.db")

conn.execute("CREATE TABLE cities(name TEXT, population INTEGER)")

with open("cities.csv", "r") as ifile:
    reader = csv.reader(ifile, delimiter = ",")
    for record in reader:
        sql = "INSERT INTO cities (name, population) VALUES('{0}',{1})".format(record[0], record[1])
        conn.execute(sql)

conn.commit()

conn.close()

Upvotes: 0

Related Questions