[Solved] Consolidate spreadsheets in 1 DB

Creating tables and queries
Post Reply
Davide.b
Posts: 2
Joined: Sat Feb 17, 2018 12:13 am

[Solved] Consolidate spreadsheets in 1 DB

Post by Davide.b »

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
Last edited by Hagar Delest on Wed Feb 21, 2018 11:43 pm, edited 1 time in total.
Reason: tagged [Solved].
OpenOffice 4.1.5 - Windows 7
eremmel
Posts: 1080
Joined: Tue Dec 30, 2008 1:15 am

Re: Consolidate spreadsheets in 1 DB

Post by 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
Last edited by eremmel on Mon Feb 19, 2018 10:12 am, edited 1 time in total.
It's Microsoft marketing that tells you computers are qualified for non-technicians
W11 22H2 (build 22621), LO 7.4.2.3(x64)
Davide.b
Posts: 2
Joined: Sat Feb 17, 2018 12:13 am

Re: Consolidate spreadsheets in 1 DB

Post by Davide.b »

Thank you!
OpenOffice 4.1.5 - Windows 7
Post Reply