CODEWITHSUNDEEP
csvsqliteexport-to-csv
Daniel O.
Daniel O.

Reputation: 813

How to output SQLite column with quotes

I have a SQLite Column that is of type VARCHAR(36). When I set the mode to CSV with .mode csv and execute SELECT column_a from table_a ORDER BY column_a ASC; I get the output as the string without double quotes. An example row would be spotify:track:6cLgBwRc1LfV0cSoboEfJD. I need the output to be "spotify:track:6cLgBwRc1LfV0cSoboEfJD".

Upvotes: 2

Views: 2547

Answers (3)

surfmuggle
surfmuggle

Reputation: 5954

In my case i wanted to export a column with quotes and a comma "123A", like this

  "123A",
  "345E",
  "678F",

to use the output for an array in a json object:

{ 
    "id": 300,
    "numbers" : [
      "123A",
      "345E",
      "678F"],
    "foo": "bar"
}

I created a file export_numbers_with_quotes.bat that has these lines

# export_numbers_with_quotes.bat
DROP TABLE IF EXISTS NewCSVImport;
.import --csv C:\\csv\\my_raw_data.csv NewCSVImport
.headers on
.mode column
.once C:\\csv\\dataout.csv
SELECT '"' || Numbers || '",' as ColumnExportQuoted FROM NewCSVImport;

Above .mode column combined with '"' || Numbers has the desired output.

To import the csv data and export the column that you want with quotes just call the batch file like this

sqlite> .read 'C:\csv\export_numbers_with_quotes.bat'

The output is determined by

SELECT '"' || VIN || '",' as ColumnExportQuoted FROM NewCSVImport;

And looks like this

ColumnExportQuoted  
--------------------
 "123A",
 "345E",
 "678F",

Upvotes: 0

Dom
Dom

Reputation: 157

You can use:

SELECT concat(concat('"', column_a), '"') from table_a ORDER BY column_a ASC;

Upvotes: 0

Tim Biegeleisen
Tim Biegeleisen

Reputation: 522712

String concatenation should work here, and you should be able to simply include double quotes inside a single-quoted string literal:

SELECT '"' || column_a || '"'
FROM table_a
ORDER BY column_a ASC;

Upvotes: 3

Related Questions