Pulling data from closed CSV file

Hi guys and girls,

I've got a sheet in which I need to look at data that I pull in from a CSV file.
Now I can pull in all the data into the sheet by just importing the CSV, but it will be hundreds of thousands of rows and to be honest, it makes the file too big and calculating it every time too slow.

So here's the setup :

I have around 500 different products - each identified by a PRODUCT_ID
I want to find out where these products are typically shipped to - that's where the CSV file comes in.
The CSV contains 6 columns as below (these are the headers) - for each day (DAY, column 1) there is a record of all (AMOUNT, column 6) products (PRODUCT_ID, column 3) that are shipped to a warehouse (WH, column 5).
DAY, LOC, PRODUCT_ID, PRODUCT_CODE, WH, AMOUNT

I want to be able to set a date period (for example 1/1/2015 - 1/31/2015) and for each product go through the rows of those dates and find the division of how many are shipped to each warehouse.

So in the end, I'd like the result to look something like this:
PRODUCT_ID TIME PERIOD WH AMOUNT % OF TOTAL
X322-AC 01/01/2015 - 01/31/2015 AMS 458 8.40%
X322-AC 01/01/2015 - 01/31/2015 BRU 1,118 20.50%
X322-AC 01/01/2015 - 01/31/2015 … … …
X322-AC 01/01/2015 - 01/31/2015 … … …

Now, I know how to do this slowly by importing all the data from the CSV into my sheet, then filtering on the product id, looping through the visible cells and assign each warehouse to a variable, then divide the variables by the total to get the percentage per warehouse.

But - IDEALLY, I'd like not to have to import the CSV contents at all, but just pull the relevant numbers from the closed file and only show the result.
Can this be done?

Name of the CSV will be "shipments.txt" and it's tab delimited.

Thanks,

Jasper

@buran : I actually haven't worked with ADODB yet, just a bit, so if you can give me some pointers, then it'd be awesome!
@michson : fantastic, I'll look at that code later on and will let you know the result!
@mike : I really wish I could, but it's all pretty sensitive data, so I cannot Sorry!

See attached, hope it is useful!sample.txt

Hi Kyle,

awesome, thanks for your reply! I'll check it out and come back if I have any additional questions.
Thanks,

Jasper

Great, thanks a LOT guys!

Now, to track back for a second on Kyle's original code which pulls everything into the rs dataset.
Do I have to write the dataset to the sheet or could I work with the dataset saying for example "I have product ID X334-QR, so can I quickly see the division on warehouse sales for this item over the time period" ?
Cause as I see it now, I'd still have to loop through it per product ID ...?

Or am I missing something here?

Or am I missing something here?

Yes, you only select the data you want in the first place

OK, but opening the data connection takes a bit of time, because the source file is quite large.
if I only select the data I want in the first place, doing that per product_ID and time frame, will that be instantly after first opening the data connection or do i have close and re-open every time I change the product ID?

Looking at Buran's code, I feel i'd have to close & reopen every time.
Looking at Kyle's first code, I open once and then set the dataset based on each product_id

Then pull all the product codes and filter them in Excel?

You're still reducing the data since you've summarised by date range

First off - I apologize for being unclear.

On sheet1, there's 500 rows of product IDs.
For each product ID, I want to know the warehouse division over a certain time period.
My source txt file contains _all_ product IDs, so I want to narrow it down to just the one product ID on the row I'm looking at at that moment.

I understand that I can do this by
1) pulling all the data, filter on the product id and loop through the visible cells.
2) pulling just the data for the product_id, but then I'll have to pull it 500 times.


My considerations to see if it can be done in another way are as follows:
Filtering and looping through (hundreds of) thousands of rows for 500 times will be slow - so ideally, I was hoping I can just pull in a dataset through ADODB and then work with that dataset in memory, which should be infinitely faster than doing it on the worksheet.

I understand from the previous replies that I cannot do that.

Can I instead, pull in the data through ADODB to sheet2 and then put that data into an array in memory and just loop through that?
Or will that be slower than filtering/looping on the sheet (considering that I will have to loop through all records, instead of just the visible ones) ?

My final aim = minimize time needed to get the data and minimize file size.

Thanks!

I don't understand why you don't just pull it for the 500 you want all at once by passing their IDs in - have a look at the in clause:

Please Login or Register  to view this content.

romperstomper : that's the answer I was looking for. Now the question is : how
Kyle: I do that - by importing * it would only have all the shipments from those 500 product_ids - the raw data txt file doesn't contain other product_ids

For example, if I pull in data set of 01/01/2015 - 02/28/2015 I get just over 30k rows. That's a lot to filter and loop through for 500 times

There is no looping, did you read my last post?

Hi Kyle,

I read your last post.
Then I read it again and actually understood it combined with the post on the previous page.
This solution is excellent, thank you so much!
Problem solved.

However - just for my future reference, how would I be able to filter on a dataset?
Thanks,

Jasper

That's very cool, Thanks!!

Oh and if I try to do 'rst.sort = "WH DESC"' it tells me:
Run-time error '3251':
Current provider does not support the necessary interfaces for sorting or filtering

Which is weird, cause I just filtered
Is there an easy way to sort like this as well or should I do that on pulling in the data ?

Thanks again - do you have any easy url for me to read up on all options by any chance?