[Solved] Consolidate spreadsheets in 1 DB

Creating tables and queries

[Solved] Consolidate spreadsheets in 1 DB

Postby Davide.b » Sat Feb 17, 2018 12:19 am

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
Davide.b
 
Posts: 2
Joined: Sat Feb 17, 2018 12:13 am

Re: Consolidate spreadsheets in 1 DB

Postby eremmel » Sun Feb 18, 2018 8:26 pm

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.
Is your question answered: edit your initial post add "[Solved]" to the subject and check V
LO4.2.8 for work with ORB; AOO4.1.4,LO5.2.5/6.0.5 on W7E SP1 64bit
It's Microsoft marketing that tells you computers are qualified for non-technicians
User avatar
eremmel
Volunteer
 
Posts: 1033
Joined: Tue Dec 30, 2008 1:15 am
Location: Barneveld, Netherlands

Re: Consolidate spreadsheets in 1 DB

Postby Davide.b » Sun Feb 18, 2018 11:53 pm

Thank you!
OpenOffice 4.1.5 - Windows 7
Davide.b
 
Posts: 2
Joined: Sat Feb 17, 2018 12:13 am


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 1 guest