1. Welcome to Chandoo.org Forums. Short message for you

    Hi Guest,

    Thanks for joining Chandoo.org forums. We are here to make you awesome in Excel. Before you post your first question, please read this short introduction guide. When posting or responding to questions please remember our values at Chandoo.org are: Humility, Passion, Fun, Awesomeness, Simplicity, Sharing Remember that we have people here for whom English is not there first language and we need to allow for this in our dealings.

    Yours,
    Chandoo
  2. Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  3. When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Extract the first 20 transactions from csv

Discussion in 'VBA Macros' started by Rodrigues, Apr 24, 2017.

  1. Rodrigues

    Rodrigues Member

    Messages:
    152
    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

    Attached Files:

  2. p45cal

    p45cal Well-Known Member

    Messages:
    716
  3. Rodrigues

    Rodrigues Member

    Messages:
    152
    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

    Attached Files:

  4. p45cal

    p45cal Well-Known Member

    Messages:
    716
  5. Rodrigues

    Rodrigues Member

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

    Attached Files:

  6. p45cal

    p45cal Well-Known Member

    Messages:
    716
    In the attached, there is a user-defined function called GetFirst20Rows, used like this:
    upload_2017-5-4_20-46-17.png
    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.

    Attached Files:

    Rodrigues likes this.
  7. Rodrigues

    Rodrigues Member

    Messages:
    152
    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
  8. p45cal

    p45cal Well-Known Member

    Messages:
    716
    Shouldn't you be using GetFirst20Rows rather than GetTimes?!
  9. Rodrigues

    Rodrigues Member

    Messages:
    152
    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.

    Attached Files:

  10. p45cal

    p45cal Well-Known Member

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

    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?
  11. Rodrigues

    Rodrigues Member

    Messages:
    152
    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

    Attached Files:

  12. p45cal

    p45cal Well-Known Member

    Messages:
    716
    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:
    Code (vb):
    strsql = "SELECT a.F5 FROM `" & fname & "` a WHERE (a.F15='" & TeamNo & "')"
    to:
    Code (vb):
    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 likes this.
  13. Rodrigues

    Rodrigues Member

    Messages:
    152
    Can't thank you enough.
    Regards

Share This Page