SQLite Forum

using a | as separator does not parse into columns during import, please help out
Login

using a | as separator does not parse into columns during import, please help out

(1.3) By JorisL (commandline) on 2021-11-29 15:53:25 edited from 1.2 [link] [source]

the sqlite3 import does not take the | as separator using

.mode csv
.separator |
.import reports/my_file_csv

I've also noticed the first line is not being used as a header

for lines like these

CVEID CVEstring severity datetime void vendor rationale score2 string2 score3 string3 cweid
CVE-2019-10218 moderate 2019-10-29T00:00:00Z JREDHAT samba: smb client vulnerable to filenames containing path separators 5.300000 CVSS:3.0/AV:N/AC:H/PR:N/UI:R/S:U/C:N/I:H/A:N CWE-22
CVE-2019-10222 CVE-2019-10222 on Ubuntu 14.04 LTS (trusty) - medium. Medium 2019-08-28 14:00:00 UTC TRUSTY A flaw was found in the Ceph RGW configuration with Beast as the front end handling client requests. An unauthenticated attacker could crash the Ceph RGW server by sending valid HTTP headers and terminating the connection, resulting in a remote denial of service for Ceph RGW clients.

(2) By Kees Nuyt (knu) on 2021-11-29 17:16:46 in reply to 1.3 [source]

It works for me with this script:

sqlite3 test.sqlite -version
sqlite3 test.sqlite <<EOSQL
.echo on
.mode csv
.separator |
.import 'in8.bsv' data1
.mode columns
SELECT CVEID,severity,datetime,vendor FROM data1;
EOSQL

Results:

+ sqlite3 test.sqlite -version
3.37.0 2021-11-19 19:11:58 d814ba6effc7c5b021ff625b57371627f3ee326687c508da99f78bc164386b7e
+ sqlite3 test.sqlite
.mode csv
.separator |
.import 'in8.bsv' data1
.mode columns
SELECT CVEID,severity,datetime,vendor FROM data1;
CVEID           severity  datetime                 vendor
--------------  --------  -----------------------  -------
CVE-2019-10218  moderate  2019-10-29T00:00:00Z     JREDHAT
CVE-2019-10222  Medium    2019-08-28 14:00:00 UTC  TRUSTY

I think you forgot to supply a table name in the .import command.

-- 
Kind regards,
Kees Nuyt

(3) By JorisL (commandline) on 2021-12-06 12:01:55 in reply to 2 [link] [source]

Thanks Kees.

I'm very new to SQL and SQLite.

I am stumped. I just tried the import again as in your example, it works fine. Since i omitted the tablename from the example i cannot but think you're right.