Calc to Base Records

[Night Owl]

Hello,

I am new to the forum but I have been using OpenOffice for many years for general word processing and personal finance. Recently I started a side hustle repairing small appliances and I threw together a spreadsheet with a tab for each of my thirty customers. On each tab I have customer information, and additional sections for each additional appliance and associated service requests, services performed etc.

I'd like to get away from using the spreadsheet and continue with Base. Is there a quick way to build a database by bringing in my existing row headers and records of each customer including long comment sections? Is there another direction I should be looking at?

Thank you.

[MrProgrammer]

Hi, and welcome to the forum.

Is there a quick way to build a database by bringing in my existing row headers and records of each customer including long comment sections?

Yes, if your spreadsheet data is structured correctly. The built-in help for Base explains how. See Help → OpenOffice Base → Index → Search → importing;databases. Or read this tutorial, beginnng at: From Spreadsheet to ooBase table.
Importing data to Base

Here are some Base tutorials to help you:
Sheepdog Guides Tutorials about using databases
Board index → Getting started → Tutorials → Base

If you need any additional assistance attach a spreadsheet demonstrating the situation (remove confidential information then use Post Reply, not Quick Reply, and don't attach a picture instead of the spreadsheet itself). You may not get much help until we see how the spreadsheet is arranged. You will need to explain in detail why the procedure in the built-in help doesn't do what you want.

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.

[Night Owl]

Mr. Programmer, thank you for your response.

Attached is a workbook with a couple example sheets.

In my attempts to automatically build a database from this data, I quickly become confused. I though this would be easier but I'm getting on in years and my eyes aren't as wide as they used to be. I've watched videos, I've saved the data as CSV and started a new database with that data and I hit the wall. I am going to try it again, but I wanted to get this sent.

Thank you!

[MrProgrammer]

Attached is a workbook with a couple example sheets.

Your spreadsheet is not structured so you can use it to import data into Base. Base cannot use your current spreadsheet. Here's one way how it could be organized for import. You could have three tables in Base: Customers, Machines, Services.
• Customers stores one row of data for each customer.
• Machines stores one row of data for each customer's machine. The row contains the customer's ID.
• Services stores one row of data for each service activity you need to record. The row contains the machine's ID.

202607281123.ods

(16.97 KiB) Downloaded 3 times

From the data you supplied you have four customers. In Base each gets their own numeric ID in the Customer table. And you will have four machines, each with their own numeric ID in the Machines table. Each customer has presented one machine. You can't put the machine data in the Customer table because a customer could have more than one machine. If an existing customer needed help with a second machine you would still have four customers, but now there would be five machines. You have 13 service interactions, three for customer 1, two for customer 2, six for customer 3, two for customer 4. The Services table will have 13 rows of data.

I've reorganized your data into that structure and I think you would be able to import it to Base. You'd import the three sheets into three tables. As you can see, you will need to completely change the organization of your data if you want to use Base. A sheet in Calc becomes a table in Base. Now you use a separate sheet for each customer, but you can't use a separate table for each customer because it's impractical to add a new table each time you have a new customer. A column in a Calc sheet becomes a field in a Base table. You cannot run different service dates across columns as you currently do because it's impractical to add a new field to a Base table each time you perform a service. I haven't worked with Base much myself, mostly Writer and Calc, and I've never done a copy from Calc to a Base table. If you need further help using Base for an import, I can't help with that process. Base help says you can do it, but only if the data meets Base's requirements. You might want to skip the first column (the ID) and let Base generate the key; read the tutorial. I do know that your existing spreadsheet structure will not work for import.

Others who answer questions in the Base forum have said that LibreOffice Base has fewer problems than OpenOffice Base. So you may want to build your database with LibreOffice.

If this solved your problem please go to your first post use the Edit ✏️ button and add [Solved] to the start of the Subject field. Select the green checkmark icon at the same time.