On Sunday, 26 January, 2020 10:29, chiahui chen <chiahuich...@gmail.com> wrote:
>After creating a table (total 8 columns including 1 generated column), I >tried to import data from a csv file (each record has values for 7 >columns that match the non-generated column names and data types, no >headers ). >The system issued "error: table has 7 columns but 8 values were supplied." >I wonder why. This is because of the way the .import (currently) command works in the CLI. Looking at the code this is how it presently works: Check if <table> exists, and if it does not, then create it, optionally using the column names from the CSV import file in the order they appear. Now that the table exists, use "SELECT * FROM <table>" to determine the number of columns in the table (which will include computed always columns, if any). Generate a statement of the form "INSERT INTO <table> VALUES (? ...)" where there is one ? parameter for each column in the table. Loop through the CSV file and bind the parameters to the above statement, executing a sqlite3_step at the end of each row. You will note that: 1. Column Names in the CSV are *only* used if <table> is created. 2. Column Names in the CSV are *not* used for the INSERT statement. 3. The number of column names from which you can "SELECT *" is not the same as the number of columns you can "INSERT INTO" if the table pre-exists and contains generated always columns (which cannot be INSERT INTO'd). 4. Specifying column names (a header line) does not "match up" those column names to the INSERT INTO column names. >After experimenting different ways to import data to a table that has a >generated column, so far I only found that .read command with a .sql >file that was output as a result of 'mode insert' is successful. Is >there any other ways to import data to an existing generated-column table? 1. .import into a temporary <temptable> then use INSERT INTO <table> (<explicit column list>) SELECT * FROM <temptable> and then drop the temporary table. 2. include/load the csv extension and use an insert with an explicit column list to insert into <table> from the csv virtual table. Otherwise, Richard *may* make some changes to the .import logic which *might* appear in the next release version. Otherwise you will have to use one of the three noted word-arounds. -- The fact that there's a Highway to Hell but only a Stairway to Heaven says a lot about anticipated traffic volume. _______________________________________________ sqlite-users mailing list sqlite-users@mailinglists.sqlite.org http://mailinglists.sqlite.org/cgi-bin/mailman/listinfo/sqlite-users
