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 called 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]

Benjamins.09.05.19.csvChilis.09.05.19.csv

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?