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

[cul]

...in column A of Sheet 2.

Sheet 2 looks like:


Sheet 1 should look like:


(there are 45,000 lines of this, so I'm trying to cut it down to make the processing faster when uploading changes to my data. Only lines with data in Sheet 2 have changes, but that will change with every upload)

[FJCC]

Select all the cells you want to work with in Sheet2 and then go to the menu item Data -> Filter -> Standard Filter. Set the Field Name to ID and the Value to Not Empty. Click More Options, select Copy Results To and in the rightmost box enter Sheet1.A1. Click OK and the data where ID is not empty will be copied into Sheet1.

[robleyd]

Have you tried using Standard Filter - Data | Filter | Standard Filter - and setting Sheet 1 A1 as the target in Copy results to; find this under More options

[cul]

Thanks, trying that right now. It's locked calc up but I assume its related to the amount of lines its trying to deal with? Hopefully it will resolve...

It resolved but it brought across everything that was on sheet 2. It didn't remove rows with a blank cell in Sheet 2 column A.

OK. I know what the problem is. The "blank" cells in Sheet 2 column A actually have a formula in them. Just that in many cases the result is blank.
Sheet2.A2 comes from Sheet3.A2 which comes from sheet 4 A2=IF('file:///C:/Users/user/Downloads/import export/wc-product-export.csv'#$'wc-product-export'.D2-F2=0, "", 'file:///C:/Users/user/Downloads/import export/wc-product-export.csv'#$'wc-product-export'.A2).(Sheet 4 determines if there have been any changes on the external inventory spreadsheets)

Is there a way to deal with that? I guess I could change "" to "IGNORE" and then filter Sheet 2 Column A for is not "IGNORE"?
-Nope. That dosn't work.

Sorry. Anything else I can try?

[Zizi64]

Anything else I can try?

Please upload or share an ODF type sample file here.

[cul]

Please upload or share an ODF type sample file here.

Thanks!

https://easyupload.io/xbrluz

[Zizi64]

My first tips:
- Do not use special characters and White spaces in a file name or a sheet name. It will increase the file size, and it will slowener it.
- Do not use external links in a sample file - these will not work on the computer of the helper.

[cul]

Hi, sorry, I didn't think you'd specifically need those files to check re: sheet1/sheet 2. I'll remove the spaces. thanks.

Here they are:
https://easyupload.io/nl0dd4
https://easyupload.io/q30lby

Thanks!

[Zizi64]

There is a lot of irregular usage of the function VLOOKUP.

And there is not any similar data structure in your sample file that you showed in the opening post.

[cul]

There is a lot of irregular usage of the function VLOOKUP.

IrregularVLOOKUPs.png

And there is not any similar data structure in your sample file that you showed in the opening post.

I'm sure my usage of VLOOKUP is highly irregular. I thought I needed it to make sure I had the data correct. There are multiple data sources and I didn't know how else to make them match up.

The data structure was changed from the opening post when the suggestion I was given re: filtering didn't work. I changed the file so that no change to the data resulted in "IGNORE" rather than a "blank" field.

[Zizi64]

My another tips:

Try to open the .csv files directly, and save them as .ods document. Copy one into a sheet of the another file if it is necessary.
Work with this file/data directly - instead of the lot of references to the .csv files.

Do not use the VLOOKUP such irregular way. use the MATCH or otther function for checking data.

[cul]

Try to open the .csv files directly, and save them as .ods document. Copy one into a sheet of the another file if it is necessary.

Let me explain my thinking somewhat and you can tell me if I'm still wrong! The 2 additional csv files are the inventory on my website and the inventory on a 3rd party site I sell on. Both sites have simple export functions via csv. My plan was to save those exported files to the same name and location every time I download them and then hit update data on the main matching ODS sheet that I sent you. This would then correct the quantities/prices/names and spit out a relatively simple csv file that I can export back to my website. I thought that would be easier than copy and pasting the 2 csv files into the main spreadsheet.

I'll lookup how to use MATCH. thanks.

[Zizi64]

Let me explain my thinking somewhat and you can tell me if I'm still wrong! The 2 additional csv files are the inventory on my website and the inventory on a 3rd party site I sell on. Both sites have simple export functions via csv. My plan was to save those exported files to the same name and location every time I download them and then hit update data on the main matching ODS sheet that I sent you. This would then correct the quantities/prices/names and spit out a relatively simple csv file that I can export back to my website. I thought that would be easier than copy and pasting the 2 csv files into the main spreadsheet.

It seems as a database-related problem. Sorry i can not help you to achieve this with the Base application.

[Villeroy]

You are misusing an arithmetic calculator as a poor database surrogate. csv data are database data. You import them into a calculator where you don't find enough database functionality.

[cul]

You are misusing an arithmetic calculator as a poor database surrogate. csv data are database data. You import them into a calculator where you don't find enough database functionality.

Yeah, I'm starting to figure this out. I posted in the base forum a couple of years ago but was told "Base is just a front end, it's not a database" and gave up after that. I sell music for a living. I'm just trying to use some tools that will make my life easier. Trying to find the balance between spending time learning computer skills and spending time doing the main job that actually generates revenue. I do need to find a way to auomate more tasks, but it's hard.

[Villeroy]

Either you build up a simple database or you pay someone to do it for you. Apart from these two options, I'm sure there is a ready made software product for what you try to achieve and it is certainly not a spreadsheet. Where do all those csv data come from? It is a database, isn't it? Can't you use that database?

[cul]

Either you build up a simple database or you pay someone to do it for you. Apart from these two options, I'm sure there is a ready made software product for what you try to achieve and it is certainly not a spreadsheet. Where do all those csv data come from? It is a database, isn't it? Can't you use that database?

Well the inventory csv files come from 2 places I suppose. Each distributor emails me a pdf invoice. Each distributor sends the information in a different layout. I convert that pdf invoice to a csv file of items and add it to my main inventory spreadsheet as a new sheet. The columns are Amount/Catalog Number/Artist/Title/Cost. I add 2 more columns: "Price" which is the (cost+shipping)*currency conversion*selling fees*markup and "Sold" which is a column I add 1 to every time an item sells. When the Sold cell is the same size as the Amount cell then I order more copies.

I then copy and paste the new csv file into another template and use it to upload the items to a 3rd party website called discogs. They have a web-based program for matching the catalog number/artist/title to the items on their site so they can be listed for sale.

I have been doing this every week for 4-5 years now so the combined inventory is also available as a csv download file from discogs with 45,000 entries in it. 7000 live "For Sale" items and 38,000 expired "Sold" items. There are many additional columns but the important ones are release ID (a unique integer for every unique version of a release. So Beatles UK Sg Pepper 1970, Beatles US Sg Pepper 1973 etc etc) and sales ID (a unique integer for every single listed item for sale)

I also have my own website which connects to discogs via an API but because discogs does not have a field for "quantity" the quantity data must come from elsewhere. As it happens it comes from a 3d party app that discogs built but were unable to fully integrate into their site.

Discogs is "the database" I suppose, but it's web-based and the only ways to access it is via some limited csv downloads and some limited API access. Building a database to deal with all the information seemed much harder than just having an inventory sheet that I could use "find & replace all sheets" with to manually find a sold item and mark it down as one extra sale. But its taking 2-3 hours a day now because we're selling a lot more than we used to.

I'm not worried about the cost of paying for a database I'm worried about trying to get all the data into it in a meaningful way that actually helps me and the only way seems to be to build it and understand it myself which is a long process.

[Zizi64]

Each distributor sends the information in a different layout. I convert that pdf invoice to a csv file of items and add it to my main inventory spreadsheet as a new sheet.

It is a nightmare...
Ask for data from the distributors in a form that can be processed directly.

[cul]

Each distributor sends the information in a different layout. I convert that pdf invoice to a csv file of items and add it to my main inventory spreadsheet as a new sheet.

It is a nightmare...
Ask for data from the distributors in a form that can be processed directly.

I have and it's not going to happen.

[Villeroy]

What do you need?
So far I see
1) a huge slut bunny file (45,000 rows, 19 columns)
2) an export file (7,200 rows, 13 columns)
3) a massive spreadsheet file that does not load on the small notebook I'm using right now
Let's throw away the spreadsheet and concentrate on the 2 text files.
Slut bunny has 2 columns with identifiers listing_id and release_id
Export has ID, SKU, another one in column I.
How are IDs related to each other? I see that slut bunnie's listing_id has matching values in the SKUs and in column I.
Now your spreadsheet tries to do what exactly? Pull slut bunnies for each export where some id matches? Which pair(s) of columns should match? Which columns should be fetched?

How often do you get new slut bunnies?
How often do you get new export files?

[Villeroy]

SlutBunnies.listing_id seems to serve as a primary key. It has unique integers, no duplicates. So each listing_id refers to one distinct slut bunny.
Same with Export.ID. Each Export.ID refers to one distinct export.
Export.SKU is a partially inconsistent foreign key when referring to SlutBunnies.listing_id. The SKU has 2 missing values @ID 184931 and 206041 and SKU 881514097, 1078880715, 1079925052, 1091633438 have no matching SlutBunnies.listing_id

[Villeroy]

I imported the slut bunny file into an embedded HSQLDB and added a text table linked to a file export.csv which is a slightly tweaked version of your export file.
Extract http://www.mediafire.com/file/gr7ippac9 ... y.zip/file and open the database document.
The form shows export.csv in a table grid and the related slut bunny record below. Both parts are editable. In the upper part you edit the csv file.
There is a query which lists all export records without matching slut bunny.

[cul]

Awesome! Thank you for that. I've been doing some basic reading and I made a report for the query you made that spits out ID/Name/Stock/Category. The report basically lists all those items that are NOT in the main inventory but are still listed on the export file (ie are on my website). So I can use that to delete those items, or list them as zero stock at least.

My next task is:

1. Make a query that pulls the quantity of stock from the main slutbunny inventory sheet (45,000 rows) using something similar to the

Code: Select all

=IFERROR(MID(O2, LEN("[q= "), FIND("]",O2)-LEN("[q= ")),1)+0

line I created for column T.
2. Compare it to the export inventory sheet (7000 rows).
3. Do the same thing for price.
4. Spit out a report for those quantities/prices anywhere differences occur related ("JOINED"?) to the ID from the export inventory sheet.

I'll have a play with the DB you made and see if I can work it out.

Is it possible for reports to spit out a csv/calc file rather than straight text/writer files?
Drag and drop the query to calc! Nice!

[cul]

How often do you get new slut bunnies?
How often do you get new export files?

Ideally I need to update daily to keep them synced.

The slutbunny file (Discogs/45,000 rows) changes by approx 50-150 rows per day. New items are listed. Old items are changed from "For Sale" to "Sold". Prices change on old rows.
The export file (woocommerce/7000 rows) changes daily everytime I use the API to update the data from discogs. It creates a new ID any time there is a new listing_id on discogs and removes an ID if the listing_id is no longer "for sale" on discogs.

export.ID is the page number for the woocommerce site. Each new product listing gets a new unique ID. It is possible to have a stock quantity of any amount on woocommerce.
listing_id is the unique sales page number for the discogs site. It is also the SKU on the woocommerce site. It is NOT possible to have a stock quantity of anything other than 1 on discogs.
release_id is the unique product ID on discogs for the item that is being sold. It is also rel_id on the woocommerce site.

Discogs built a 3rd party app 6 months ago to deal with the quantity=1 issue. This 3rd party app lists the stock quantity data in the [q=n] field within the external_id field. When a listing_id with external_id [q=3] changes from "For Sale" to "Sold" it creates a new listing_id with the same release_id (and price, condition, comments etc etc) but external_id [q=2].

There are multiple instances of the same release_id appearing on multiple listing_id (different condition/price of same item or simply listing errors). I would be willing to remove these instances if it would make things run more smoothly.
I have experimented with using the release_id for the SKU on woocommerce and would be willing to change it again if it would help.

Hopefully that summary helps. There are other issues, but this is the fundamental part I think.

[cul]

4. Spit out a report for those quantities/prices anywhere differences occur related ("JOINED"?) to the ID from the export inventory sheet.

So it seems like I need an OUTER JOIN in my query for this rather than an INNER JOIN right? I need it to highlight cases where either category, quantity or price are not matched between the 2 tables and then get the correct answer from the slutbunny table. I don't see how to do that. The drop down only offers INNER. LEFT. RIGHT. CROSS. Or am I supposed to use something in the criterion box to the effect of IS NOT?

[Villeroy]

You UPDATE the rows where old_table.listing_id=new_table.listing_id. That's an inner join.
And you INSERT (append) rows where old_table.listing_id is null (has no match in old_table). That would be an outer join.
Finally you may DELETE rows where new_table.listing_id is null (no longer exisits in new_table). That would be an outer join on the other side.

It is also possible to build the whole database with HSQL tables linked to csv files. Then you shut down the whole office suite. Replace the linked csv files with new ones and start again. The whole thing will be slower but not as slow as a spreadsheet.
The [q=n] tags are no problem. That is easier to do than in Calc.

[Villeroy]

The attached odb file is somewhat exotic. It embeds a HSQL database but no data because the HSQLDB consists of 2 text table links. Table "export" refers to a file export.csv and table "SlutBunny" refers to a file theslutbunny.csv.

-- Both files are expected to reside in the same directory as the odb document.
-- Both files are UTF-8 encoded with comma as column separator and double-quote as text separator with the first line having column labels.
-- export.csv consists of 5 columns "ID","SKU","Name","Categories","Stock"
-- "Stock" is a number. It must not contain any #N/A strings.

When new files arrive, first close the whole office suite, copy the new files replacing theslutbunny.csv and export.csv respectively. That's it. My simple test form and query seems to work pretty well with this setup. My system warns that LibreOffice does not react anymore because the form takes some time to load. It works pretty fluently when it finally is loaded.

Edit: Attachment deleted. New version on 2020-04-28

[Villeroy]

Code: Select all

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

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

[cul]

-- Both files are UTF-8 encoded with comma as column separator and double-quote as text separator with the first line having column labels.

I had an issue with:
bad TEXT table source file - line number: 1 Attempt to insert null into a non-nullable column: column: Stock table: export

The stock/categories columns are in the opposite order in the "export" file. So I swapped the stock and categories columns in the table edit options (using cut row and paste row and saved it). Closed and reloaded base but got nothing. When I checked the edit window it was back to the original order. So I went to the export file and swapped the columns there. Restarted base. Still getting a blank table for "export" unfortunately.

I also just tried bringover over the "export" file that was in your zip from yesterday but got nothing from that either. Let me know what else I can try.

Thank you for going through this with me, really appreciate it!

Is there a way to do a couple more things?

1. Add a column for price in export.csv (column 5)
2. Use

Code: Select all

=CONCATENATE(C2," - ",D2)

on slutbunny.csv to compare to (and eventually replace) the name column of export.csv I'd also like to use the SUBSTITUTE C2 "\(\d+\)" to replace all examples of an integer within brackets in the "Name" column of slutbunny.csv as explained to me here: viewtopic.php?f=9&t=101685#p490620
3. If part 2 involves making a new table/query from slutbunny.csv does it also make sense to filter for Status="For Sale" items only?

New copies of source files:
https://easyupload.io/mj99dx
https://easyupload.io/tihjyu

[cul]

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.