Results 1 to 8 of 8
  1. #1
    New Lounger
    Join Date
    Dec 2006
    Location
    Michigan, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Import from inside a text file (2k3)

    Is it possible to do an import of data from inside a delimited text file without importing the entire text file? There are a couple of things that need to be overcome before the data that I want can be imported. I'll list the items of concern, and hopefully someone knows how to get around these:

    1. The column headers are on the 4th row, there is junk header info in rows 1-3.
    2. The data starts with a column that only contains the date in "A1". The rest of the column is blank, and I don't care about the date.
    3. There is a blank row between the column headers and the actual data.

    Thanks in advance!

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import from inside a text file (2k3)

    Welcome to Woody's Lounge!

    It might be easiest to pre-process the text file in Excel, since Excel lets you specify in which row you want to start importing. You can also specify that you want to skip the first column.
    You can then save the file as a text file again, or as an Excel workbook, and import this into Access.
    This won't work if your text file has more than 65536 lines (the maximum number of rows in an Excel 2003 worksheet).

    It is possible to use VBA code to import the text file line by line, but this will be a lot slower.

  3. #3
    New Lounger
    Join Date
    Dec 2006
    Location
    Michigan, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import from inside a text file (2k3)

    Thanks for the reply so soon HansV.

    I expected to hear "use excel" as the first advice, but that's exactly what I am trying to avoid. The file does load into Excel, but I need to develop a minimum-user-interface system for posterity. I saw once that it is possible to pick out data from a text file using VBA, but I can't find where I saw that now that I need it. If I can get the basic commands I need, I can put it together.

    What's nice is if I can create a module that will handle this data format, it will work on nearly every file we get out of our enterprise system.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import from inside a text file (2k3)

    Could you attach a small sample text file? The data can be fake.
    If you do, I'll try to come up with code to read the text file.

  5. #5
    New Lounger
    Join Date
    Dec 2006
    Location
    Michigan, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import from inside a text file (2k3)

    Ok, Hans it's a deal. [img]/forums/images/smilies/smile.gif[/img]

    I have attached a file for you to play with. This is a typical output, but number of records will change every time, and each file has a different number of columns.

    Thanks!

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import from inside a text file (2k3)

    I'll look at it later today.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Import from inside a text file (2k3)

    It would be much more convenient, of course, if you could get your enterprise system to produce files in a usable format; after all, it must be stored there in a database-like format. It's rather silly to export data from a database to an unusable format, then spend time to convert it back into a database table.

    The following code will prompt the user to select a text file, then use Excel to strip out the nonsense, save the file as a .csv file and import it into Access.

    Sub ImportUsingXL()
    Dim strFile As String
    Dim xlApp As Object
    Dim xlWbk As Object
    With Application.FileDialog(msoFileDialogOpen)
    .Filters.Clear
    .Filters.Add "Text files (*.txt)", "*.txt"
    If .Show = True Then
    strFile = .SelectedItems(1)
    Else
    Exit Sub
    End If
    End With
    On Error GoTo ErrHandler
    Set xlApp = CreateObject("Excel.Application")
    xlApp.Workbooks.OpenText FileName:=strFile, _
    StartRow:=4, DataType:=1, ConsecutiveDelimiter:=False, _
    Tab:=True, FieldInfo:=Array(Array(1, 9))
    Set xlWbk = xlApp.ActiveWorkbook
    xlApp.Rows("2:2").Delete
    xlApp.DisplayAlerts = False
    strFile = Replace(strFile, ".txt", ".csv")
    xlWbk.SaveAs FileName:=strFile, FileFormat:=6
    xlWbk.Close SaveChanges:=False
    xlApp.DisplayAlerts = True
    DoCmd.TransferText TransferType:=acImportDelim, _
    TableName:="tblImport", FileName:=strFile, HasFieldNames:=True
    ExitHandler:
    On Error Resume Next
    Set xlWbk = Nothing
    xlApp.Quit
    Set xlApp = Nothing
    Exit Sub
    ErrHandler:
    MsgBox Err.Description, vbExclamation
    Resume ExitHandler
    End Sub

  8. #8
    New Lounger
    Join Date
    Dec 2006
    Location
    Michigan, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Import from inside a text file (2k3)

    I agree that it's absolutely silly to have to do this. Don't even get me started on the dismal data management policies that I have to put up with every day. Unfortunately I work for a very large company, and the only thing I could possibly accomplish by complaining about the database is a good deal of annoyance for my supervisor.

    I will try out your code and see what that comes of it. Thanks for your advice Hans!

Posting Permissions

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