Results 1 to 8 of 8
  1. #1
    ctolbert is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2012
    Location
    Bakersfield, CA
    Posts
    4

    Question Import Tab Delimited Text Files via VBA File Picker and add to Table

    Hello all-



    I'm a novice w/ VBA, but I know it's the solution to my problem. I have a bunch of files that are tab delimited, however they have no file extension, so I cannot create a import spec with them into Access. What I need to do is read through each line of the file, delete some rows, and add data to a table from the valid rows.

    My files look like this:

    Accepted Files[]
    Approved[]
    Acct#[] Name[] Desk[] Field1[] Field2[]
    132131[] John Doe[] R1[] GG[] BK003[]

    (I used "[]" to stand for a tab marker since I'm not sure how to make them.)

    To futher complicate this, after this grouping of data, there's 2 blank rows, then a similar set of data for a declined accounts!

    Declined Files[]
    Acct#[]Name[]Desk[]Field1[]Field2[]

    I don't necessarily need everything (accepted and declined) in seperate tables, as I could create a query that could seperate the two- however, I would like to have the user open a File Picker from a form to select the file (file type has to be "*" as these files have no extension), then the VBA dump it all into a table. I tried to write as much as I could figure out:

    Code:
    Public Function getFileName(tfile) As String
    Dim fDialog As Object
    Set fDialog = Application.FileDialog(msoFileDialogOpen)
    Dim tfile As Variant
    Dim fileDate As Date
    With fDialog
        .AllowMultiSelect = False
        .Title = "Please select one file"
        .Filters.Clear
        .Filters.Add "All Files", "*.*"
    If .Show = True Then
        For Each tfile In .SelectedItems
            getFileName = tfile
        Next
    End If
    End With
       
    MsgBox (getFileName)
    getFileName = CStr(getFileName)
    End Function

    That allows me to open the file picker, but not sure what to do next. Any help would be greatly appreciated!

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Why don't these files have a file extension? Why don't you rename them with a file extension? If they are just text files then use .txt extension.

    Goolge: Access VBA read write text file
    http://www.databasedev.co.uk/text_files.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ctolbert is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2012
    Location
    Bakersfield, CA
    Posts
    4
    Lol, well I guess I can, but I get about 30 of these files throughout the day. I was hoping for something programmable as it would save me some time from having the do the opening, renaming, and importing for each file! As to "why" they don't have any extension, that's just the way the client system works unfortunately. Any other suggestions?

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    You can programmatically rename the files.

    Google: VBA rename files in folder

    http://forums.techguy.org/business-a...es-folder.html

    Here is another link about reading a text file and saving data to table:

    http://forums.aspfree.com/microsoft-...vba-30159.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    ctolbert is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2012
    Location
    Bakersfield, CA
    Posts
    4
    @June7: Thanks for the advice, however this still isn't really what I'm needing as 1: the files aren't being renamed w/ an extension like .txt so that Access can see them and 2: even if that were to work, I'd have to go back and manaually delete a bunch of blank records (because they are included in the file layout) from the table. Any better ideas?

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    All can be done programmatically, just have to decide which way to go.

    Option A
    1. rename file with extension
    2. import file
    3. delete 'blank' records

    Option B
    1. open text file
    2. read each line
    3. if line not blank, parse data and save to table
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    ctolbert is offline Novice
    Windows XP Access 2007
    Join Date
    Nov 2012
    Location
    Bakersfield, CA
    Posts
    4
    Option B would be the preferred method.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Based on example of your data structure that could be the best approach, although the code might be more complex.

    Adapt code from referenced links. When you encounter specific issue, come back with question.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 19
    Last Post: 10-08-2012, 07:47 AM
  2. Replies: 3
    Last Post: 07-30-2012, 02:16 PM
  3. Having troble exporting a text delimited file
    By itm in forum Import/Export Data
    Replies: 2
    Last Post: 07-18-2011, 06:42 AM
  4. Replies: 5
    Last Post: 02-20-2011, 08:22 PM
  5. Importing a tab delimited file into access table - please he
    By championss in forum Import/Export Data
    Replies: 0
    Last Post: 10-29-2006, 02:33 AM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums