Page 1 of 1

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

PostPosted: Fri Oct 30, 2015 3:02 am
by strangeChild
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?

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

PostPosted: Fri Oct 30, 2015 6:30 am
by mgroenescheij
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?

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

PostPosted: Fri Oct 30, 2015 2:35 pm
by strangeChild
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.

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

PostPosted: Fri Oct 30, 2015 2:51 pm
by Villeroy
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.

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

PostPosted: Fri Oct 30, 2015 11:12 pm
by strangeChild
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?

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

PostPosted: Fri Oct 30, 2015 11:40 pm
by Villeroy
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.

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

PostPosted: Thu Nov 05, 2015 1:06 am
by Villeroy
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.

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

PostPosted: Fri Nov 06, 2015 3:40 pm
by strangeChild
Awesome. Thanks!

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

PostPosted: Fri Nov 06, 2015 3:46 pm
by Villeroy
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.