Import CSV and create reports

Discuss the database features
Post Reply
Dropout
Posts: 17
Joined: Tue Sep 23, 2008 10:49 pm

Import CSV and create reports

Post by Dropout »

Hi Everyone

I will try to explain clearly what I want to do.

I use Simply Accounting Canadian Version and can use it to enter orders that I get from my clients.

I can export a csv file summarizing those orders.

I want to create a dbase of part numbers with the relevant BOM and order of workstations needed to produce each part.

So, I think I want to read the csv file from Simply with part numbers and due dates, pick up the additional data from my dbase and using some kind of query produce production reports.

If it's not too complicated, I'd like to be able to check off each part at each workstation so I can see what the status is of the parts being produced and then check them off when done.

Is it possible, and if so, are there any templates I should consider using to start?

Thanks in advance.
Last edited by MrProgrammer on Fri Feb 14, 2020 10:35 pm, edited 1 time in total.
Reason: Changed subject, was: Is this possible?
OOo 2.4.X on MS Windows Vista
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Is this possible?

Post by UnklDonald418 »

Yes it is possible. For one approach see
[Tutorial] Using csv/text files as editable data source
An alternative would require a two steps.
Import the csv data into a Calc spreadsheet then
copy from the spreadsheet to a Base table.
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
User avatar
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Is this possible?

Post by Villeroy »

If you can connect a Base document to the "Simply Accounting" database you may save all the csv trouble and query directly from the original database. You may also create a Calc pivot table as a more flexible approach to build an aggregation query.
If csv is inevitable and all you need is a view on that database (no editing by office suite), you can also connect the csv file(s) to a Base document and use simple queries * and/or pivot tables.

[*] "simple queries" from csv files are limited to one table with no aggregates (sum, count, average, min, max) and with a limited set of functions. Pivot tables are the only way to aggregate csv data.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Dropout
Posts: 17
Joined: Tue Sep 23, 2008 10:49 pm

Re: Is this possible?

Post by Dropout »

Thanks. I'll experiment to see what works best.

Then how to combine the data from the imported data with the existing dbase that I will make with the extra part number data that I need?

Do I need to take both sets of data and create a new table to work with?
OOo 2.4.X on MS Windows Vista
User avatar
Villeroy
Volunteer
Posts: 31270
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Is this possible?

Post by Villeroy »

We can not tell anything because we do not know anything about your database(s). So far we only know that you are using an application called "Simply Accounting" which is able to export some kind of text (csv). But we don't know which type of database is used by that application nor which flavour of csv it exports. We do not see what you have, so we can not know how to do anything with it.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Dropout
Posts: 17
Joined: Tue Sep 23, 2008 10:49 pm

Re: Is this possible?

Post by Dropout »

Thanks for the response Villeroy

Would it help if I posted a small sample of each? Not quite sure of the proper protocol here - don't want to offend or assume too much.

Simply (better known as Sage 50) will export an xls file if that matters.
OOo 2.4.X on MS Windows Vista
UnklDonald418
Volunteer
Posts: 1544
Joined: Wed Jun 24, 2015 12:56 am
Location: Colorado, USA

Re: Is this possible?

Post by UnklDonald418 »

Database design requires intimate knowledge of the available inputs and the desired outputs.
Samples improve the likelihood of forum volunteers understanding your inputs and outputs and thus being able to offer relevant advice.
Have you tried any of the suggestions already given?
What were the results?
If your problem has been solved, please edit this topic's initial post and add "[Solved]" to the beginning of the subject line
Apache OpenOffice 4.1.14 & LibreOffice 7.6.2.1 (x86_64) - Windows 10 Professional- Windows 11
Post Reply