[Solved] Consolidate spreadsheets in 1 DB

[Hagar Delest]

Hi - this question is quite basic but I am new to the software.
Here my challenge:
- I have 4 spreadsheets with more than 100k lines each.
- Each line is a customer and there is the unique ID in column 1, then each sheet has a different set of data (2-3 columns)
- not all the IDs are present in all the spreadsheets

I want to unify all of them in 1 single database, with all data available for each ID.

I guess it's quite easy but I wasted hours trying to get it without success: help please!!

Davide

[eremmel]

Hi Davide, welcome on this forum.

You can not put the data of each sheet in the same table when you want to use the customerID of the sheets as your primary key of that new table. There is also no way to consolidate the data from the 3 columns.

But you might use a temporary table for this. Create a new Base document and design a table with the fields:
ID (identity auto increment)
CustomerID
Data1
Data2
Data3
Now make for each spreadsheet a Base document.
You can not drag and drop the tables of the spreadsheet-base documents to the table-section of the new document. An import wizard will popup to help you to get all the data of the four spreadsheet-base documents into the temporary table.

Now create your final table and write a query that consolidates all the data per CustomerID.
With this query you can run an insert statement to fill your final table.

To work out the details and help you with that, you need to present the way how/what you want to consolidate and table/field names.

Success, Erik

[Davide.b]

Thank you!