Results 1 to 4 of 4
  1. #1
    Join Date
    Feb 2013
    Posts
    2

    Unanswered: How to automate importing a text file

    Hi all, I have been asked to assist one of our other departments with an Access database that they use daily and I'm not sure what the best way to handle this would be.

    Today they have a program that runs against multiple databases on our mainframe and dumps a large bar delimited text file into a folder on the network - we'll just call it ERRORFILE.txt. They import this file into access as-is and create a table called ERRORFILE. That table is used for a bunch of different queries that they use to report on errors from the previous day.

    Every new text file is around 400MB so it is a time consuming process to go in every morning and blow away the old table then import the new one.

    My first suggestion to them was to link to the text file instead of importing it since it never changes name. They said they tried that at first and it didn't work because they need to have multiple users in the DB at once and that causes problems.

    Any suggestions on this one? I found the TransferText method for VBA and could probably do something with that but I'm wondering if there is an easier way that I am overlooking?

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    As you do not specify wheter each line in text file must be imported as a single piece of data or if it's a CSV file (several columns separated by commas or another separator character), I'll assert the first.

    You can copy-paste the following two VBA procedures in a new or existing Standard Module (i.e. not a Module behind a Form):
    Code:
    Option Compare Database
    Option Explicit
    
    Public Function CreateTable(ByVal TableName As String, Optional ByVal EreaseExisting As Boolean = True) As Long
    
        Const c_SQL = "CREATE TABLE @T ( RowID COUNTER (1,1) CONSTRAINT Pk PRIMARY KEY, TextLine Text(255) );"
        
        On Error GoTo Err_CreateTable
        If DCount("*", "MSysObjects", "Name='" & TableName & "'") > 0 Then
            If EreaseExisting = False Then
                Exit Function
            Else
                CurrentDb.Execute Replace("DROP TABLE @T;", "@T", TableName), dbFailOnError
            End If
        End If
        CurrentDb.Execute Replace(c_SQL, "@T", TableName), dbFailOnError
        CreateTable = True
            
    Exit_CreateTable:
        Exit Function
        
    Err_CreateTable:
        CreateTable = Err.Number
        Resume Exit_CreateTable
    
    End Function
    
    Public Sub ImportTextFile(ByVal FileName As String, ByVal TableName As String)
    
        Const c_SQL As String = "INSERT INTO @T ( TextLine ) VALUES ( '@L' );"
        
        Dim intHandle As Integer
        Dim strLine As String
        Dim lngResult As Long
        
        On Error GoTo Err_ImportTextFile
        If Len(Dir(FileName)) > 0 Then
            lngResult = CreateTable(TableName, True)
            If lngResult = True Then
                intHandle = FreeFile
                Open FileName For Input As #intHandle
                Do Until EOF(intHandle)
                    Line Input #intHandle, strLine
                    strLine = Replace(strLine, "'", "''")
                    CurrentDb.Execute Replace(Replace(c_SQL, "@L", strLine), "@T", TableName), dbFailOnError
                Loop
                Close #intHandle
            Else
                MsgBox "Error: " & lngResult & vbNewLine & "occurred while trying to create the input table.", _
                       vbExclamation, "Create Table error"
            End If
        Else
            MsgBox "The file: " & FileName & vbNewLine & "Does not exists.", vbExclamation, "File not found"
        End If
        
    Exit_ImportTextFile:
        Exit Sub
        
    Err_ImportTextFile:
        If intHandle <> 0 Then Close #intHandle
        MsgBox "Error: " & Err.Number & " (" & Err.Description & ")" & vbNewLine & _
               "occurred in the procedure ImportTextFile.", vbExclamation, "Import Text file error"
        Err.Clear
        Resume Exit_ImportTextFile
    End Sub
    You can then call the procedure ImportTextFile from anywhere in your application:
    Code:
    ImportTextFile <FileName>, <TableName>
    Where:
    <FileName> is the name (full path) of the file to be imported.
    <TableName> is the name of a temporary destination table.

    You can then transfer the imported lines in the final destination table if necessary, with a query:
    Code:
    INSERT INTO <final destination table> ( <ColumnName> ) 
    SELECT TextLine 
    FROM <temporary destination table>
    ORDER BY RowId;
    If the file to be imported has a CSV structure, the same principle can be used but the structure of both VBA procedure would be a little more complex, as would also be the structure of the temporary destination table.
    Have a nice day!

  3. #3
    Join Date
    Feb 2013
    Posts
    2
    I mentioned in my OP that it is bar delimited. Will that make a difference?

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. What do you mean with "bar delimited"? Are there several columns (fields) on each line of the text file? Are those columns delimited by a bar ("|", ANSI or ASCII 124)? Please provide a sample line if you can.

    2. The question is not whether the lines contain separators or not, but whether you want to process those separators while importing the file (this would require a more complex table structure than the one I used in my example, but that's not really difficult) or if the processing will take place later.
    Have a nice day!

Posting Permissions

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