Consulting

Page 2 of 4 FirstFirst 1 2 3 4 LastLast
Results 21 to 40 of 66

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

  1. #21
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    NOPE!!!! nevermind, it was the file! fixing and re-trying.

  2. #22
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    THIS IS AMAZING!!!!! I CAN'T THANK YOU ENOUGH!!

  3. #23
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Good, let me know if you need any help analyzing all that lovely data.

  4. #24
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    I will take you up on that I'm sure! Thanks again.

  5. #25
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    OBP, I can't thank you enough for all your help last fall. I have learned so much from you and I appreciate it! I have a two follow up questions if you have some time.

    How would I change this code to allow for a variable amount of information imported. For example, if instead where you have the "varsplit" as set columns... would it be possible to say I want to check for 10 different possible varsplits and simply put a 0 if it is not in the txt file?

    Also, how do I get the program to move the file to a "Done" folder when it is finished importing. When I have a large amount of files it goes through, if the data is funky, I have to try and search through and find where it stopped, fix the data, then remove all the finished files so it won't duplicate.

    Thanks for any help.

  6. #26
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Too late for a reply tonight, I will reply tomorrow.

  7. #27
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    OBP, just wondering if you ever got a chance to check out that code you sent me... I appreciate any help. If my explanation doesn't make sense, I could put together an example for you if that is better?

  8. #28
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Sorry, I forgot, I was working on other problems.
    I am sure that it is possible to match the number of elements in the Array to the number of fields, I will need to check if I have that code.
    Moving the files to another folder can be achieved using the MoveFile method here
    https://docs.microsoft.com/en-us/off...ovefile-method

  9. #29
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    Oh great! thank you.

  10. #30
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    How variable do you think the number of fields is likely to be, the other problem is what sort of table will it go in, as the table will need to accommodate the values whenthere are more of them?

  11. #31
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    I have checked the code again and the Addnew routine needs to be able to match the varsplit to the storage table Field names.
    So I am not sure you can get the flexibility you are looking for.
    Can you provide more details on what you actually need?

  12. #32
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    So in the last code you sent me, it is set up for 5 specific Field Names. The data that I would like to now use a macro to add has a maximum amount of field names that are set, but each file will not contain all of the field names. So if the fields were A through E... the first file could have A - 240, C - 300. The second could have B - 300, C - 210, D - 102, E- 100.

    Is it possible to import fields as a kind of "If" statement? Look for Field A, but if no field A, set to 0? That would work if it is possible.

    I have sent a few examples of the txt files that I have to deal with. The file NetCash01.31.20.csv is a compilation of a few to get more field names to show that there could be more, but they are always the same field name.
    Attached Files Attached Files

  13. #33
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    So, how many of that data do you need to import?
    Department Sales & Additions & Subtractions?
    Is there just the one record in each file?
    What fields do does your table have?

  14. #34
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    The fields in my table are
    Beer
    Beer/Keg
    BPO
    Cig
    Cigar
    Clothing
    Coupon
    GiftCertificate
    KegDeposit
    KegTapDeposit
    Liquor
    Lotto
    MB
    MiscNonTax
    MiscTax
    Novelties
    QuickAdd
    Soda/Mixer
    Tobacco
    TownsquareTickets
    Wine
    Zippo
    GrossSales
    OpeningCash
    SalesTax1
    SalesTax2
    BottleDeposit
    ROAChargeAccounts
    ROAGiftCards
    TotalAdditions
    BottleDepositTendered
    CouponsTendered
    CreditCardTendered
    DebitCard
    CashPaidOut
    ChargeTendered
    GiftTendered
    OnlinePaidout
    InstantPaidout
    ACHWholesale
    TotalSubtractions

    But the only reason I have the Gross Sales, Total Additions, and Total Subtractions is because I manually enter every day, so those are double checks for typos.

  15. #35
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    I have the report on a daily basis and have to print and retype into my DB. On any given day, the report will have any number of those fields, but it will never have more than those fields or different fields. Those are all the possibilities.

    There will be one record in each file every day.

  16. #36
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, can you get me a copy of your database table less all the data?
    I will look at matching the titles in the csv files to the fields in the table.

  17. #37
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    Do these files come in .txt or .csv format?

  18. #38
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    I'll get you a copy of the table. I will actually be building a new one based around being able to import if that makes any difference.

    I usually save them in a txt form, but I was having trouble uploading those files and I didn't want to take up more of your time than I already had, so i sent in a CSV

  19. #39
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    Here is an excel file of the table? I am not sure how to export it if you want it otherwise. Everything is currency with the exception of the ID which is AutoNumber and the Date.
    Attached Files Attached Files

  20. #40
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    It would be best to use the new version when you have it.
    You create a new database, import the table and delete the data and then post the zipped database.

Posting Permissions

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