Consulting

Page 4 of 4 FirstFirst ... 2 3 4
Results 61 to 66 of 66

Thread: Importing multiple txt files, but including extra data from file name

  1. #61
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    I think I'll be fine this way, thank you again. I do have one question though. The code you provided before had me importing all the Fields on the report. If the report I want to use has 51 fields, which will remain constant, but I only want to import select ones... how could I modify the code? For example, the daily report I am pulling from has 51 fields, but I only need 19 of those fields. I could just import everything, but I have read how efficiency in these DB are key, so I figured it would be smart to limit to what I absolutely need.

    Thank you!

  2. #62
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    DISREGARD LAST QUESTION PLEASE!

    Actually, I asked that question incorrectly. Each report has 2 fields, but has 51 rows of Data. If I only want specific rows of data, do I manipulate that with the "If recount>0" statement? Or is there a more efficient way to choose which lines of data to import.

  3. #63
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    Here are the two reports that have all the info I need, and are constant. Department totals, I just take everything. Gross Sales, I only need select data....

    Gross Sales (row 2)
    Sales Tax 1 charged (5)
    Sales Tax 2 Charged (8)
    Bottle Deposit (23)
    Bottle Returns (24)
    Coupons (25)
    Gift Card Credit (26)
    Gift Card Cashout (27)
    Gift Card Tendered (28)
    Loyalty Credit (29)
    Loyalty Redeemed (30)
    Online Paidout (31)
    Instant Paidout (32)
    ACH Wholesale (33)
    Checks Tendered (34)
    Charge Tendered (35)
    Credit Card Charges (36)
    Debit Card Charges (37)
    ROA Charge (40)

    Obviously I wouldn't ask you to do this whole thing... but if you could suggest how to start, I can follow after. Thank you.
    Last edited by damdmak; 02-24-2020 at 09:12 AM. Reason: adding files

  4. #64
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    It depends on where the records are that you want, if they not at the beginning of the data you can set the recount> to where you want them to start.
    ie
    if recount>1 then
    will start on line 2.
    However if you wish to ignore later lines you will need an "If/then" for each line or batch of lines.
    ie
    if recount <> 3 and recount<> 4 and recount<>9 and recount<> 11 then
    would skip those lines of code, you would then have to add an end if before the other end if for the recount>1.

    However having complicated If then statements might actually sow down the import.
    It might be better to leave it as is and just ignore or delete the data not required.

  5. #65
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    Gotcha... that makes sense. One more thought... if you look at the text files I sent, the data in the first column will be constant and actually probably should be fields in a table. Would it be better to import with the first column as the field and the second as the data for the table? That would eliminate half the data in the table each day. Also, if that was a better idea, how would I manipulate the code to import that instead?

  6. #66
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    That was basically how I think your table was setup, you then have to use if then statements to match the first column with the fieldname you want to put the value in.
    ie
    if varsplit(0) = "Beer then
    ![Beer] = varsplit(1)
    end if
    Last edited by OBP; 02-25-2020 at 12:45 PM.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •