Extract the first 20 transactions from csv

[Rodrigues]

All
File 20 First transactions V1.xlsm (attached), contains a piece of VBA code kindly given by the Ninjas.
The problem I have now is that, rather then have to return the very first transaction from the source file I have to return the first 20, also the layout has changed.

Each worksheet represents the day od the month (Sheet23 = 23APR ; Sheet24= 24APR)
Aim: extract the data (time) without opening source .csv file.
Return the 20 first transactions of each team.
Typed in on worksheets 23 & 24 the expected times (results).
Look at source file Column O
Match dest file C1 ; D1 until U1
Return on cells C2:C21 the first 20 trans.

Thanks in advance.
R

 

[p45cal]

This might be an uphill struggle; the thread you got that code from was https://chandoo.org/forum/threads/vba-extract-data-from-csv.33395/#post-198484 . The txt/csv files at that thread are comma delimited, the equivalent files here are tab delimited. Are you sure you've got the right file structure here?

 

[Rodrigues]

Hi
The originals source files are .csv, converted to .txt otherwise can't upload them.
Left the original code, as I though it will be easier to tweak it (did try to play with it without success).
On dest file cells C1:U19 are teams
Column B refers to the 20 trans.
Have uploaded again the dest file with piece of code missing on the first one (thisworkbook & sheet 23/24) I sent (it's the original code to read the first transaction only). If you have another way to structure the file, I'm open to ideas.
Thanks in advance.
R

 

[p45cal]

Hi
The originals source files are .csv, converted to .txt otherwise can't upload them.

The files are definitely different (content-wise). It shouldn't be a case of converting them , only renaming them - perhaps as Marc L suggested here: https://chandoo.org/forum/threads/vba-extract-data-from-csv.33395/#post-198376
Otherwise code I tweak simply won't work.

 

[Rodrigues]

Please see files attached, hopefully correct.
Thanks for your help.
Regards
R

 

[p45cal]

In the attached, there is a user-defined function called GetFirst20Rows, used like this:

In the PathAndFileName argument it expects to see a full path and file name to the data file. You can make this up any way you want. In the file I've referred to a cell containing just the path (without its final backslash) and added to it a string starting with a backslash and the start of your file names, followed by a reference to cell B1 which contains a full date (of which the format is irrelevant) which is formatted in the formula to the appropriate format using TEXT, followed by ".csv". The TeamNo argument points to a cell containing the team number in row1. This means you can change the date and/or directory in cells A1 and B1 and observe the values updating.
The formula itself should be array-entered using Ctrl+Shift+Enter into a range of cells 1 column wide and 20 rows deep. It can then be copied left/right as required. There's Macro1 which does this for whichever is the active sheet.

In sheet23, cells C2:C21, shaded green, is where you could aray-enter the formula.

There are a few other macros in Module2, demonstrating how you can use the udf.

I don't think that this is necessarily the best way to do this (using sql on a ADODB object) and if I get time I'll compare this with another method which will get the whole 20x20 array filled with a single formula in one visit to the file (currently it takes one visit per Team no. to get the 20 results), but I'll use a completely different method.

 

[Rodrigues]

Thank you so much to take the time to sort this for me.
I'm getting error run time error 1004 unable to set the formulaarray property of the range class and highlight [.Rows(1).FormulaArray = "=GetTimes(RC[-1],R1C3:R1C21,""k:\AutoReports\Production Report Detailed "")"]
Could you please advise?
Thanks again
Regards

 

[p45cal]

Shouldn't you be using GetFirst20Rows rather than GetTimes?!

 

[Rodrigues]

I'm sorry, I must be missing something.
Have disabled GetTimes code under This workbook, close & open the file the error has gone and data comes up (zeros).
Set cell A1 to point to my K drive and just for curiosity have amended the first two times on 23-APR-17 file and cell J2 reflects the change when type in the path on cell A1, on Sheet24 have typed in on A1 the path and shows #value!
Also disabled all the other codes (Sub test;macros4,subtest), so just left Macro1 active, and have the same results.

 

[p45cal]

Set cell A1 to point to my K drive and just for curiosity have amended the first two times on 23-APR-17 file and cell J2 reflects the change when type in the path on cell A1

That's a very good sign that things are working!

on Sheet24 have typed in on A1 the path and shows #value!

Well, I've tested again here and all seems well; I've tried to get the formula to return #VALUE! and can only do so if the csv file is wrong - is that particular file (24th) not comma delimited perchance?

Could I suggest the briefest of TeamViewer sessions?

 

[Rodrigues]

p45cal
I have been scratching my head for the last few days with this and it was a silly mistake of mine, have deleted the very first row on the csv file.
If you don't mind, could you please have a look and help me one more time;
been checking the trans and found that sometimes is not returning the very first time trans, the reason (I believe) is that, the trans are in the middle of the others of the same team, as an example on "Production Report Detailed 2017-05-10" are highlighted at yellow the 20 trans returned and below (same columns) are the correct ones.
Column F:
returns rows 370-389
should be rows 740-759

Column M:
Returns rows 2209-2228
should be rows 2549-2568

Column O:
returns rows 2813-2832
should be rows 2815-2834
Once again apologies for my mistake and the trouble caused.
Regards
R

 

[p45cal]

Yes, you asked for the first 20 records and you got the first 20 records in the file, you really wanted the first 20 transactions in time, wherever they were in the file.

Change the line in the GetFirst20Rows function:

strsql = "SELECT a.F5 FROM `" & fname & "` a WHERE (a.F15='" & TeamNo & "')"

to:

strsql = "SELECT a.F5 FROM `" & fname & "` a WHERE (a.F15='" & TeamNo & "') ORDER BY a.F5"

Try not to fiddle with the csv files; take them as they come, from wherever they come from.

 

[Rodrigues]

Can't thank you enough.
Regards