You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
It would be great if the column order during csv import could be changed. If the order of the columns in the target database doesn't match the source database, swapping the columns around manually is somewhat difficult.
Hmmmm, needing to import columns in a different order than defined in a csv file is pretty unusual (I think anyway). I doubt we'll do this... but if someone sends in a patch to make it happen we'll happily merge it. 😄
Well, the problem is that csv import ignores column names in the csv file
and assumes the order is the same. In my situation this was not so. I
resorted to fixing the sql export as fixing the csv file was too much work.
But as a csv importer is already present, a dialog allowing the user to
arrange the csv columns before import would make sense.
Was about to post a new issue and then I see this one exists and is still open.
Details for the issue
When importing data from a CSV file into an existing table, the column names in header row are ignored and the import occurs only on column order. So if the source data has columns out of order, then the data is goes into the wrong columns.
If importing a CSV file as a new table, then it uses the header row as the column names and in that order.
If this behavior is by design and not a bug - it is inconsistent wrt import as new table vs into existing table.
Hmmmm, needing to import columns in a different order than defined in a csv file is pretty unusual (I think anyway).
Maybe switching the column order during import is more work than it's worth - but reading the header row (if the user leaves the box checked) and then matching that with the table's column names, if the table exists, should be straightforward. Especially if the header row names are a 100% match against the table's column names.
And providing a header-to-column name dialog when importing into an existing table may be easier than column switching.
I finally used something like this to do the imports in Python:
withopen('some_file.csv', 'Urb') ascsvfile:
reader=csv.DictReader(csvfile, dialect='excel')
fieldnames=reader.fieldnames# DictReader uses the header row for dict keysinsert_query=u""" INSERT OR IGNORE INTO `some_table` ({}) VALUES ({}) """.format(', '.join('`%s`'%fforfinfieldnames), # column names', '.join(':%s'%fforfinfieldnames)) # named parameters# the insert query construction maps the fieldnames to columns with the same nameforrowinreader:
# in actual version, I optimised by inserting 10,000 rows at a time before committingrow= {k.decode('utf-8'): v.decode('utf-8') fork, vinrow.items()}
_=cursor_lite.execute(insert_query, row)
# when `row` is a dict/mapping and not a list/tuple,# then it's matched on param names and keys
This inserts on names rather than order and skips fieldnames in the csv which are not columns of the table. (If a not-null column is missed, that should error out anyway.)
I doubt we'll do this... but if someone sends in a patch to make it happen we'll happily merge it. 😄
Alas, my C++ skills are just high-school level from too many years ago.
Hmmm, it's kind of neither. It's just that when this was first implemented, the concept of "lets do a match up by column name" probably didn't spring to mind. So, wasn't part of how things were put in place.
That being said, it's not a bad idea. It would be useful for some scenario's and there's no reason to not implement this.
FWIW, "match csv Headers to Column names" should be the default when the user selects 'headers in first row'. However, for those who actually want to import by order and ignore the header row - that may break backwards behaviour. Probably add a checkbox for "match headers & columns" which is enabled (and checked) only if 'headers in first row' is checked.
Yeah, when thinking about this a bit more earlier on today... I can't really imagine a scenario where someone supplies a csv with column headers and doesn't want the columns matching up with the same column names in an existing table.
eg not real sure a checkbox selector is needed
If course, I might just be not thinking of some super obvious scenario... 😄
I stumbled upon this as well today when trying to import some psql data dump into a SQLite database for testing purposes. Writing the corresponding UPDATE query manually is not really a problem, but I would not have expected this behaviour with the headers option enabled.
Activity
justinclift commentedon Mar 30, 2015
Hmmmm, needing to import columns in a different order than defined in a csv file is pretty unusual (I think anyway). I doubt we'll do this... but if someone sends in a patch to make it happen we'll happily merge it. 😄
galmok commentedon Mar 30, 2015
jungle-boogie commentedon May 16, 2016
I'm able to change the column order during a csv import in the latest master.
justinclift commentedon Jan 28, 2017
Looking back over this... @jungle-boogie how did you rearrange the column order during import?
jungle-boogie commentedon Jan 30, 2017
@justinclift,
I think I'm mistaken.
I think what I was saying is that within a table I can change the column order, but I cannot change a table order of an import. Sorry about that.
best,
jb
aneroid commentedon Jul 30, 2017
Was about to post a new issue and then I see this one exists and is still open.
Details for the issue
When importing data from a CSV file into an existing table, the column names in header row are ignored and the import occurs only on column order. So if the source data has columns out of order, then the data is goes into the wrong columns.
If importing a CSV file as a new table, then it uses the header row as the column names and in that order.
If this behavior is by design and not a bug - it is inconsistent wrt import as new table vs into existing table.
(Tested on 3.9.1 and 3.10.0-beta1-win64.)
aneroid commentedon Jul 30, 2017
@justinclift Wrt
Maybe switching the column order during import is more work than it's worth - but reading the header row (if the user leaves the box checked) and then matching that with the table's column names, if the table exists, should be straightforward. Especially if the header row names are a 100% match against the table's column names.
And providing a header-to-column name dialog when importing into an existing table may be easier than column switching.
I finally used something like this to do the imports in Python:
This inserts on names rather than order and skips fieldnames in the csv which are not columns of the table. (If a not-null column is missed, that should error out anyway.)
Alas, my C++ skills are just high-school level from too many years ago.
justinclift commentedon Jul 30, 2017
Hmmm, it's kind of neither. It's just that when this was first implemented, the concept of "lets do a match up by column name" probably didn't spring to mind. So, wasn't part of how things were put in place.
That being said, it's not a bad idea. It would be useful for some scenario's and there's no reason to not implement this.
@iKlsR Any interest in getting this done? 😄
aneroid commentedon Jul 30, 2017
FWIW, "match csv Headers to Column names" should be the default when the user selects 'headers in first row'. However, for those who actually want to import by order and ignore the header row - that may break backwards behaviour. Probably add a checkbox for "match headers & columns" which is enabled (and checked) only if 'headers in first row' is checked.
justinclift commentedon Jul 30, 2017
Yeah, when thinking about this a bit more earlier on today... I can't really imagine a scenario where someone supplies a csv with column headers and doesn't want the columns matching up with the same column names in an existing table.
eg not real sure a checkbox selector is needed
If course, I might just be not thinking of some super obvious scenario... 😄
iKlsR commentedon Jul 31, 2017
Sure, I won't be able to get to it for a couple weeks however.
FriedrichFroebel commentedon Feb 26, 2021
I stumbled upon this as well today when trying to import some psql data dump into a SQLite database for testing purposes. Writing the corresponding
UPDATEquery manually is not really a problem, but I would not have expected this behaviour with the headers option enabled.