I’m trying to copy data from a .CSV file that was exported from an Access database. When I try to create the table with the Copy/Paste function in the Tables window, I can proceed through all the table creation steps until I click the “Create” button. Then, I get this error:
I don’t understand what this means, or what’s not correct. Do I need to create a blank table with the fields and then paste the data?
Connect a Base document to your Access database. You can use that connection with new queries, forms and reports or copy over the tables into some other database by dragging table icons from one database into the other.
Thank you for the reply. I don’t want to connect the existing Access database because I want to move away from the MS products entirely. I want to port the existing data into Libre Office Base, and continue from there.
Anyway, connect a Base document to that Access-DB and drag the tables over to the new database. This is by far easier than struggling with csv.
As we can not see, what you tried it is some kind of guesswork…
That is, what I have usually done. Because migration is a good opportunity to check sizes of fields/columns and perhaps also names. But as @Villeroy already wrote: It is not really necessary.
The first time I tried this, I left the defaults for the Firefox Embedded. The second time I tried it, I used the HSQLDB, and the same process worked fine.
Thanks for the tip. I wasn’t able to drag and drop, but I could copy the table to the Clipboard from MSAccess, and Paste in Base.
Even if there’s an ID field as the Primary Key, it’s not recognized. I always have to create a new Primary Key. Why is that? Any way to set the Primary Key field when importing the data? I didn’t see one, but not sure if I missed something.
I often import data from Calc to databases by using copy/paste.
As already said: I prefer to create the empty table first, then import data. When I have defined an id with auto-increment as primary key I can either have an empty column in Calc (will be filled by the database during import) or provide the Ids (obviously need to be unique) in the Calc-Table.
If you see this filed in import wizard: Right mouse click on the filed and choose “Primary Key”.
2 database windows, Northwind.accdb on the left, a new one with embedded HSQL on the right. Notice the statusbars!
I drag the Customers table over the [Tables] icon, then into the tables section and import all columns except for the last one (“Attachments”) which raised an error in an earlier attempt. The last column has some binary data unhandled by the driver.
The resulting table includes all column types including the auto-ID.
Contrary to csv or spreadsheets, the database table provides all technical data about data types and primary keys
Thanks to everyone for posting tips and suggestions. This helped a lot. I was able to implement the methods suggested, and I figured out how to set the Primary Key for the field being imported to the new table.