Getting desperate at this:
I try to limit the width of one particular column in a SQL-table to be 7 characters wide. More precisely, I would like to trim each element in the column to be a Text-element of at most 7 characters in width (if longer, it shall be truncated).
Another possibility is to alter the number of characters in the corresponding CSV column before SQL-db creation.
The problem: The DB is large !!
(And therefore altering the CSV (before SQL-DB creation) is not possible with most common tools such as Sublime, Atom, or Excel)
What alternatives are there ? (maybe sqlite3 or some terminal-cmd ??)
My CSV-File (table1.txt) looks like:
some_text,some_id,stop_name
"Hello World1","1101069:0:1","MyName1"
"Hello World2","1333332","MyName2"
"Hello World3","1452339:0","MyName3"
"Hello World4","8334342:2:0","MyName4"
The some_id column shall be restricted to 7 characters, resulting in...
some_text,some_id,stop_name
"Hello World1","1101069","MyName1"
"Hello World2","1333332","MyName2"
"Hello World3","1452339","MyName3"
"Hello World4","8334342","MyName4"
My sqlite3 creation file (creationFile.sql) looks like that:
CREATE TABLE table1 (some_text TEXT PRIMARY KEY, some_id TEXT, stop_name TEXT) WITHOUT ROWID;
.separator ,
.import table1.txt table1
I use the following sqlite3 terminal cmd to create the DB :
sqlite3 myTable.db > creationFile.sql`
Now again, the column-width restriction could already be introduced in the CSV-file. But I think this is rather impossible since no standard editor does it anymore due to the large size of the CSV-file (> 10 Mio lines). Is there a way sqlite3 can handle this during DB creation ? Or any terminal cmd doing so before, during or after DB creation ??
You can try this. After creating table1 run this statement:
CREATE TABLE table2 AS
SELECT some_text, SUBSTR(some_id, 1, 7) as some_id, stop_name
FROM table1;
Note: not sure about syntax of SUBSTR() function. It's better to google it.