0

Better progress:

nicholas@mordor:~/csv$ 
nicholas@mordor:~/csv$ sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> 
sqlite> .import BCCDC_COVID19_Dashboard_Case_Details.csv details

...

BCCDC_COVID19_Dashboard_Case_Details.csv:25475: unescaped " character
BCCDC_COVID19_Dashboard_Case_Details.csv:25475: unescaped " character
sqlite> 
sqlite> select * from details limit 3;
2020-01-26","Out of Canada","M","40-49","Lab-diagnosed
2020-02-02","Vancouver Coastal","F","50-59","Lab-diagnosed
2020-02-05","Out of Canada","F","20-29","Lab-diagnosed
sqlite> 
sqlite> select "AGE_Group" from details limit 3;
AGE_Group
AGE_Group
AGE_Group
sqlite>

thanks to the answer below.

How can I import this data into sqlite:

nicholas@mordor:~/csv$ 
nicholas@mordor:~/csv$ sqlite3
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
Connected to a transient in-memory database.
Use ".open FILENAME" to reopen on a persistent database.
sqlite> 
sqlite> .mode csv
sqlite> 
sqlite> .import BCCDC_COVID19_Dashboard_Case_Details.csv details;
CREATE TABLE details;(...) failed: near ";": syntax error
sqlite> .quit
nicholas@mordor:~/csv$ 
nicholas@mordor:~/csv$ head BCCDC_COVID19_Dashboard_Case_Details.csv 
"Reported_Date","HA","Sex","Age_Group","Classification_Reported"
"2020-01-26","Out of Canada","M","40-49","Lab-diagnosed"
"2020-02-02","Vancouver Coastal","F","50-59","Lab-diagnosed"
"2020-02-05","Out of Canada","F","20-29","Lab-diagnosed"
"2020-02-05","Out of Canada","M","30-39","Lab-diagnosed"
"2020-02-11","Interior","F","30-39","Lab-diagnosed"
"2020-02-20","Fraser","F","30-39","Lab-diagnosed"
"2020-02-21","Fraser","M","40-49","Lab-diagnosed"
"2020-02-27","Vancouver Coastal","F","60-69","Lab-diagnosed"
"2020-02-28","Vancouver Coastal","F","30-39","Lab-diagnosed"
nicholas@mordor:~/csv$

where it seems that the quote marks are problematic.

I've also tried different modes and other settings:

sqlite> 
sqlite> .mode ascii
sqlite> .separator ","
sqlite>

without success. Referencing generic documentation.

perhaps:

How to import quoted data from a CSV file using mysqlimport?

the csvsql tool would be use.

Nicholas Saunders
  • 684
  • 9
  • 28

2 Answers2

1

It's not a problem with double-quotes. The problem is ;. dot-commands are not terminated with a semi-colon ;. The command line is interpreting the semi colon as part of the table name.

DinoCoderSaurus
  • 6,110
  • 2
  • 10
  • 15
0

For some reason they put quotations around everything in the CSV, so:

nicholas@mordor:~/csv$ 
nicholas@mordor:~/csv$ rm covid.db 
nicholas@mordor:~/csv$ 
nicholas@mordor:~/csv$ touch covid.db
nicholas@mordor:~/csv$ 
nicholas@mordor:~/csv$ csvsql --db sqlite:///covid.db --table cases --insert BCCDC_COVID19_Dashboard_Case_Details.csv 
nicholas@mordor:~/csv$ 
nicholas@mordor:~/csv$ csvsql --db sqlite:///covid.db --table labs --insert BCCDC_COVID19_Dashboard_Lab_Information.csv 
nicholas@mordor:~/csv$ 
nicholas@mordor:~/csv$ sqlite3 covid.db 
SQLite version 3.31.1 2020-01-27 19:55:54
Enter ".help" for usage hints.
sqlite> 
sqlite> .schema
CREATE TABLE cases (
    "Reported_Date" DATE NOT NULL, 
    "HA" VARCHAR NOT NULL, 
    "Sex" VARCHAR NOT NULL, 
    "Age_Group" VARCHAR NOT NULL, 
    "Classification_Reported" VARCHAR NOT NULL
);
CREATE TABLE labs (
    "Date" DATE NOT NULL, 
    "Region" VARCHAR NOT NULL, 
    "New_Tests" FLOAT NOT NULL, 
    "Total_Tests" FLOAT NOT NULL, 
    "Positivity" FLOAT NOT NULL, 
    "Turn_Around" FLOAT NOT NULL
);
sqlite> 
sqlite> select "Reported_Date","HA","Classification_Reported" from cases limit 3;
2020-01-26|Out of Canada|Lab-diagnosed
2020-02-02|Vancouver Coastal|Lab-diagnosed
2020-02-05|Out of Canada|Lab-diagnosed
sqlite> 
sqlite> select "Date","Region" from labs limit 3;
2020-01-23|BC
2020-01-23|Fraser
2020-01-23|Interior
sqlite>

which works.

see:

How to import quoted data from a CSV file using mysqlimport?

Nicholas Saunders
  • 684
  • 9
  • 28