Results 1 to 15 of 15
  1. #1
    sumdumgai is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    8

    Import text file question

    I've got a pipe delimited text file that I need to import into an Access table using VBA. When I issue a read statement against the text file, the entire text file, not just a line at a time, gets read into memory. As a result, I read one record and the input file is at EOF. Is there a way to read a line at a time? I know I'm missing something here. Thanks.

  2. #2
    SOS is offline Novice
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Posts
    11
    The text file likely doesn't have line delimiters and so it thinks it is one big line.

    Have you tried using the DoCmd.TransferText method?

  3. #3
    sumdumgai is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    8
    There are quire a few text files that need to be imported. Some will be empty files (no data). I've tried TransferText but get an error when the imput file is empty so can't save the spec. When I open the text file with notepad, it looks like there is a line delimiter. If I attached the text file to my post, would you be able to determine if line delimiter exists? Thanks.

  4. #4
    SOS is offline Novice
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Posts
    11
    Can you post one of the text files?

  5. #5
    sumdumgai is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    8
    I think I've attached it. Thanks.

  6. #6
    SOS is offline Novice
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Posts
    11
    That file I just opened and it looks to me that there is one single line in it (no line break).

  7. #7
    SOS is offline Novice
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Posts
    11
    However, when I did a manual import it gave me 16 records.

  8. #8
    sumdumgai is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    8
    There are in fact multiple records so I'm going to have to find a way to split it.

  9. #9
    SOS is offline Novice
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Posts
    11
    What code have you been using?

  10. #10
    sumdumgai is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    8
    I've been searching everywhere for some import text file examples and to tell the truth, my code's now a mess. But no matter which examples I used, I always pull in the entire file and not a line at a time. I found some code that looks like it addresses this situation:

    Function SplitFileIntoLines(PathFileName As String) As String()
    Dim X As Long
    Dim FileNum As Long
    Dim TotalFile As String
    Dim Lines() As String
    ' Better to let VB select the file channel number than to hard code it.
    FileNum = FreeFile
    ' Load entire file into the TotalFile variable in "one fell swoop".
    Open PathFileName For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
    Close #FileNum
    ' Replace CarriageReturns with Line Feeds... this will help
    ' handle normal Windows files AND files generated by a Mac.
    TotalFile = Replace(TotalFile, vbCr, vbLf)
    ' Now, if it was a normar Windows file, there will be two
    ' Line Feeds following each other, so we reduce them to just one.
    TotalFile = Replace(TotalFile, vbLf & vbLf, vbLf)
    ' Now that the entire file is now contained in the TotalFile
    ' variable as a Line Feed delimited file, let's return those
    ' individual lines in an array for use by the calling code.
    SplitFileIntoLines = Split(TotalFile, vbLf)
    End Function

    'To see how to use it, consider this sample macros...

    Sub Test()
    Dim X As Long
    Dim FileLines() As String
    FileLines = SplitFileIntoLines("c:\temp\Book1.txt")
    For X = 0 To UBound(FileLines)
    Debug.Print FileLines(X)
    Next
    End Sub

    Having a little problem managing opening and closing files.

  11. #11
    SOS is offline Novice
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Posts
    11
    First of all, you are opening it wrong

    Open PathFileName For Binary As #FileNum

    should be

    Open PathFileName For Input As #FileNum


    Second, once you do that you don't need to split it to lines as it will do so and you would use
    Code:
    Dim strHold As String
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim varSplit As Variant
    Dim intCount As Integer
     
    Set db = CurrentDb
    Set rst = db.OpenRecordset("yourTableNameToSendDataTo")
    With rst
     
    Do Until EOF(FileNum)
       Input FileNum, strHold
       varSplit = Split(strHold, "|")
           .AddNew
              For intCount = 0 to UBound(varSplit)
                     .Fields(intCount) = varSplit(0)     
             Next    
                .Update
    Loop
     
    rst.Close
    Set rst = Nothing

  12. #12
    sumdumgai is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    8
    Made some changes to get clean compile but still only get one record:

    Dim strHold As String
    Dim db As DAO.Database
    Dim rst As DAO.Recordset
    Dim varSplit As Variant
    Dim intCount As Integer

    fileName = "ar_mast.txt"
    filePath = "M:\Reports\Sales Data\Database\CORD SAMPLE DATA\"
    fileSpec = filePath & fileName

    FileNo = FreeFile ' Get next available file number.
    MsgBox FileNo
    'Close #FileNo

    Open fileSpec For Input As #FileNo

    Set db = CurrentDb
    Set rst = db.OpenRecordset("ar_mast")
    With rst
    Do Until EOF(FileNo)
    Line Input #FileNo, strHold
    varSplit = Split(strHold, "|")
    .AddNew
    For intCount = 0 To .Fields.Count - 1
    .Fields(intCount) = varSplit(intCount)
    Next
    .Update
    Loop
    End With
    rst.Close
    Set rst = Nothing

  13. #13
    SOS is offline Novice
    Windows 7 Access 2007
    Join Date
    Mar 2010
    Posts
    11
    I don't know what to say. If I import it manually I get 17 records. If I do it like this in code, the line input only says it is one line; just like you get.

  14. #14
    sumdumgai is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    8
    Thanks for your help anyway. If I figure it out, I'll post it.

  15. #15
    sumdumgai is offline Novice
    Windows Vista Access 2007
    Join Date
    Mar 2010
    Posts
    8
    This seems to work:

    Sub ImportText (fileSpec)
    FileLines = SplitFileIntoLines(fileSpec)

    With rst
    For X = 0 To UBound(FileLines) - 1
    If FileLines(X) = "" Then GoTo endsub
    .AddNew
    varSplit = Split(FileLines(X), "|")
    For intCount = 0 To .Fields.Count - 1
    .Fields(intCount) = varSplit(intCount)
    Next
    .Update
    Next
    endsub:
    .Close
    End With
    End Sub

    Function SplitFileIntoLines(PathFileName As String) As String()
    Dim X As Long
    Dim FileNum As Long
    Dim TotalFile As String
    Dim Lines() As String
    On Error Resume Next
    ' Better to let VB select the file channel number than to hard code it.
    FileNum = FreeFile
    ' Load entire file into the TotalFile variable in "one fell swoop".
    Open PathFileName For Binary As #FileNum
    TotalFile = Space(LOF(FileNum))
    Get #FileNum, , TotalFile
    Close #FileNum
    ' Replace CarriageReturns with Line Feeds... this will help
    ' handle normal Windows files AND files generated by a Mac.
    TotalFile = Replace(TotalFile, vbCr, vbLf)
    ' Now, if it was a normar Windows file, there will be two
    ' Line Feeds following each other, so we reduce them to just one.
    TotalFile = Replace(TotalFile, vbLf & vbLf, vbLf)
    ' Now that the entire file is now contained in the TotalFile
    ' variable as a Line Feed delimited file, let's return those
    ' individual lines in an array for use by the calling code.
    SplitFileIntoLines = Split(TotalFile, vbLf)
    End Function

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

Similar Threads

  1. XML File Import To Access...Overwrite Issue
    By wipers123 in forum Import/Export Data
    Replies: 0
    Last Post: 02-11-2010, 12:27 PM
  2. Import XML file
    By randolphoralph in forum Programming
    Replies: 1
    Last Post: 01-22-2010, 09:12 PM
  3. Import prn file
    By Accessshelp@work in forum Import/Export Data
    Replies: 1
    Last Post: 10-29-2009, 09:16 AM
  4. Import a .sql file?
    By Alan in forum Import/Export Data
    Replies: 1
    Last Post: 06-03-2009, 07:52 PM
  5. CSV File Import
    By compasst in forum Import/Export Data
    Replies: 3
    Last Post: 03-31-2006, 09:37 AM

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