Results 1 to 6 of 6
  1. #1
    Join Date
    May 2011
    Posts
    17

    Unanswered: Import text file

    Hi

    I have a text file that I want to import into a table in access. see attached file. Can I get access to import the file as follows:
    A01
    B01
    C01
    D01
    E01
    F01
    G01
    H01
    A02
    B02, etc.
    if so how?

    in advance thanks for your help

    Geir Arne
    Attached Files Attached Files

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    In the File menu --> Get External Data --> Import
    Have a nice day!

  3. #3
    Join Date
    May 2011
    Posts
    17

    Import text file

    Quote Originally Posted by Sinndho View Post
    In the File menu --> Get External Data --> Import
    It’s was a bit poorly explained, what I meant was I know how to import text file. After I have imported the text file in the table it will look like this:
    Position
    A01
    A02 etc.

    But I want it to look like this:
    A01
    B01 etc
    Geir Arne

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    OK! Here's a solution. Create a new Module and paste this into it:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Function ObjectExists(ByVal ObjectName As String, ByVal ObjectType As Long) As Boolean
    
        Dim obj As AccessObject
        Dim dbs As Object
        Dim tdf As DAO.TableDef
        Dim qdf As DAO.QueryDef
        
        Set dbs = Application.CurrentProject
        Select Case ObjectType
            Case acTable
                For Each tdf In CurrentDb.TableDefs
                    If tdf.Name = ObjectName Then
                        ObjectExists = True
                        Exit For
                    End If
                Next tdf
            Case acQuery
                For Each qdf In CurrentDb.TableDefs
                    If qdf.Name = ObjectName Then
                        ObjectExists = True
                        Exit For
                    End If
                Next qdf
            Case acForm
                For Each obj In dbs.AllForms
                    If obj.Name = ObjectName Then
                        ObjectExists = True
                        Exit For
                    End If
                Next obj
            Case acReport
                For Each obj In dbs.AllReports
                    If obj.Name = ObjectName Then
                        ObjectExists = True
                        Exit For
                    End If
                Next obj
            Case acMacro
                For Each obj In dbs.AllMacros
                    If obj.Name = ObjectName Then
                        ObjectExists = True
                        Exit For
                    End If
                Next obj
            Case acModule
                For Each obj In dbs.AllModules
                    If obj.Name = ObjectName Then
                        ObjectExists = True
                        Exit For
                    End If
                Next obj
        End Select
        Set dbs = Nothing
    
    End Function
    
    Private Sub QuickSort(C() As String, ByVal First As Long, ByVal Last As Long)
    '
    ' Original from Michael Ciurescu (CVMichael from vbforums.com)
    ' Original thread: http://www.vbforums.com/showthread.php?t=231925
    '
    ' Adapted for sorting a bidimensional array.
    '
        Dim Low As Long
        Dim High As Long
        Dim MidValue As String
        
        Low = First
        High = Last
        MidValue = C(0, (First + Last) \ 2)
        
        Do
            Do While C(0, Low) < MidValue
                Low = Low + 1
            Loop
            Do While C(0, High) > MidValue
                High = High - 1
            Loop
            If Low <= High Then
                Swap C(0, Low), C(0, High)
                Swap C(1, Low), C(1, High)
                Low = Low + 1
                High = High - 1
            End If
        Loop While Low <= High
        
        If First < High Then QuickSort C, First, High
        If Low < Last Then QuickSort C, Low, Last
        
    End Sub
    
    Public Function SortImportTextFile(ByVal FileName As String)
    
        Const c_strSQL As String = "INSERT INTO [@Table] ( Field1, Field2 ) VALUES ( '@1', '@2' );"
        
        Dim strTableName As String
        Dim strLines() As String
        Dim strLine As String
        Dim intHandle As Integer
        Dim lngCount As Long
        Dim varline As Variant
        Dim strSQL As String
        
        intHandle = FreeFile
        Open FileName For Input As #intHandle
        Do Until EOF(intHandle)
            Line Input #intHandle, strLine
            strLine = Replace(Trim(strLine), vbTab, "")
            If InStr(strLine, "Date & time of Trace = ") > 0 Then strTableName = Replace(strLine, "Date & time of Trace = ", "")
            If lngCount > 1 Then
                ReDim Preserve strLines(0 To 1, 0 To lngCount)
                strLines(0, lngCount) = Mid(Trim(strLine), 2, 2) & Left(Trim(strLine), 1)
                strLines(1, lngCount) = strLine
            End If
            lngCount = lngCount + 1
        Loop
        Close #intHandle
        QuickSort strLines, 0, UBound(strLines, 2)
        If ObjectExists(strTableName, acTable) = True Then CurrentDb.Execute "DROP Table [" & strTableName & "];"
        CurrentDb.Execute "CREATE TABLE [" & strTableName & "] ( Field0 Counter PRIMARY KEY, Field1 Text(50), Field2 Text(50));"
        strSQL = Replace(c_strSQL, "@Table", strTableName)
        For lngCount = 0 To UBound(strLines, 2)
            If Len(strLines(1, lngCount)) > 0 Then
                varline = Split(strLines(1, lngCount), ";")
                CurrentDb.Execute Replace(Replace(strSQL, "@1", varline(0)), "@2", varline(1))
            End If
        Next lngCount
        
    End Function
    
    Private Sub Swap(ByRef A As String, ByRef B As String)
    
        Dim T As String
        
        T = A
        A = B
        B = T
        
    End Sub
    Usage: SortImportTextFile ""C:\Documents and Settings\SinnDHo\My documents\Access\Test (1).txt"

    It only works with a file format such as the one you provided ('Test (1).txt') and for this file it creates a table named '02 aug 2011 12:11:54 PM'.
    Have a nice day!

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or better yet split the A01 to two columns A 01
    set your primary key as a composite of the two columns
    you could add a new column to your existing table, called say prefix
    the run an update query
    update my table set prefix = left(mycolumn, 1) set mycolumn = mid(mycolumn,2,2) where length mycolumn = 3
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    Join Date
    Feb 2004
    Location
    Chicago, IL
    Posts
    1,312
    You can sort it any way you want in a query. I would create a query with all of you fields and then add a calculated sort field:

    SortField:Right([FieldName],2) & Left([FieldName],1)

    The sort field will look like:

    01A
    01B
    01C
    .
    .
    .
    02A
    02B

    You can choose to show the sort field or hide it, but your results will be sorted they way you want it.

Posting Permissions

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