Results 1 to 3 of 3
  1. #1
    Join Date
    Nov 2013

    Unanswered: Automating a Fixed Width Import from Text and CSV file

    I'm using Access 2010
    I've created a couple of databases at my new job for maintaining files that are brought in on a daily basis. Most of my work so far has just been with queries to update and format the data as it comes in.

    We receive multiple text files and csv files each day. The text files go into one database and csv files in another.

    Both files are around 2000 characters long, and are imported using a saved fixed width specification. It hasn't been too much of a hassle to import these manually as they come in, but things have picked up recently and it is becoming time consuming to do each of these files manually.

    I'm looking for a way to "automate" this a bit as the import steps are the same each time. I haven't been able to use the "Saved Imports" feature as the files come with different names so I would like a way to still have the "Browse" feature open so that the file can be selected manually.

    The Import Steps are:

    External Data > From Text > Import into new table > Advanced > Specs > Open > Select Primary Key > Name Table(this is always the same).

    I'm not familar with VB, but I'm always willing to learn and try and get my hands dirty.

    Once this part and built, I will be having other people begin Importing the daily files into this database, so I'd like the end product to be as simple as possible where all thats needed is to select the file being imported and then all the other steps above are done behind the scenes.

    Thanks for any help!

  2. #2
    Join Date
    Nov 2012

    I have never done this before, but I think in VBA a function DoCmd.TransferText, acImportFixed would be appropriate.

    If you want to use VBA you may find this overview helpful:

    Google around and you will find what you'll looking for, this seemed to contain more that enough information:
    TransferText - specificationname? - Xtreme Visual Basic Talk

    I hope this helps!


  3. #3
    Join Date
    Mar 2009
    Provided Answers: 14
    Asserting that the structure of the files to be imported is constant (i.e. that the number of columns to be imported is always the same), you could build your own File Importation system. Here's an example:
    Option Compare Database
    Option Explicit
    Private Function GetName(ByVal FilePath As String) As String
        Dim x As Long
        x = InStrRev(FilePath, "\") + 1
        FilePath = Mid(FilePath, x)
        x = InStrRev(FilePath, ".")
        GetName = Left(FilePath, x - 1)
    End Function
    Public Sub Import(ByVal TableName As String, Optional ByVal FilePath As String, Optional ByVal Separator As String, Optional ByVal Append As Boolean)
        Const c_SQL As String = "SELECT * INTO @Table FROM @STable WHERE 1 = 2;"
        Const c_Prompt As String = "A table named @N already exist in the database." & vbNewLine & _
                                  "Do you want to replace the existing table?"
        Dim strFileName As String
        Dim strTableName As String
        strFileName = SelectFile(FilePath)
        If Len(strFileName) > 0 Then
            If Append = True Then
                strTableName = TableName
                strTableName = GetName(strFileName)
                If DCount("*", "MSysObjects", "Name='" & strTableName & "'") > 0 Then
                    Select Case MsgBox(Replace(c_Prompt, "@N", strTableName), vbInformation + vbYesNoCancel, "Import: Existing table")
                        Case vbYes
                            DoCmd.DeleteObject acTable, strTableName
                        Case vbNo
                            strTableName = strTableName & "_" & Format(Now, "yyyymm_hhnnss")
                        Case vbCancel
                            Exit Sub
                    End Select
                End If
                CurrentDb.Execute Replace(Replace(c_SQL, "@Table", strTableName), "@STable", TableName)
            End If
            ImportFile strFileName, strTableName, Separator
        End If
    End Sub
    Private Sub ImportFile(ByVal FilePath As String, ByVal TableName As String, ByVal Separator As String)
        Dim rst As DAO.Recordset
        Dim intHandle As Integer
        Dim strLine As String
        Dim varItems As Variant
        Dim i As Long
        Set rst = CurrentDb.OpenRecordset(TableName, dbOpenDynaset)
        intHandle = FreeFile
        Open FilePath For Input As #intHandle
        Do Until EOF(intHandle)
            Line Input #intHandle, strLine
            If Len(Separator) > 0 Then
                varItems = Split(strLine, Separator)
                varItems = Splinter(strLine)
            End If
            With rst
                For i = 0 To UBound(varItems)
                    rst.Fields(i).Value = varItems(i)
                Next i
            End With
    End Sub
    Private Function SelectFile(Optional ByVal Path As String) As String
        Dim Fdlg As FileDialog
        Set Fdlg = Application.FileDialog(msoFileDialogOpen)
        With Fdlg
            .AllowMultiSelect = False
            .InitialFileName = Path
            .Filters.Add "CSV files", "*.csv;*.txt", 1
            If .Show = -1 Then SelectFile = .SelectedItems(1)
        End With
    End Function
    Private Function Splinter(ByVal Line As String) As Variant
        Const c_Positions As String = "1,12,20,30,255"
    ' Instead of the constant c_Positions, it's also possible to use a table
    ' (e.g. Tbl_Positions: 'CREATE TABLE Tbl_Positions ( Position LONG );')
    ' that contains the position of each data column.
    ' In such a case, the array varPos can be initialized with:
    '     Dim rst As DAO.Recordset
    '     Set rst = CurrentDb.OpenRecordset("Tbl_Positions", dbOpenSnapshot)
    '     varPos = rst.GetRows(999)
    '     rst.Close
    '     Set rst = Nothing
        Dim varPos As Variant
        Dim var As Variant
        Dim i As Long
        varPos = Split(c_Positions, ",")
        ReDim var(0 To UBound(varPos) - 1)
        For i = 0 To UBound(varPos) - 1
            var(i) = Trim(Mid(Line, varPos(i), varPos(i + 1) - varPos(i)))
        Next i
        Splinter = var
    End Function
    You call it by calling:
    Import "Tbl_Source","Initial Path","Separator",Append (True/False)
    - "Tbl_Source" is the name of a table with the proper structure to receive the data being imported (this table must exist although it can be empty).
    - "Initial Path" is the path where the file to be imported should be found.
    - "Separator" is the character used to separate the data elements when the file to be imported is in the "pure csv format (urually: "," or ";" or vbTab). Leave this argument empty for a fixed-length file.
    - Append is a boolean value (True or False). If set to True, the imported data will be appended to the table "Tbl_Source". If set to False, a new table will be created on the model of "Tbl_Source" and with the name of the imported file (e.g. is the imported file is "C:\Data.txt", the name of the table will be "Data"). If a table with the same name already exists, a dialog box will pop up and propose 3 options:
    Yes: Replace the existing table.
    No: append a TimeStamp to the base name of the new table (e.g. "Data" becomes "Data_201311_144418").
    Cancel: Abort the importation process.

    For fixed-length data files, you could create a table containing the position of each "column" instead of using the constant c_Position (see the remarks in the Splinter() function).

    A more complex system that would be able to handle various type of importations could be created using a Class with several initialization parameters.
    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