Results 1 to 8 of 8
  1. #1
    Join Date
    Apr 2011
    Posts
    4

    Unanswered: Import large Text file into Access with specific field types

    Can someone help me in importing a very large (>3 MM records) text file (.csv)
    into Access 2010?
    I would like to import in specific field types.
    I would really appreciate if anyone can throw light.

    So far, I have used following script
    DoCmd.TransferText acImport, , "tmmgr#ord", "C:\Users\hdharmadhikari\Documents\BizProj\2011_Pr ojects\041311_Kohls\01DataRecd\ORD.csv", True

    This script pulls the data and assign the field type based on first row contents.
    Ex: First row of field1 is number then it assigns Number as field type and hence does not import any data that has text in the same field type.

    Any help will be appreciate.

    Thank you.

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can import the file into an existing table which will have the data type of its columns already defined.
    Have a nice day!

  3. #3
    Join Date
    Apr 2011
    Posts
    4

    @Sinndho

    This is a huge file so you can not append using regular import data wizard.

    Thanks though.

    Harshad

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Why not? You're using the TransferText method for importing the "huge" file now. What will change if you import it to an existing table in place of importing it to a new one?
    Have a nice day!

  5. #5
    Join Date
    Apr 2011
    Posts
    4

    thank you

    Do you know any function which I can use to append data.
    I would appreciate if you can provide me a complete code- if you know.
    Thanks.

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You can keep the code you use now. If the table exist the TransferText method will use it.

    Otherwise, here's a possibility. However, it is rather complex, needs an error handler, has several issues and is not garanteed to be more efficient than the TranferText method.

    This example uses a class Cls_DataArray. That class is normally used in programs that exchange data with a SQL Server, so many properties and Methods are useless here. There can also be a problem with the formating of the data (Function Coerce) that is adapted to the data format expected by a SQL Server, not by an Access Query.

    Here's the code of Cls_DataArray:
    Code:
    Option Compare Database
    Option Explicit
    
    Private Type DataArray
        Value As Variant
        Name As String
        Type As Long
    End Type
    
    Private m_varArray() As DataArray
    Private m_lngMaxIndex As Long
    Private m_booAllowNull As Boolean
    
    Public Property Get AllowNull() As Boolean
    
        AllowNull = m_booAllowNull
        
    End Property
    
    Public Property Let AllowNull(Status As Boolean)
    
        m_booAllowNull = Status
        
    End Property
    
    Private Function Coerce(ByVal Argument As Variant, ByVal DataType As Long) As String
    
        If m_booAllowNull = False Then
            Select Case DataType
                Case dbText, dbMemo
                    Argument = Nz(Argument, "")
                Case Else
                    Argument = Nz(Argument, 0)
                    If Not IsNumeric(Argument) Then Argument = Val(Argument)
            End Select
        End If
        If IsNull(Argument) Or ((DataType <> dbText And DataType <> dbMemo) And Len(Argument) = 0) Then
            Coerce = "Null"
        Else
            Select Case DataType
                Case dbText, dbMemo
                    Coerce = "'" & Replace(Argument, "'", "'''") & "'"
                Case dbBoolean
                    Coerce = IIf(Argument = True, "1", "0")
                Case dbDate
                    Coerce = "'" & Format(Argument, "yyyymmdd") & "'"
                Case dbCurrency, dbDouble, dbFloat, dbSingle, dbDecimal
                     Coerce = Replace(CStr(Argument), "', '", ".")
                Case dbByte, dbInteger, dbLong
                    Coerce = CStr(Argument)
                Case Else
                    Coerce = vbNullString
            End Select
        End If
    
    End Function
    
    Public Property Get Count() As Long
    
        Count = m_lngMaxIndex + 1
    
    End Property
    
    Public Property Get DataType(ByVal Index As Variant) As Long
    
        Dim i As Integer
        
        If Not IsNumeric(Index) Then
            Index = Me.Index(Index)
        Else
            Index = Validate(Index)
        End If
        DataType = m_varArray(Index).Type
    
    End Property
    
    Property Get Exist(Name As String) As Long
    
        Dim i As Long
        
        For i = 0 To m_lngMaxIndex
            If m_varArray(i).Name = Name Then
                Exist = True
                Exit For
            End If
        Next i
    
    End Property
    
    Public Function FromString(ByVal CsvRow As String, Optional ByVal Separator As String = ",")
    
        Dim var As Variant
        Dim i As Long
        
        var = Split(CsvRow, Separator)
        If UBound(var) <> UBound(m_varArray) Then ReDim Preserve var(0 To UBound(m_varArray))
        For i = 0 To UBound(m_varArray)
            m_varArray(i).Value = var(i)
        Next i
        
    End Function
    
    Property Get Index(ByVal Name As String) As Long
    
        Dim i As Long
        
        Index = -1
        For i = 0 To m_lngMaxIndex
            If m_varArray(i).Name = Name Then
                Index = i
                Exit For
            End If
        Next i
    
    End Property
    
    Public Function Load(rst As DAO.Recordset) As Long
    
        Dim i As Integer
        
        With rst
            m_lngMaxIndex = .Fields.Count - 1
            ReDim m_varArray(-1 To m_lngMaxIndex)
            If (.BOF And .EOF) = False Then
                .MoveFirst
                For i = 0 To m_lngMaxIndex
                    m_varArray(i).Value = .Fields(i).Value
                    m_varArray(i).Name = .Fields(i).Name
                    m_varArray(i).Type = .Fields(i).Type
                Next i
            Else
                For i = 0 To m_lngMaxIndex
                    m_varArray(i).Value = Null
                    m_varArray(i).Name = .Fields(i).Name
                    m_varArray(i).Type = .Fields(i).Type
                Next i
            End If
        End With
        Load = i
    
    End Function
    
    Public Property Get MaxIndex() As Long
    
        MaxIndex = m_lngMaxIndex
    
    End Property
    
    Public Property Get Name(ByVal Index As Long) As String
    
        Index = Validate(Index)
        Name = m_varArray(Index).Name
        
    End Property
    
    Public Property Get Parameter(ByVal Index As Variant) As String
    
        Dim i As Integer
        
        If Not IsNumeric(Index) Then
            Index = Me.Index(Index)
        Else
            Index = Validate(Index)
        End If
        Parameter = "@" & m_varArray(Index).Name & "=" & Coerce(m_varArray(Index).Value, m_varArray(Index).Type)
    
    End Property
    
    Public Property Get SQLValue(ByVal Index As Variant) As String
    
        Dim i As Integer
        
        If Not IsNumeric(Index) Then
            Index = Me.Index(Index)
        Else
            Index = Validate(Index)
        End If
        SQLValue = Coerce(m_varArray(Index).Value, m_varArray(Index).Type)
    
    End Property
    
    Public Function ToString(Optional ByVal Normalize As Boolean = True) As String
    
        Dim var As Variant
        Dim str As String
        Dim i As Long
        
        For i = 0 To UBound(m_varArray)
            If Len(str) > 0 Then str = str & ", "
            If Normalize = True Then
                str = str & Coerce(m_varArray(i).Value, m_varArray(i).Type)
            Else
                str = str & Nz(m_varArray(i).Value, "")
            End If
        Next i
        ToString = str
        
    End Function
    
    Private Function Validate(ByVal Index As Long) As Long
    
        If Index < 0 Or Index > m_lngMaxIndex Then
            Validate = -1
        Else
            Validate = Index
        End If
        
    End Function
    
    Public Property Get Value(ByVal Index As Variant) As Variant
    
        Dim i As Integer
        
        If Not IsNumeric(Index) Then
            Index = Me.Index(Index)
        Else
            Index = Validate(Index)
        End If
        Value = m_varArray(Index).Value
    
    End Property
    
    Public Property Let Value(ByVal Index As Variant, ByVal Value As Variant)
    
        Dim i As Integer
        
        If Not IsNumeric(Index) Then
            Index = Me.Index(Index)
        Else
            Index = Validate(Index)
        End If
        m_varArray(Index).Value = Value
    
    End Property
    
    Public Property Get Values() As Variant
    
        Dim i As Integer
        Dim var As Variant
        
        ReDim var(0 To m_lngMaxIndex)
        For i = 0 To m_lngMaxIndex
            var(i) = m_varArray(i).Value
        Next i
        Values = var
    
    End Property
    
    Public Property Let Values(DataArray As Variant)
    
        Dim i As Integer
        
        For i = 0 To m_lngMaxIndex
            m_varArray(i).Value = DataArray(i)
        Next i
    
    End Property
    
    Public Function Void(Optional ByVal AllowNull As Boolean)
    
        Dim i As Integer
        
        For i = 0 To m_lngMaxIndex
            If AllowNull = True Then
                m_varArray(i).Value = Null
            Else
                If m_booAllowNull = True Then
                     m_varArray(i).Value = Null
                Else
                    Select Case m_varArray(i).Type
                        Case dbText, dbMemo
                            m_varArray(i).Value = ""
                        Case dbBoolean, dbByte, dbCurrency, dbDecimal, dbDouble, dbFloat, dbInteger, dbLong, dbNumeric, dbSingle
                            m_varArray(i).Value = 0
                        Case Else
                            m_varArray(i).Value = Null
                    End Select
                End If
            End If
        Next
    
    End Function
    And the code used to import a .csv file:
    Code:
    Function ImportTextFile(ByVal FileName As String, _
                            ByVal TableName As String, _
                            Optional ByVal Separator As String = ",", _
                            Optional ByVal SkipFirstLine As Boolean = False) _
        As Long
    
        Dim clsDataArray As Cls_DataArray
        Dim strLine As String
        Dim intHandle As Integer
        Dim i As Integer
        Dim tdf As DAO.TableDef
        Dim dbs As DAO.Database
        Dim rst As DAO.Recordset
        Dim strSQL As String
        
        Set dbs = CurrentDb
        Set tdf = dbs.TableDefs(TableName)
        For i = 0 To tdf.Fields.Count - 1
            If Len(strSQL) > 0 Then strSQL = strSQL & ", "
            strSQL = strSQL & tdf.Fields(i).Name
        Next i
        strSQL = "INSERT INTO " & TableName & "( " & strSQL & " ) VALUES ( "
        Set tdf = Nothing
        Set clsDataArray = New Cls_DataArray
        clsDataArray.AllowNull = True
        Set rst = dbs.OpenRecordset(TableName)
        clsDataArray.Load rst
        intHandle = FreeFile
        Open FileName For Input As #intHandle
        If SkipFirstLine = True Then Line Input #intHandle, strLine
        Do Until EOF(intHandle)
            Line Input #intHandle, strLine
            clsDataArray.FromString strLine, ";"
            dbs.Execute strSQL & clsDataArray.ToString(True) & " );"
        Loop
        Close #intHandle
        dbs.Close
        Set dbs = Nothing
        Set clsDataArray = Nothing
        
    End Function
    Have a nice day!

  7. #7
    Join Date
    Apr 2011
    Posts
    4

    Thumbs up Thanks

    Sinndho, Thank you much.
    I will try my best to use this.
    If it works will let you know.
    Thanks again.

  8. #8
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

Tags for this Thread

Posting Permissions

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