Bug Report: SQLite tools .import command does not strip spaces from input .csv header column names
(1) By rgward611 on 2020-10-16 19:59:47 [link] [source]
Details on the issue...
Created a sqlite3 script to create NEW database tables using .import command. The SQLite .import command dynamically creates the CREATE TABLE command.
My expectation was the .import command would work exactly like (DB4S) DB Browser GUI IMPORT CSV as Table and strip imbedded spaces from the input CSV column names. Instead, the .import command tool is NOT removing imbedded spaces from the CSV header column names and constructs the CREATE TABLE with the imbedded spaces (e.g., "Property Group", "Property Address"). As the result, all subsequent SQL Select command scripts executions to fail due to "no such column" errors.
DB4S IMPORT function is working as expected, stripping imbedded spaces from the input CSV header column names when constructing the CREATE TABLE command (e.g., "PropertyGroup", "PropertyAddress").
(Partial) CREATE TABLE command generated using DB4S: CREATE TABLE HOAexportusers( "ChapterGroup" TEXT, "ChapterName" TEXT, "LastLoginDate" TEXT, "MembershipTitle" TEXT, "PropertyAcct.#" TEXT, "PropertyAddress" TEXT, "PropertyGroup" TEXT, "PropertyName" TEXT, "PropertyUnit" TEXT )
(Partial) CREATE TABLE command generated by .import command showing imbedded 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 )
Using versions... 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 SQLite tools versions 3.33 and 3.29
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;
(2) By Richard Hipp (drh) on 2020-10-16 20:16:57 in reply to 1 [source]
My expectation was the .import command would work exactly like (DB4S) DB Browser GUI IMPORT CSV as Table and strip imbedded spaces from the input CSV column names.
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.
Is there some other part of RFQ-4180 that overrides the statement above, that I have overlooked?
(3) By rgward611 on 2020-10-16 20:44:08 in reply to 2 [link] [source]
Darn Richard! After all the work I put into writing and documenting the issue you blow the issue out of the water with RFC-4180. You nailed it. Back to the DB4S team. Thanks very much for sharing the RFC.