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

[damdmak]

NOPE!!!! nevermind, it was the file! fixing and re-trying.

[damdmak]

THIS IS AMAZING!!!!! I CAN'T THANK YOU ENOUGH!!

[OBP]

Good, let me know if you need any help analyzing all that lovely data.

[damdmak]

I will take you up on that I'm sure! Thanks again.

[damdmak]

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.

[OBP]

Too late for a reply tonight, I will reply tomorrow.

[damdmak]

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?

[OBP]

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

[damdmak]

Oh great! thank you.

[OBP]

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?

[OBP]

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?

[damdmak]

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.

[OBP]

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?

[damdmak]

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.

[damdmak]

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.

[OBP]

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.

[OBP]

Do these files come in .txt or .csv format?

[damdmak]

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

[damdmak]

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.

[OBP]

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.