[Solved] Can I Bulk Update Fields with a CSV file?

dBase, Calc, CSV, MS ACCESS, MySQL, PostgrSQL, OTHER

[Solved] Can I Bulk Update Fields with a CSV file?

Postby strangeChild » Fri Oct 30, 2015 3:02 am

I am playing around with a database to see if it will help me keep track of inventory for my online business. I would like to be able to bulk update the quantity field for all of my products with a CSV file but I can't figure out if Base can do that.

I've found directions for creating new tables with an import, but nothing has appeared to be instructions for a bulk update to an existing table.

Can anyone point me in the right direction?
Last edited by Hagar Delest on Fri Feb 26, 2016 10:51 pm, edited 1 time in total.
Reason: tagged [Solved].
Open Office 4.1.1 & Windows 10
strangeChild
 
Posts: 4
Joined: Fri Oct 30, 2015 2:48 am

Re: Can I Bulk Update Fields with a CSV file?

Postby mgroenescheij » Fri Oct 30, 2015 6:30 am

I doubt if you can run an update with a CSV file as the language for maintaining the data is SQL.
But why don't you import your CSV file, create a relation and update it with an SQL statement?
AOO 4.1.3 on MS Windows 10
Please add '[Solved]' at beginning of your first post title (edit button) if your issue has been fixed.
mgroenescheij
Volunteer
 
Posts: 289
Joined: Thu Apr 23, 2009 10:19 pm
Location: Sydney Australia

Re: Can I Bulk Update Fields with a CSV file?

Postby strangeChild » Fri Oct 30, 2015 2:35 pm

Mainly because I am a total n00b. ;)

But in all seriousness, thank you! That gives me some idea of what I need to be researching / learning about.
Open Office 4.1.1 & Windows 10
strangeChild
 
Posts: 4
Joined: Fri Oct 30, 2015 2:48 am

Re: Can I Bulk Update Fields with a CSV file?

Postby Villeroy » Fri Oct 30, 2015 2:51 pm

Since we know nothing about your ingoing data, the already stored data nor the general purpose of your database, one general advice: A business application which stores all prices for all products at all times is more useful than a database reflecting only one snapshot in time constantly overwriting old prices with new prices. A more complicated set of relations would insert new records with a current time stamp into a table of prices and article numbers.
Date | ArticleID | Price
2015-10-31 | 9999 | 34.71
With this setup you could watch the development of prices and you could reproduce past invoices with correct prices that where valid on the day of invoicing. Both is impossible if you have only the current prices.
The queries would be less trivial of course.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24652
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can I Bulk Update Fields with a CSV file?

Postby strangeChild » Fri Oct 30, 2015 11:12 pm

OK, here is a little more information about what I'm hoping to do.

I created a database with all the info for my products.

Some of those products are dropshipped, basically my vendor ships it to my customer when an order is made. It means I have no control over what is / isn't in stock.

BUT every day my vendors provide me with a CSV file with the exact number of products available. I would like to use this csv file to update the quantity field in my product database. This way I never sell anything I can't actually get.

I'm using the vendor's part number as a unique identifier.

Do any of ou think this is something base can help me with?
Open Office 4.1.1 & Windows 10
strangeChild
 
Posts: 4
Joined: Fri Oct 30, 2015 2:48 am

Re: Can I Bulk Update Fields with a CSV file?

Postby Villeroy » Fri Oct 30, 2015 11:40 pm

Anyway, you can append data by means of SQL INSERT. Link the incoming csv file to a text table and drag the text table over the storage table which triggers an import wizard. If the column order of the import table matches the column order of the data table, all you need to do is confirm this dialog. The data table needs some auto-ID and an automatic time stamp field at the end of the column order. Then you can query the latest status for any product at any time.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24652
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can I Bulk Update Fields with a CSV file?

Postby Villeroy » Thu Nov 05, 2015 1:06 am

Download the attached zip archive from this topic: https://forum.openoffice.org/en/forum/v ... 00&t=23727
Open the CSV_Editor.odb and add a table like this:
menu:Tools>SQL...
Code: Select all   Expand viewCollapse view
CREATE TABLE "Data"
("ForeignID" INTEGER NOT NULL,
"Date" DATE DEFAULT CURRENT_DATE NOT NULL,
"Group" CHAR(1) NOT NULL,
"Amount" DECIMAL(5,2) NOT NULL,
"Stamp" TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
"ID" INT GENERATED BY DEFAULT AS IDENTITY
);

menu:View>Refresh
Then drag the linked csv table over the Data table and choose to append data.
The ID in the csv file corresponds to the "ForeignID", the "Date", "Group", "Amount" correspond to their respective counterparts while the "Stamp" and auto-ID will not be touched by the import data. Just hit enter to confirm the import dialog with the given order of columns. Next csv file with the same name and column order will do the same. Your data collection will grow with a minimum of effort and without loss of information.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24652
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany

Re: Can I Bulk Update Fields with a CSV file?

Postby strangeChild » Fri Nov 06, 2015 3:40 pm

Awesome. Thanks!
Open Office 4.1.1 & Windows 10
strangeChild
 
Posts: 4
Joined: Fri Oct 30, 2015 2:48 am

Re: Can I Bulk Update Fields with a CSV file?

Postby Villeroy » Fri Nov 06, 2015 3:46 pm

If your incoming data need some pre-processing (changing order of columns, doing calculations, converting strings to ISO dates etc.) then you can create a view and drag that view over the data table. A view is almost the same as a query but with a different scope.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 16.04, OpenOffice 4.x & LibreOffice 5.x
User avatar
Villeroy
Volunteer
 
Posts: 24652
Joined: Mon Oct 08, 2007 1:35 am
Location: Germany


Return to External Data Sources

Who is online

Users browsing this forum: No registered users and 1 guest