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

[damdmak]

Hello, and thank you for any help. I am learning (slowly) how to create an Access DB for my small business to attempt to be a bit more analytical than my register system allows. I would like to analyze data for my customers, but the only way I can export data from my system is into individual files and have the customer name and date of purchase in the file name. The file would then be be consistent in the data forms, but I can not add the customer and date to the data.

For example, the file for Customer 1's purchases on 8/29/19 would be named 'customer1.08.29.19.txt' and the file is a CSV of Item, UPC, Amount, Description. I kept the date in the file name in a MM.DD.YY. format to try keep it standard.

What I would like to automate is...

Import all the files in the chosen folder
Add the customer and the date as columns along with the 4 in the file
Append to table in my database
Move extracted files to a new folder so I can repeat gathering data

Any help, or ideas would be appreciated. I wish I could just get the data out of our register system, but they say that this CSV export is the only way to gather the data I am trying to analyze.

[OBP]

This code will import all the CSV files from a Folder.


Dim FileNm, FilePathName, Path, FileNameList() As String
Dim FileCount As Integer
DoCmd.SetWarnings False
Path = Me.FilePath
FileNm = Dir(Path & "")
Search_path = Path ' where ?
Search_Filter = "*.CSV" ' what ?
Docname = Dir(Search_path & "" & Search_Filter)
Do Until Docname = "" ' build the collection
MsgBox Docname
FilePathName = Path & Docname
DoCmd.TransferText transferType:=acImportDelim, SpecificationName:="pp OUTINGS", TableName:="pp OUTINGS", FileName:=FilePathName, hasfieldnames:=True

Docname = Dir
Loop
DoCmd.SetWarnings True
MsgBox "Updates Complete"

Exit Sub
errorcatch:
MsgBox Err.Description


Note that this VBA is run from a form which has a field caled Filepath which is used to identify which folder you want to use, you could replace the me.Filepath with the actual path to your folder.

Also the DoCmd.TransferText has an Import specification called "pp OUTINGS" which you will not need unless you have an import spec.
The data is then placed in a table called "pp OUTINGS"

Try this code by modifying it to suit your folder and table and then repost if you do so.

[damdmak]

I really appreciate the help. I may just be in over my head, I thought I changed what I was supposed to. The file names in the folder will be various customers and a date. Am I supposed to enter something into the blank quotations? Here is what I did.


Sub ImportDailyBar()
Dim FileNm, FilePathName, Path, FileNameList() As String
Dim FileCount As Integer
DoCmd.SetWarnings False
Path = "C:\Users\dmaks\Desktop\Liquor Files\Bar Daily Sales"
FileNm = Dir(Path & "")
Search_path = Path ' where ?
Search_Filter = "*.txt" ' what ?
Docname = Dir(Search_path & "" & Search_Filter)
Do Until Docname = "" ' build the collection
MsgBox Docname
FilePathName = Path & Docname
DoCmd.TransferText transferType:=acImportDelim, TableName:="tblBarDailyData", FileName:=FilePathName, hasfieldnames:=True
Docname = Dir
Loop
DoCmd.SetWarnings True
MsgBox "Updates Complete"
Exit Sub
errorcatch:
MsgBox Err.Description
End Sub


I really appreciate it.

[OBP]

Well I can't see anything obviously wrong, except possibly you have included the file name in the Path or the Path needs a \ on the end of the path.
So what happens?
Do you get an error message?
Do you get a msgbox providing the Document name (Docname)?

Can you provide a dummy customer file and a blank copy of your database?

[damdmak]

Originally, it would just say Updates Complete, but there would be no data.

I did as you said and added a \ to the end of the file path, and when I ran again, the messagebox showed the first file in the folder.

Then it said " Run Time Error '3011' --- it lists the file name, but it is exact as far as I can tell.

I don't know how to do a blank copy? I'm sorry is there a quick way to do that?

[damdmak]

As I am playing with it, I couldn't import it using the import wizard "As is" because it was using Long Integer for my UPC column. When I switched to Double, I could import no problem. I'm saving the spec and retrying.

[OBP]

OK, let me know how it goes.

[damdmak]

No, it still has the same problem. Seems like after it finds the file name, then it says the file name doesn't exist?

[OBP]

OK, I have had another look at your code, you have removed the Specification name from the docmd function, but you also removed the comma as well, which is a place holder for the specn. so the code is probably reading the file name as the specn.
So try


DoCmd.TransferText transferType:=acImportDelim, , TableName:="tblBarDailyData", FileName:=FilePathName, hasfieldnames:=True

If that doesn't work take a copy of a file and remove any sensitive data and then upload it as a zipped file on to the forum using the Go Advanced and Manage Attachment buttons and I will see if I can open it.

[damdmak]

2500525006

These are two of the files I am trying to import. I tried to upload the original files (saved as a .txt) but it wouldn't let me. It said invalid file. I tried re-running the code with ".csv" and the same error came up. I'm wondering if the files I create with our P.O.S. system won't allow import.

[damdmak]

What my end goal truly is (assuming I can import these files to Access) is to add data to the files based on the file name. So in these examples, I want the customer number, in the customer table, added to each line of data as well as the date. So each line of data is an item, UPC, Units, and Sale. Im wondering if it is possible to import multiple files containing that information, and use the file name (Benjamins.09.05.19) to add a 5th and 6th column (Benjamins and 9/5/19) in this case. Or if there is a better way to do this. I am stuck with the data files in this form, and I am trying to find the fastest way I can bring in daily data for any amount of 100 clients.

[OBP]

No it is the Forum, it only allows zip files normally along with Excel.

[OBP]

The final part of your plan should not be a problem once we can get the data in to the database.
If necessary I can use a different method for the import, if you look at the first post title on this forum you will see that I have laready done so on 2 occasions.
I will take a look at the files and it will probably be tomorrow when I get back to you as it is already nearly 8:00pm over here.

[damdmak]

You are such a huge help! thanks!

[OBP]

A Question, what do you wan to do about the last line of the file, as the data does not fit with the Table headings?
Do you need that Summmary?

ps even though a manual Import works the Docmd.TransferText refuses to accept the file name with the fullstops in, so I am using a different technique which is working fine.

[damdmak]

I would love to have the last line removed. Also, I can name the files anything. So if it is easier to get rid of the periods, consider it done.

[OBP]

I will continue with the method that I am using now as it makes it easier to remove the last line.
I may have it ready by the time you read this, although I am going to reconfigure how the data will be stored.

[OBP]

OK, here is the database that does what I think you want, it uses a table and form to identify the Folder where your data files are located. Once you have updated the location in the form you can click on the import Button called Import New File Data.
There are 2 tables, one called Customers to hold the CustomerID, the file name, the Customer and the file date.
The other table holds the sales data from the files in the folder.

The VBA finds a file in the Target Folder and re-arranges the date into a format that is acceptable to an Access date Field, it then stores that date in a single record in the Customer table.
it then looks up the CustomerID that has been allocated to that record.
The code then opens the target file for reading data (the old fashioned method) and reads in one line at a time, it ignores the first line and the last line. It saves the data to the Test table adding the CustomerId that it looked up in the last Field. This is the correct way to store data in a relational database, ie whereever possible only store the data once and then refer to it via it's Key ID field.
I have added a query that opens after the import that combines the 2 tables for data output.

[damdmak]

This is amazing!!!! The only thing is, when I run it... it says "Run Time Error '3421' Data Type conversion error. I hit debug and this line is highlighted.


![Sales] = varsplit(3)

I can't tell you how much I appreciate the help.

[OBP]

Is that with your table or my table?
As I don't get an error when I run it.
Are you using the same files a you provided, if not I may need to see what the txt files hold for Sales?

[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/office/vba/language/reference/user-interface-help/movefile-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.

[damdmak]

I think this is what you asked for.

[OBP]

Is the date the date you import thr data or from the file name?

[damdmak]

From the file name. Each file is "NetCashMM.DD.YY.txt"

[OBP]

Ok, this may take a while.

[OBP]

Can you supply an actual original file for me to work with?

[damdmak]

Yes, but it will not let me upload the txt file. I'll try again.

[damdmak]

I'll try compressing.

[OBP]

Thanks, I take it that you do not need the original VBA code import routine for customers etc that I posted previously?

[damdmak]

Thank you so much.

[damdmak]

no Way! I use that 10 times a day thanks to you.

[OBP]

OK, do you need data from the first line of the file which tells you the sales period?

But do you need the old code in with this code?

[damdmak]

I dont need the data from the first line, that will be read from the file name. All the reports are 1 day reports. I compiled one ( i believe 2/20/20) for you so you had more categories showing the different possibilities that can show up.

I don't need the old code in with this one. If it imports the file with variable fields included, I believe I can manage from that point. The importing is what I can't figure out.

[OBP]

Yes, this one is not straightforward, the file format is not normal, ie it is not CSV or using a Space or Tab as a seperator.
So I need to work with the Table field names, the only problem is if they change them in the file output it may cause the code to crash.
Can you control the output or is that proprietary?

[damdmak]

I control the output. So those field names will remain constant.

[OBP]

Can you control the type of output?
As it would be easier if it was an actual csv or Tab delimited.

[damdmak]

No unfortunately that is what I am left with. The only options it gives me is to export to HTML or CSV/TXT

[OBP]

That is OK, I have imported a couple of lines and it has seperated out the title and amount for me.
I just have to figure out waht it is using to do so.

[damdmak]

I maybe able to use your original code, and instead of one report use a few and get all the information. I don't want to take up your time if the way I am trying now is a huge pain. Let me see if I can get two or three reports and get all the data I need. The reports are in CSV form. Can I get back to you please?

[damdmak]

I can do it with three reports using your original code! Thank you so much. It will be much better than trying to conform the annoying report. I seriously appreciate everything you do. Thank you .

[OBP]

It is not a problem, but for your information the file uses Horizontal Tab as the delimiter.
So if you want me to do this I can it is just a case of matching the Field names.
I could do a couple and you could do the rest.

[damdmak]

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!

[damdmak]

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.

[damdmak]

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.

[OBP]

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.

[damdmak]

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?

[OBP]

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