Pulling data from closed CSV file

**JasperD** · 03-13-2015, 04:32 AM

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

**mike7952** · 03-13-2015, 04:48 AM

Can you upload a example of the csv file and an exaple excel file you want to see for the output? Also any code that you have already, if you do.

**michson** · 03-13-2015, 04:50 AM

Ready code for your issue:

Sub ImportFromCsvTxt()
  Dim FPath As String
  Dim NextFreeFile As Integer
  Dim RowNo As Long
  Dim Sht As Worksheet
  Dim LineFromLine As String
  Dim LineItem() As String
  Dim i As Long

  With Application.FileDialog(msoFileDialogFilePicker)
    .AllowMultiSelect = False
    .Title = "Select txt/csv file"
    .Filters.Add "Files CSV/Txt", "*.csv; *.txt", 1
    .FilterIndex = 1
    .Show
      If .SelectedItems.Count <> 0 Then
        FPath = .SelectedItems(1)
      Else
        MsgBox "No file!"
        Exit Sub
      End If
  End With
  
  NextFreeFile = FreeFile
  
  Open FPath For Input As NextFreeFile
  
  Set Sht = ActiveSheet
  RowNo = 1
  
  Do Until EOF(NextFreeFile)
    Line Input #NextFreeFile, LineFromLine
    
    LineFromLine = Replace(LineFromLine, """", "")
    LineItem = Split(LineFromLine, ",")
  
      For i = LBound(LineItem) To UBound(LineItem)
    
        Sht.Cells(RowNo, i + 1) = LineItem(i)

      Next i

    RowNo = RowNo + 1

  Loop
  
  Close NextFreeFile
  
End Sub

**buran** · 03-13-2015, 05:18 AM

ADODB, SQL.. etc.
you know the rest...

**JasperD** · 03-13-2015, 05:28 AM

@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!

**buran** · 03-13-2015, 06:19 AM

Find attached zip.
it contains the sample.xlsm file, sample.txt you have uploaded, as well schema.ini file that specify that txt file is tab-delimited.
Note that maybe it's possible to change the sql statement to include the calculation of % of total, but I didn't manage to do so. That's why I use formula to calculate the %.
If someone else could change the sql to do the calculation I would be curious to see it too.

**buran** · 03-13-2015, 05:31 AM

Can you upload some sample txt file?
if data are sensitive, just put few rows of mock data

**Kyle123** · 03-13-2015, 05:38 AM

You'll need to tweak the query and I suspect that you'll need a schema.ini file, but here's the basics:

Sub test()

    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset
    Dim sql As String

    Set cn = New ADODB.Connection
    cn.ConnectionString = _
        "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\MYFILEPATH;Extended Properties='text;HDR=Yes';"
    cn.Open

    sql = "Select * FROM " & "[MYFILE.CSV]"
    
    Set rs = cn.Execute(sql)

End Sub

**JasperD** · 03-13-2015, 05:47 AM

See attached, hope it is useful!sample.txt

**JasperD** · 03-13-2015, 05:50 AM

Hi Kyle,

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

Jasper

**Kyle123** · 03-13-2015, 06:39 AM

Reckon this'll get it:

Public Sub QueryTextFile()
    Dim rsData As ADODB.Recordset
    Dim sConnect As String
    Dim sSQL As String
    Dim lngLastRow As Long
    Dim strStartDate As String
    Dim strEndDate As String

    strStartDate = "01/01/2015"
    strEndDate = "01/31/2015"


    ' Create the connection string.
    sConnect = "Provider=Microsoft.ACE.OLEDB.12.0;" & _
               "Data Source=" & ThisWorkbook.Path & "\;" & "Extended Properties=Text;"
    ' Create the SQL statement.
    sSQL = "SELECT PRODUCT_ID,'" & _
           strStartDate & "-" & strEndDate & _
           "', WH, Sum(AMOUNT), SUM(AMOUNT)/(SELECT SUM(AMOUNT) FROM sample.txt WHERE DAY >=#" & strStartDate & "# And DAY<=#" & strEndDate & "#)" & _
           "FROM sample.txt " & _
           "WHERE DAY >=#" & strStartDate & "# And DAY<=#" & strEndDate & "# " & _
           "Group By WH,PRODUCT_ID;"
           Debug.Print
    Set rsData = New ADODB.Recordset
    rsData.Open sSQL, sConnect, adOpenForwardOnly, adLockReadOnly, adCmdText
    ' Check to make sure we received data.
    If Not rsData.EOF Then
        ' Dump the returned data onto Sheet1.
        Range("A1").CopyFromRecordset rsData

    Else
        MsgBox "No records returned.", vbCritical
    End If
    ' Clean up our Recordset object.
    rsData.Close

End Sub

Not the best performance wise (might still be better with formula on a large dataset)

SQL:

SELECT product_id, 
       '01/01/2015-01/31/2015', 
       wh, 
       SUM(amount), 
       SUM(amount) / (SELECT SUM(amount) 
                      FROM   sample.txt 
                      WHERE  day >=# 01/01/2015# 
                             AND day <=# 01/31/2015#) 
FROM   sample.txt 
WHERE  day >=# 01/01/2015# 
       AND day <=# 01/31/2015# 
GROUP  BY wh, 
          product_id;

**JasperD** · 03-13-2015, 07:09 AM

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?

**buran** · 03-13-2015, 07:16 AM

Originally Posted by JasperD

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.

my initial understanding was you want the data for all product_ids within the date frame. if you want to see data just for one Product ID, then change the WHERE clause(s) to add also condition on Product_id

**Kyle123** · 03-13-2015, 07:20 AM

Or am I missing something here?

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

**JasperD** · 03-13-2015, 07:29 AM

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

**Kyle123** · 03-13-2015, 07:30 AM

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

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

**buran** · 03-13-2015, 07:35 AM

Originally Posted by JasperD

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?

I really don't understand what you want - data for one Product ID or data for all?
If you want for one (or several) you can specify this in the where clause of the sql statement. and then dump all data on worksheet like in the sample or loop trough recordset and do anything you want record by record.
If you want for all - no need to add anything in the where clause and again you can dump all data on the sheet or loop trogh the recordset and do [anything] with the data record by record.

**JasperD** · 03-13-2015, 08:10 AM

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!

**romperstomper** · 03-13-2015, 08:11 AM

You can filter a recordset too.

**Kyle123** · 03-13-2015, 08:18 AM

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:

SELECT product_id, 
       '01/01/2015-01/31/2015', 
       wh, 
       SUM(amount)
FROM   sample.txt 
WHERE  day >=# 01/01/2015# 
       AND day <=# 01/31/2015# 
       AND product_id in ('X355-DE','X355-DE')
GROUP  BY wh, 
          product_id;

**JasperD** · 03-13-2015, 08:33 AM

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

**Kyle123** · 03-13-2015, 08:35 AM

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

**JasperD** · 03-13-2015, 08:50 AM

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

**romperstomper** · 03-13-2015, 10:54 AM

It's just:

   rst.Filter = "[FieldName] = 'Criterion'"

**JasperD** · 03-13-2015, 11:35 AM

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?

**romperstomper** · 03-13-2015, 12:14 PM

Welcome to the weird world of ADO.

You need to specify a client side cursor (it will default to server) before you open the recordset, then the Sort will work:

   rst.CursorLocation = adUseClient

MSDN is a pretty good source for this.

Hybrid View

Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Re: Pulling data from closed CSV file

Thread Information

Users Browsing this Thread

Similar Threads

Bookmarks

Bookmarks

Posting Permissions