How to import data from spreadsheet into existing table?

[adrian87]

I can see how to import data into a new table but I am struggling to import into an existing one.

I have a spreadsheet with column headers that exactly match the columns in the table i want to import to. I copy the spreadsheet cells and click paste special on the table.

Select Rich Text formatting (only other option is Microsoft excel worksheet which doesnt work).

I set the table name to the table i want to import to.
Select append data and "user first line as column names" (I have tried with and without first column as headers)

Then theres a assign columns table which I dont really get - it looks like where your supposed to assign which input column gets entered to which table column but you cant actually do anything other than tick or un-tick input columns.

Click create.

I get an error - "incorrect type for setstring continue anyway?" - I assume this is caused by the process trying to paste a string value into the integer primary key field in the table.

I click continue and i get a new record in the table but its missed the first column and entered some madness in the primary key field (should be an auto generated integer - on some attempts it entered a 0 on others it enters a huge negative number).



What am I doing wrong?

[Villeroy]

Copy a cell range, select the icon of a database table and paste. An import wizard pops up.
Choose "Append data" and make sure that the table name is the right one.
The copied selection may include one line of column headers or not. Check the corresponding option in the import dialog if the selection includes headers.
Spreadsheet cells support 2 kinds of values: text and numbers. Dates are day numbers formatted as dates. If your dates are text, only ISO dates (YYYY-MM-DD) will be recognized as dates. Times are day fractions formatted as times. Times should be smaller than one day.

[adrian87]

Copy a cell range, select the icon of a database table and paste. An import wizard pops up.
Choose "Append data" and make sure that the table name is the right one.
The copied selection may include one line of column headers or not. Check the corresponding option in the import dialog if the selection includes headers.
Spreadsheet cells support 2 kinds of values: text and numbers. Dates are day numbers formatted as dates. If your dates are text, only ISO dates (YYYY-MM-DD) will be recognized as dates. Times are day fractions formatted as times. Times should be smaller than one day.

thanks for your reply, but not sure you actually read my post? I have tried those exact steps and get odd results. I just tried again and of 4 columns, only 2 copied over (translated to the wrong columns in the table) and the other 2 didnt populate at all.

also rather than primary key id of 107 (the next in sequence) its added a key value of -1314799888

[Villeroy]

Then theres a assign columns table which I dont really get - it looks like where your supposed to assign which input column gets entered to which table column but you cant actually do anything other than tick or un-tick input columns.

You can move them side by side. select an entry on one side and click the up/down buttons until it is besides the corresponding column. Move any auto-id field down to the bottom and uncheck. If the order of database columns matches with the order of sheet columns you can skip this.
You may prefer to arrange the column order on sheet before copying.
You may paste to a transitional table matching the structure of the sheet columns, create a view doing some type conversion and rearrangments and then copy the view to the actual data table.
You may use a macro of mine: viewtopic.php?f=21&t=95587

No matter what you do, you are the only one who knows about the actual data structures. Upload a spreadsheet file with a few lines of data copied from the original. Quite often spreadsheet data are inconistent.