Import rows into sheet 1 only where there is a value...

[Villeroy]

selects all the [q=NN] quantities for each listing_id or NULL if there is no such tag

On a single occasion, and hopefully more in future, I've had a stock quantity of 3 digits, does this still cover that?
If there is no such [q=NN] tag then the quantity=1.

Try out. Open the query, filter by Q not being null (wait some seconds, text tables are slow), add digits to one of the [q] tags and save the record. The Q will adjust accordingly.

Code: Select all

CAST( substr( "external_id", LOCATE( '[q=', "external_id" ) + 3, LOCATE( ']', "external_id" ) - LOCATE( '[q=', "external_id" ) - 3 ) AS INTEGER ) AS "Q" 

translated to spreadsheet language:

Code: Select all

=IFERROR(VALUE(MID( $X2; FIND( "[q="; $X2 ) + 3; FIND( "]"; $X2 ) - FIND( "[q="; $X2 ) - 3 ));"")

where X2 is the first cell that may have a [q] tag. You have to constantly adjust that formula to the whole column of data.
---------------------
Regarding the text tables:
Each table is predefined special text table with column types, column labels and if the column accepts null values or not (entry required or not). In a second step the predefined text table is linked to a concrete csv file which needs to fulfill the requirements of the predefined table. I just had to use the files you were posting.
Before I was able to create the text tables, I analysed each column's appropriate data type, its maximum text lenghts or max/min numeric value respectively, if there are blank values or not, if there are any text values within a numeric column (which is a common spreadsheet problem), which IDs are unique and which IDs may refer to some other table's ID. From this information I built up the 2 tables. Unfortunately, it is not possible to do this in the Base GUI. It is a feature of the underlying database which is only accessible through SQL commands (in Base: Tools>SQL...). Base shows text tables with a special table icon having a green arrow.
Please post a few lines (header row and some data) of an actual text file of the "export" type and I will be able to adjust the text table accordingly. If your "export" files follow different rules every now and then, this would require another text import strategy. However, even the worst database tool on the planet is easier to handle than a spreadsheet if you use it properly. Base is definitively an insufficient database tool but it can do so much more than Calc.

[cul]

OK, I'll try and work that in. This is the current export file with all the columns I think I need at the moment (and in the correct order!)
https://easyupload.io/wtsxjp

[Villeroy]

I corrected the column order and added the regular price to the definition of table "export". Now it matches your export.csv.

This is the procedure in plain SQL which deletes the table first, then creates a new one and assignes the source file with the right encoding and header row.

Code: Select all

DROP TABLE "export";
CREATE TEXT TABLE "export"(
    "ID" INTEGER NOT NULL PRIMARY KEY,
    "SKU" BIGINT,"Name" VARCHAR(256) NOT NULL,
    "Stock" INTEGER NOT NULL,
    "Regular Price" DECIMAL(6,2) NOT NULL,
    "Categories" VARCHAR(16)
);
SET TABLE "export" SOURCE "export.csv;ignore_first=true;encoding=UTF-8";

I added the regular price to the form. The result is attached to this posting.
Close your office.
Replace the database file with the new one attached here.
Open the database and see if it works with your export csv.

[cul]

OK. I got it working. I had an issue yesterday when I named the source file csv the wrong name for it to work. That's fixed.

However. I feel like while I can see the advantage of this if/when it works in terms of speed I am also totally daunted by the amount of extra work still required to produce the outcome I need. It seems like so many more steps than were involved in doing this with calc?

The problems:
1. I have the quantity data in the qQuanity query. But not all of the data. The examples where quantity = 1 (ie no [q=nnn] data) are not listed.
2. I tried to paste this qQuantity query into a spreadsheet and it brought in all 45,000 rows from the large file.
3. I still need to compare the qQuanity "table" to the export table to find the mismatches and print only the mismatches using the export.ID as the primary key

4. I need to do the same thing you've done with quantity for the name field - pull in the data to a query and then CAST? it to concatenate "Artist - Title" with all instances of an integer within brackets in the Artist field removed.
5. Compare this to the export table and print only the mismatches using the export.ID as the primary key

6. I need to do the same thing you've done with quantity for the category field - pull in the data from theslutbunny.location to a query and then CAST? it to create a text or number based on the 2019.nn or 2020.nn format for year.week
7. Compare that category query to the export.csv and print all examples of the mismatches using the export.ID as primary key.

8. Compary the price field of theslutbunny.csv to the export.csv and print all examples of the mismatches using the export.ID as primary key.

9. Combine all of these into a single new import.csv for importing back to my website that details all export.IDs with changes in either name, quantity, price or category.

[Villeroy]

Calc is by far more inefficient. In a spreadsheet you are constantly importing, exporting, adjusting formulas, dealing with errors and wrong data types.
With a database you have to do the development work only once for years to come.

1. This sets Q to 1 if there is not tag:

Code: Select all

COALESCE(CAST( substr( "external_id", LOCATE( '[q=', "external_id" ) + 3, LOCATE( ']', "external_id" ) - LOCATE( '[q=', "external_id" ) - 3 ) AS INTEGER ),1) AS "Q" 

All comparisons and merging of lists is trivial to do with a database and almost impossible to do with a spreadsheet. Right now I don't have the time to do it.

[Villeroy]

There is a foreseeable problem with our little text database, It is too sluggish to compare tens of thousands records on substring level. This requires true binary database tables.
I added binary tables to the text tables. The database consists of tables "export", "export_CSV", "SlutBunnies" and "SlutBunnies_CSV". The _CSV versions are linked to the text files.
Then I happened to run into a stability problem which is typical for a embedded HSQLDB when doing mass updates. So I converted the embedded HSQL into a better stand-alone database. This is what everybody should do with a productive database which may become very large. No big deal.

---------------------------
I added some macro code and 2 forms with 2 push buttons each.
The first form makes it easy to replace the csv files. Push one of the buttons, pick whatever csv file happens to be the new "export" or "SlutBunny" file and it will be copied into the database directory replacing any existing csv file. This file replacement macro closes the database connection.
This macro does the same as before when you closed the office suite and manually copied the right file to the right place.

When you open the second form, your database connection will be re-opened with the new text tables in place and you see another pair of push buttons. Each of them executes a sequence of two SQL statements. The first statement deletes all data from the respective SlutBunnies or export table, the second statement inserts all the records from the respective _CSV table. In other words, old data are wiped out and replaced with the content of the current csv. This is fast and easy. It would be possible to keep old records that are not in the csv.
Without macro code you could do the same like this:
Open the database with the right csv files in place.
menu:Tools>SQL...
DELETE FROM "SlutBunnies";
DELETE FROM "export"
[Execute...]
menu:View>Refresh Tables
[or delete all rows in the open grid]
Copy "export_CSV" to "export"
Copy the view "vSlutBunny_Import" to "SlutBunnies".

Binary SlutBunnies differs a little bit from SlutBunnies_CSV. I appended a "quantity" column which is filled automatically when inserting the csv records. The view includes the quantity column. Since Base can not edit existing database views, I keep a query "qSlutBunny_Import" which can be edited easily. Then you may delete the view and rebuild the view from the modified query.
----------------------

3. I still need to compare the qQuanity "table" to the export table to find the mismatches and print only the mismatches using the export.ID as the primary key

See query qQuantDiff. I even added a report based on that query.

4. I need to do the same thing you've done with quantity for the name field - pull in the data to a query and then CAST? it to concatenate "Artist - Title" with all instances of an integer within brackets in the Artist field removed.

I would remove that when importing from csv analogue to the added quantity column. There is a query "qNumBrackets" which finds 2 such records with listing_id 435996244 and 519905533 having "Remixed [1]". Do you mean these records?

5. Compare this to the export table and print only the mismatches using the export.ID as the primary key

qMismatchNames returns 70 records where the concatenation "artist - title" differs from the corresponding export.Name with the matching ID

The match/mismatch queries are trivial. Far more trivial than anything you would forced into when using a spreadsheet.

Installation Instructions
The whole package in a zip: https://www.mediafire.com/file/aiwrst4b ... s.zip/file
The contained database document includes a macro which connects the document with the external database when you open the document. All you need to do is extract everything into a "trusted directory" according to Tools>Options>Security>[Macro Security....]>tab "Trusted Sources". Do NOT turn off macro security. Just set up some trusted directories for macro contaminated stuff in your documents folder (no, "Downloads" is a bad candidate because that folder gets anything from anywhere). Call the file picker form and import your csv files. Call the sync form and sync the binary tables with the csv tables.
The database package is small because I removed all binary data and did not include the csv files. However, there is one large file which does not allow to upload the whole thing here. It is the database driver hsqldb.jar of version 2.5.0. I added this driver because it supports the use of regular expressions for non-trivial matching stuff. The driver shipped with your office suite is 13 years old.

Side note: I find it a little bit confusing to deal with "export" and "export.csv" since somebodie's export is our import. We do import these records into our database, don't we?

[Villeroy]

How all the macros are configured:
There is no need to rewrite the code just because you want to use it with another database. I tried my best to make the macro part configurable.

1) There are some general constant declarations on top of module "ImportButtons" such as file name pattern *.csv, dialog title and a default directory where to pick up the files from.

2) Each push button has data in the "Additional Information" property field (edit mode, right-click>Control... scroll down tab "General").
2.1) The SQL executors have a sequence of SQL commands as "additional info"
2.2) The file picker buttons store the path/name where to store the picked up csv file. ./database/export.csv specifies a path relative to the database document. It starts with ./ and uses the slash as path separator. For an embedded database, this would be ./export.csv because the document path is used as the database directory when linking text files with embedded HSQL. Your buttons should copy the picked up files right into the database folder whereever that is. Otherwise they are out of reach for HSQL's SET TABLE command.
If you wish to move your database document to some other place, the file picker could not find the relative directories anymore. If the path does not start with ./ the macro tries to interprete the info as absolute path in system notation such as C:\Path\database\export.csv (Windows backslash as path separator).

3) The auto-install macro which connects the document with its database backend is taken from FreeHSQLDB. On top of the AutoInstall module you find the constant declarations which refer to driver and database relative to the database document. These paths will be stored as absolute paths in the guts of the document. After document has been connected successfully relatively to its driver and database, you may copy it to other places and remove the entry in Tools>Customize>tab:Events which triggers the macro on document open. After the whole package has been moved, you need to reconnect the document copy which is placed in the right place relatively to the database and driver location.

[Villeroy]

This weekend I played a little bit with this database and I noticed that deletion and complete rewrite it is very fast but the database bloats because the free space is not reused.
There are 2 solutions:
1) Run SHUTDOWN COMPACT before closing the office suite. This command takes minutes to complete. The program is not frozen. It just takes a while to complete.
2) You change the SQL commands so you insert csv data that do not exist in the binary table. You update all existing recordsd without wiping them out and you delete only those records which exist in the binary table but not in the csv. You may also decide to not delete old records.
This is the modified SQL code for the "Sync Export" button. If you decide to not delete any records, remove the DELETE statement at the end.

Code: Select all

UPDATE "export"
SET "SKU" =(SELECT "SKU" FROM"export_CSV"WHERE "ID"="export"."ID"),
"Name" =(SELECT "Name"FROM"export_CSV"WHERE "ID"="export"."ID"),
"Stock" =(SELECT "Stock"FROM"export_CSV"WHERE "ID"="export"."ID"),
"Regular Price" =(SELECT "Regular Price"FROM"export_CSV"WHERE "ID"="export"."ID"),
"Categories" =(SELECT "Categories"FROM"export_CSV"WHERE "ID"="export"."ID")
WHERE EXISTS (SELECT "ID"FROM "export_CSV"WHERE "export_CSV"."ID" = "export"."ID");
INSERT INTO "export"("ID","SKU","Name","Stock","Regular Price","Categories" )
SELECT "ID","SKU","Name","Stock","Regular Price","Categories" FROM "export_CSV"
WHERE NOT EXISTS (SELECT "export"."ID" FROM "export" WHERE "export_CSV"."ID" = "export"."ID");
DELETE FROM "export"WHERE NOT EXISTS (SELECT "ID"FROM "export_CSV"WHERE "export_CSV"."ID" = "export"."ID")

And this is the SQL for the SlutBunnies table

Code: Select all

UPDATE "SlutBunnies"
SET "artist" =(SELECT "artist" FROM"SlutBunnies_CSV"WHERE "listing_id"="SlutBunnies"."listing_id"),
"title" =(SELECT "title"FROM"SlutBunnies_CSV"WHERE "listing_id"="SlutBunnies"."listing_id"),
"label" =(SELECT "label"FROM"SlutBunnies_CSV"WHERE "listing_id"="SlutBunnies"."listing_id"),
"catno" =(SELECT "catno"FROM"SlutBunnies_CSV"WHERE "listing_id"="SlutBunnies"."listing_id"),
"format" =(SELECT "format"FROM"SlutBunnies_CSV"WHERE "listing_id"="SlutBunnies"."listing_id"),
"release_id" =(SELECT "release_id"FROM"SlutBunnies_CSV"WHERE "listing_id"="SlutBunnies"."listing_id"),
"status"  =(SELECT "status"FROM"SlutBunnies_CSV"WHERE "listing_id"="SlutBunnies"."listing_id"),
"price"  =(SELECT "price"FROM"SlutBunnies_CSV"WHERE "listing_id"="SlutBunnies"."listing_id"),
"listed" =(SELECT "listed"FROM"SlutBunnies_CSV"WHERE "listing_id"="SlutBunnies"."listing_id"),
"comments" =(SELECT "comments"FROM"SlutBunnies_CSV"WHERE "listing_id"="SlutBunnies"."listing_id"),
"media_condition" =(SELECT "media_condition"FROM"SlutBunnies_CSV"WHERE "listing_id"="SlutBunnies"."listing_id"),
"sleeve_condition" =(SELECT "sleeve_condition"FROM"SlutBunnies_CSV"WHERE "listing_id"="SlutBunnies"."listing_id"),
"accept_offer" =(SELECT "accept_offer"FROM"SlutBunnies_CSV"WHERE "listing_id"="SlutBunnies"."listing_id"),
"external_id" =(SELECT "external_id"FROM"SlutBunnies_CSV"WHERE "listing_id"="SlutBunnies"."listing_id"),
"weight" =(SELECT "weight"FROM"SlutBunnies_CSV"WHERE "listing_id"="SlutBunnies"."listing_id"),
"format_quantity" =(SELECT "format_quantity"FROM"SlutBunnies_CSV"WHERE "listing_id"="SlutBunnies"."listing_id"),
"flat_shipping"  =(SELECT "flat_shipping"FROM"SlutBunnies_CSV"WHERE "listing_id"="SlutBunnies"."listing_id"),
"location" =(SELECT "location"FROM"SlutBunnies_CSV"WHERE "listing_id"="SlutBunnies"."listing_id"),
"quantity"=(SELECT CASEWHEN(LOCATE('[q=',"external_id")>0,CAST(SUBSTR("external_id",LOCATE('[q=',"external_id")+3,LOCATE(']',"external_id")-LOCATE('[q=',"external_id")-3)AS INTEGER),1)FROM"SlutBunnies_CSV"WHERE "listing_id"="SlutBunnies"."listing_id")
WHERE EXISTS (SELECT "listing_id"FROM "SlutBunnies_CSV" WHERE"SlutBunnies_CSV"."listing_id" = "SlutBunnies"."listing_id");
INSERT INTO "SlutBunnies" ("listing_id","artist","title","label","catno","format","release_id","status","price","listed","comments","media_condition","sleeve_condition","accept_offer","external_id","weight","format_quantity","flat_shipping","location","quantity") SELECT "listing_id","artist","title","label","catno","format","release_id","status","price","listed","comments","media_condition","sleeve_condition","accept_offer","external_id","weight","format_quantity","flat_shipping","location",casewhen(LOCATE( '[q=', "external_id" )>0,CAST( substr( "external_id", LOCATE( '[q=', "external_id" ) + 3, LOCATE( ']', "external_id" ) - LOCATE( '[q=', "external_id" ) -3 ) AS INTEGER ),1) AS "Q" FROM "SlutBunnies_CSV"
WHERE NOT EXISTS (SELECT "listing_id"FROM "SlutBunnies" WHERE"SlutBunnies_CSV"."listing_id" = "SlutBunnies"."listing_id");
DELETE FROM "SlutBunnies"WHERE NOT EXISTS (SELECT "listing_id"FROM "SlutBunnies_CSV" WHERE"SlutBunnies_CSV"."listing_id" = "SlutBunnies"."listing_id")

Add these statements to "Additional Info" of the respective push buttons on the sync form,

Edit: 2020-5-7: Minor change. UPDATE, INSERT, DELETE is more efficient than INSERT, UPDATE DELETE. No need to update stuff that was not there in the first place.

[cul]

Thanks for this. I'll try and take a look this week. I spent the weekend experimenting with zoho inventory and a couple of other database inventories. The issue I'm having with base is not that its less efficient than calc. Its obviously more efficient. The problem is me. It has taken me 3 years to learn to speak basic calc. Trying to learn base in addition to that is daunting and slow. If I plan to build multiple databases then its worth the investment in my brain, but if its just a one off thing then its less obviously beneficial. I'm going to try these things you sent though and see if anything starts to click! Thank you!

[Villeroy]

Very similar topic in parallel, just finished (I hope): viewtopic.php?f=13&t=101801
Simple external HSQL2 database with a csv file receiving the result set of a filtered view. If this is all he needs, this little application will work for him without understanding how it was made.