[sequel-pro] Importing csv errors

[akinnie]

I have an existing database that was created in a different system. I
exported the data as csv. Each line should be a new row. However,
when I try and import, I get repeated errors on all rows after the
first:

Duplicate entry '0' for key 'PRIMARY'

I have no idea what I am supposed to do, and there appear to be no
docs. I have tried it by deleting the index on the primary key, and
the data imports, but the row that is supposed to be the primary key
gets 0 in each row.

If I leave it in, the pk never increments, so I get the error.

I have been trying to do this by selecting the table, going to the
menu and selecting "import" selecting csv, assigning the correct
columns to the correct columns, all as seems obvious. It's just when
it I click import, it imports one row and fails.

I assume there is something blindingly obvious that I am missing. The
tool is basically useless without this function, and everyone else
seems to have no problem at all.

I had hoped I could accomplish something with it the weekend, but it
now seems unlikely

Any help would be appreciated.

--
You received this message because you are subscribed to the Google Groups "Sequel Pro" group.
To post to this group, send email to seque...@googlegroups.com.
To unsubscribe from this group, send email to sequel-pro+...@googlegroups.com.
For more options, visit this group at http://groups.google.com/group/sequel-pro?hl=en.

[Rowan Beentje]

Hi akinnie,

It sounds like two things could be happening:

1) Is the primary column which is meant to be auto-incrementing actually set up with the "auto_increment" field attribute? In Sequel Pro's interface, if you're looking at your table in the Structure view, the "Extra" column should show "atuo_increment" for that field. If this hasn't been set up, the server will use the field default for each newly created row, which could be causing the issue.

2) When you import, the CSV field mapping sheet should show you a list of the table fields on the right, and CSV fields on the left. For your table's auto incrementing field, did you choose "Ignore field" in the mapping dropdown? If so, it should show "DEFAULT: auto_increment" in grey if the field is set up correctly. If it didn't show this, what did it show?

--Rowan

[Andrew Kinnie]

Thanks for responding.

I didn't have the "auto-increment" set in the primary key field. (I've now changed it)

Also, when I select the id field (the PK) and tell the id column to be ignored, I get "Default:0" but it does not say "auto-increment"

What should I change and how do I change it?

[Rowan Beentje]

The missing auto_increment was almost certainly it then. It may be that we don't update all the caches when auto_increment is added - try switching to another database/table, back, and then try the import again.

"Default:0" suggests it's still picking up the old value - we'll look at that.

--Rowan

[Andrew Kinnie]

hmmm. OK, well, now I can import with the auto-increment, however that creates another issue. I have some data that had a PK that was already in the exported csv file. Is there a way to import this while maintaining the existing rows primary keys? (they are not necessarily sequential, as some rows had been deleted in the other database - so the first PK is 1 and the next is 4, etc.).

Thanks for your help.

[Rowan Beentje]

MySQL's default behaviour is to assign automatically incrementing IDs where no value is provided, or where NULL is provided. You're going to have to tweak your csv file so that the PK column contains either NULL (*not* quoted as text) or the null character (\0); then when importing, map the PK column and you should find your existing IDs are preserved, while the NULL entries have new ids assigned.

Fingers crossed,
Rowan

[Andrew Kinnie]

OK.... so now I'm confused. I am not concerned whether MySQL needs its own PK that it increments, and have no problem mapping the old file's PK to the new MySQL PK column. Are you saying I need to create a new empty row for each of the missing rows from the old table? Or are you saying create a new row in the MySQL table for the new PK, then create another row for the old PK table?

[Rowan Beentje]

I think you can keep your auto_incrementing MySQL field as the PK field. Say you've got a CSV file which contains two columns: one being the existing PK, one being a name. For the rows where you *don't* have a preexisting PK, just make sure the cell contains NULL (unquoted) or \0, and then import the CSV mapping both columns. MySQL will then fill in your existing PKs, create new ones where they don't exist. So say your CSV contains these three rows:

1,'foo'
NULL,'bar'
2,'baz'

The two existing PKs will be imported as-is; the NULL will be assigned a new ID. This does bring up one dangerous issue - if these are imported in order, the second row will be assigned an ID of 2, so you'll get an error in your third row. You could either presort your CSV, or go to the "Table Info" section, click on the gear next to the "Auto Increment" field, and reset it to a non-conflicting number before you import.

[Andrew Kinnie]

yeah I think I wasn't clear.

I have an existing PK field _rowid, and another name

so I might have data:

_rowid, name
1, bob
4, steve
5, ed
7, joe
etc.

So it's not that existing rows are missing pk's but rather that the PKs are not sequential, because the rows represented by PK 2, 3 and 6 were deleted.

I could create new empty rows for those, but this table has thousands of rows, so I'd rather not if possible.

[Rowan Beentje]

I'm a little confused by your question I'm afraid!

> I have some data that had a PK that was already in the exported csv file. Is there a way to import this while maintaining the existing rows primary keys? (they are not necessarily sequential, as some rows had been deleted in the other database - so the first PK is 1 and the next is 4, etc.).

Are you asking whether you can import your existing primary keys into the MySQL primary key field? In which case, that's no problem - just map the column, the data can be non-sequential without any issues (there's even settings so that the auto_increment value can be numbers other than 1).

Or are you asking whether you can preserve the existing keys while also creating a new MySQL key? In which case, just add a old_key field to the table, and when importing, map the field to that.

Sorry if I'm missing something.

[Andrew Kinnie]

Actually, after I sent my last message I checked the data, and there were only 2 rows missing, so I just created blank rows for those two, and everything appears to have worked.

Thanks for all your help!