Repository navigation
sqlite3 .import command does not strip spaces from header column names #2442
Description
Details for the issue
What did you do?
Created a sqlite3 script to create NEW database tables using .import command.
What did you expect to see?
Expected .import command to work exactly like DB Browser GUI import function and strip embedded spaces from input CSV column names. (e.g., "PropertyGroup", "PropertyAddress"
What did you see instead?
The column names of the input CSV file were created WITHOUT stripping embedded spaces. (e.g., "Property Group", "Property Address"). As the result, all subsequent SQL Select command scripts fail due to undefined column names.
Useful extra information
DB4S v3.12.0 [built for x86_64-little_endian-llp64] on Windows 10 (10.0) (winnt/10.0.19041) [x86_64]
using SQLite Version 3.32.2
and Qt 5.12.8
Have tested with SQLite3 versions 3.33 and 3.29
Activity
chrisjlocke commentedon Oct 15, 2020
What was the actual SQL you used - can you post it here? Only need the create table command - no data.
If you used quotes (
create table 'my table' (field1 integer, 'my field' integer);) then DB4S would do as you asked. Come to think of it, so would SQLite. If it was simplycreate table my table (field1 integer, my field integer);then that's invalid SQL, and that wouldn't import at all. The SQLite .import tool might like it and import it (or correct it) but its still invalid SQL, and as such, DB4S is a bit more strict.Actually, re-reading your query, the SQLite .import tool is nothing to do with DB Browser for SQLite - it just happens DB Browser for SQLite uses the SQLite library.
rgward611 commentedon Oct 15, 2020
The SQLite .import tool dynamically creates the Create Table command and only requires the input CSV file. Here is the command line script.
sqlite3 "%dbpath%HOAdirectory_test.db" < "%scriptpath%sqlite3_createtablecommands_runscript.txt"
sqlite3_createtablecommands_runscript.txt
.databases
.mode csv
.header on
.separator ,
.read "C:/RGW/Home/Carrolls Creek Condo/Community website/Database/DB SQL Scripts/drop_HOA_tables.sql"
.import "C:/RGW/Home/Carrolls Creek Condo/Community website/Database/DB Import-Export Data/HOAexportusers_100620_test.csv" HOAexportusers
.import "C:/RGW/Home/Carrolls Creek Condo/Community website/Database/DB Import-Export Data/HOAexportusers_Profiles_100620.csv" HOAexportusers_Profiles
.exit
drop_HOA_tables.sql
DROP TABLE IF EXISTS HOAexportusers;
DROP TABLE IF EXISTS HOAexportusers_Profiles;
rgward611 commentedon Oct 16, 2020
Additional info:
Here is the CREATE TABLE command generated by .import for one table showing embedded space(s) in the column names.
CREATE TABLE HOAexportusers(
"Chapter Group" TEXT,
"Chapter Name" TEXT,
"Last Login Date" TEXT,
"Membership Title" TEXT,
"Property Acct. #" TEXT,
"Property Address" TEXT,
"Property Group" TEXT,
"Property Name" TEXT,
"Property Unit" TEXT,
"User Status" TEXT,
"First Name" TEXT,
"Last Name" TEXT,
"Company Name" TEXT,
"Access Level" TEXT,
"Add Date" TEXT,
"Address" TEXT,
"Admin Notes" TEXT,
"Chapter ID" TEXT,
"Email Address" TEXT,
"Expiration Date" TEXT,
"Group ID(s)" TEXT,
"Join Date" TEXT,
"Last Modified" TEXT,
"Membership Type ID" TEXT,
"Profile Photo" TEXT,
"Show Profile" TEXT,
"Status" TEXT,
"User ID" TEXT,
"User Level" TEXT,
"Primary User" TEXT,
"Username" TEXT,
"2019 Audit Completed" TEXT,
"2020 Audit Completed" TEXT,
"2020 RIF Received" TEXT,
"Alt Address" TEXT,
"Alt Phone" TEXT,
"Cell Phone" TEXT,
"Date of Birth" TEXT,
"Emergency Contacts" TEXT,
"Emergency Notes" TEXT,
"Garage Number" TEXT,
"Home Phone" TEXT,
"No Home Computer" TEXT,
"Pet 1 AACo TagNumber" TEXT,
"Pet 1 AACo Validated" TEXT,
"Pet 1 Breed" TEXT,
"Pet 1 Classification" TEXT,
"Pet 1 Name" TEXT,
"Pet 1 Notes" TEXT,
"Pet 1 Remove Pet" TEXT,
"Pet 1 Tag Expiration" TEXT,
"Pet 1 Type" TEXT,
"Pet 1 Weight" TEXT,
"Pet 2 AACo TagNumber" TEXT,
"Pet 2 AACo Validated" TEXT,
"Pet 2 Breed" TEXT,
"Pet 2 Classification" TEXT,
"Pet 2 Name" TEXT,
"Pet 2 Notes" TEXT,
"Pet 2 Remove Pet" TEXT,
"Pet 2 Tag Expiration" TEXT,
"Pet 2 Type" TEXT,
"Pet 2 Weight" TEXT,
"Pet 3 AACo TagNumber" TEXT,
"Pet 3 AACo Validated" TEXT,
"Pet 3 Breed" TEXT,
"Pet 3 Classification" TEXT,
"Pet 3 Name" TEXT,
"Pet 3 Notes" TEXT,
"Pet 3 Remove Pet" TEXT,
"Pet 3 Tag Expiration" TEXT,
"Pet 3 Type" TEXT,
"Pet 3 Weight" TEXT,
"Profile Updated" TEXT,
"Reserved Parking" TEXT,
"Resident Status" TEXT,
"Unit Number" TEXT,
"Vehicle 1 CC Sticker" TEXT,
"Vehicle 1 Color" TEXT,
"Vehicle 1 Make" TEXT,
"Vehicle 1 Model" TEXT,
"Vehicle 1 NeedStickr" TEXT,
"Vehicle 1 Notes" TEXT,
"Vehicle 1 Remove" TEXT,
"Vehicle 1 State" TEXT,
"Vehicle 1 Tag Number" TEXT,
"Vehicle 1 Verified" TEXT,
"Vehicle 1 Year" TEXT,
"Vehicle 2 CC Sticker" TEXT,
"Vehicle 2 Color" TEXT,
"Vehicle 2 Make" TEXT,
"Vehicle 2 Model" TEXT,
"Vehicle 2 NeedStickr" TEXT,
"Vehicle 2 Notes" TEXT,
"Vehicle 2 Remove" TEXT,
"Vehicle 2 State" TEXT,
"Vehicle 2 Tag Number" TEXT,
"Vehicle 2 Verified" TEXT,
"Vehicle 2 Year" TEXT,
"Vehicle 3 CC Sticker" TEXT,
"Vehicle 3 Color" TEXT,
"Vehicle 3 Make" TEXT,
"Vehicle 3 Model" TEXT,
"Vehicle 3 NeedStickr" TEXT,
"Vehicle 3 Notes" TEXT,
"Vehicle 3 Remove" TEXT,
"Vehicle 3 State" TEXT,
"Vehicle 3 Tag Number" TEXT,
"Vehicle 3 Verified" TEXT,
"Vehicle 3 Year" TEXT,
"Work Phone" TEXT
)
justinclift commentedon Oct 16, 2020
As @chrisjlocke mentions, the
.importcommand is something specific to the SQLite command line interface, and isn't part of the SQLite library itself:https://sqlite.org/cli.html#csv_import
We could probably add support for it. No idea how tricky it would be though. It'd probably be something like:
.importcommand.importcommand when deciding whether to create a new table or reuse an existing oneThe rules the
.importcommand uses (from reading the above SQLite page) seem to be the same as what we already do. So, it might not be too hard. Not sure though. 😄rgward611 commentedon Oct 16, 2020
Thanks Justin. Appreciate your feedback. Your steps sound like the right approach in modifying the SQLite tools .import command code to be consistent with the SQLite library in handling header column names.
chrisjlocke commentedon Oct 16, 2020
For the avoidance of doubt, the SQLite tools .import code is not maintained by us, cannot be changed by us and isn't part of the DB4S project that we manage.
We can change DB4S to more follow the .import tool or even use the .import tool, but we can't modify the .import tool itself.
rgward611 commentedon Oct 16, 2020
Sorry for my misunderstanding. I thought this forum was also the means to submit bug reports with the SQLite tools. Can you advise where to go to place a bug report with the SQLite tools? I'm not suggesting DB4S be changed at all. It's working as I expect, stripping imbedded spaces from the input CSV header column names when constructing the CREATE TABLE command. I'm saying the .import command tool is incorrectly parsing the CSV header names (NOT removing imbedded spaces) and inconsistent with the way DB4S IMPORT constructs the CREATE TABLE command using CSV header column names (i.e., stripping imbedded spaces).
chrisjlocke commentedon Oct 16, 2020
That would be here.
https://sqlite.org/forum/forummain
rgward611 commentedon Oct 16, 2020
Ahh, ok. Thank you for all your help, Chris & Justin.
justinclift commentedon Oct 16, 2020
Ahhh, no worries at all @rgward611. 😄
rgward611 commentedon Oct 17, 2020
After placing a bug report on SQLite forum it appears the issue is with DB4S not compliant with RFC-4180 and stripping spaces from the header column names when they should be ignored.
RFQ-4180 says "Within the header ... spaces are considered part of a field and should not be ignored." It seems like DB4S is the one that is not following the spec, not the SQLite CLI.