Consulting

Page 1 of 4 1 2 3 ... LastLast
Results 1 to 20 of 66

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

  1. #1
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location

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

    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.

  2. #2
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.
    Last edited by OBP; 09-03-2019 at 03:28 AM.

  3. #3
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    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.
    Last edited by Aussiebear; 04-11-2023 at 06:03 AM. Reason: Added code tags to supplied code

  4. #4
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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?

  5. #5
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    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?

  6. #6
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    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.

  7. #7
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    OK, let me know how it goes.

  8. #8
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    No, it still has the same problem. Seems like after it finds the file name, then it says the file name doesn't exist?

  9. #9
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.
    Last edited by Aussiebear; 04-11-2023 at 06:04 AM. Reason: Added code tags to supplied code

  10. #10
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    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.

  11. #11
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    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.

  12. #12
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    No it is the Forum, it only allows zip files normally along with Excel.

  13. #13
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  14. #14
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    You are such a huge help! thanks!

  15. #15
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  16. #16
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    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.

  17. #17
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.

  18. #18
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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.
    Attached Files Attached Files

  19. #19
    VBAX Regular
    Joined
    Sep 2019
    Posts
    36
    Location
    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.
    Last edited by Aussiebear; 04-11-2023 at 06:05 AM. Reason: Added code tags

  20. #20
    VBAX Guru
    Joined
    Mar 2005
    Posts
    3,296
    Location
    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?

Posting Permissions

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