GeezerVison is showing github.com — Import CSV column order · Issue #243 · sqlitebrowser/sqlitebrowser
Import CSV column order #243DescriptionIt 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. Activityjustinclift commented on 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 commented on Mar 30, 2015 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. jungle-boogie commented on May 16, 2016 I'm able to change the column order during a csv import in the latest master. justinclift commented on Jan 28, 2017 Looking back over this... @jungle-boogie how did you rearrange the column order during import? jungle-boogie commented on 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 commented on 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 commented on Jul 30, 2017 @justinclift Wrt
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:
with open('some_file.csv', 'Urb') as csvfile:
reader = csv.DictReader(csvfile, dialect='excel')
fieldnames = reader.fieldnames # DictReader uses the header row for dict keys
insert_query = u"""
INSERT OR IGNORE INTO `some_table`
({})
VALUES
({})
""".format(', '.join('`%s`' % f for f in fieldnames), # column names
', '.join(':%s' % f for f in fieldnames)) # named parameters
# the insert query construction maps the fieldnames to columns with the same name
for row in reader:
# in actual version, I optimised by inserting 10,000 rows at a time before committing
row = {k.decode('utf-8'): v.decode('utf-8') for k, v in row.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 keysThis 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. justinclift commented on Jul 30, 2017
If this behavior is by design and not a bug
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 commented on 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 commented on 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 commented on Jul 31, 2017 Sure, I won't be able to get to it for a couple weeks however. FriedrichFroebel commented on 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 UPDATE query manually is not really a problem, but I would not have expected this behaviour with the headers option enabled.
MetadataMetadataDevelopmentNo branches or pull requestsIssue actions
|