text file to upload in a table (1 Viewer)

Vinod9111

New member
Local time
Today, 20:06
Joined
Sep 4, 2020
Messages
19
Hi All,

Am facing a little challenge with regard to uploading text file in a table . The number of records are large in number resulting it to surpass 2 GB in size.

Any ideas how to work around to get it upload.

regards

Vinod Krishna.
 

bob fitz

AWF VIP
Local time
Today, 14:36
Joined
May 23, 2011
Messages
4,166
How big is the db before you try importing data. Have you done a "Compact and Repair" on the db.
 

arnelgp

error reading drive A:
Local time
Today, 22:36
Joined
May 7, 2009
Messages
11,211
if you are interested, i have a VBA that manually add records to a table from a Textfile.
 

arnelgp

error reading drive A:
Local time
Today, 22:36
Joined
May 7, 2009
Messages
11,211
it is for you to test, or if you like upload the db + textfile, for me to test.
 

arnelgp

error reading drive A:
Local time
Today, 22:36
Joined
May 7, 2009
Messages
11,211
it will not append a Complex Field (ie Attachment, MFV):
Code:
''''''''''''''''''''''''
'arnelgp
'
'note:
'
'structure of table/textfile must be the same
'
Public Function TextFileToTable(ByVal pstrTextFile As String, ByVal pstrTable As String, Optional ByVal delim As String = ",")
    Dim arrColumn() As String
    Dim db As DAO.Database
    Dim rs As DAO.recordSet
    Dim intFile As Integer
    Dim strData As String
    Dim i As Integer
    Dim var As Variant
    Dim varValue As Variant
    
    '* check if the textfile exists
    If Len(Dir(pstrTextFile)) = 0 Then
        Exit Function
    End If
    '* open the table
    Set db = CurrentDb
    If InStr(pstrTable, "SELECT ") > 0 Then
        Set rs = db.OpenRecordset( _
            "SELECT * FROM (" & pstrTable & ") " & _
            "WHERE (1=0);", dbOpenDynaset)
    Else
        Set rs = db.OpenRecordset( _
            "SELECT * FROM [" & pstrTable & "] " & _
            "WHERE (1=0);", dbOpenDynaset)
    End If
    Set db = Nothing
    intFile = FreeFile
    '* open the text file
    Open pstrTextFile For Input As #intFile
    '* assume that the first line on text file is the header
    Input #intFile, strData
    var = Split(strData, delim)
    '* save the header to dictionary
    ReDim arrColumn(0 To UBound(var))
    For i = 0 To UBound(var)
        arrColumn(i) = var(i)
    Next
    '* step through each line in textfile
    Do Until EOF(intFile)
        Input #intFile, strData
        var = Split(strData, delim)
        rs.AddNew
        For i = 0 To UBound(var)
            Select Case rs.fields(arrColumn(i)).Type
            Case dbByte
                varValue = CByte(var(i))
            Case dbInteger
                varValue = CInt(var(i))
            Case dbSingle
                varValue = CSng(var(i))
            Case dbDouble
                varValue = CDbl(var(i))
            Case dbLong
                 varValue = CLng(var(i))
            Case dbDate
                varValue = CDate(var(i))
            Case dbCurrency, dbDecimal
                varValue = CCur(var(i))
            Case Else
                varValue = var(i)
            End Select
            rs.fields(arrColumn(i)) = varValue
        Next
        rs.Update
    Loop
    Close #intFile
    rs.Close
    Set rs = Nothing
    Erase var
    Erase arrColumn
End Function
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 09:36
Joined
Feb 19, 2002
Messages
30,680
It would be more helpful if we knew what type of text file you were importing. If it is a fixed width file, then you might have to trim the columns before appending them to your Access table. In Access all columns are variable in width but when importing a fixed width file, the trailing blanks are also imported unless you trim them so if the Last Name is fixed at 35 characters and the name is Smith which is only 5, then you don't want the 30 extra spaces to be stored. To prevent that, you would use Trim(LastName) in your append query.

The general process is to link to the text file creating an import spec that defines the record field by field. Then you would create an append query that selects the data from the linked table and appends it to the Access table. In the query, you would Trim() and Format() as necessary to adjust the data to conform to how you want it to be saved.

If It still won't load because it is too big. You won't be able to use Jet/ACE to store the data. You can still use Access to create an application to use the data but you will need to use SQL Server or some other RDBMS to actually hold the data since Jet/ACE are limited to less than 2 gig for a single table because you need space for indexes and overhead.
 

Isaac

Lifelong Learner
Local time
Today, 06:36
Joined
Mar 14, 2017
Messages
3,325
@Vinod9111 Maybe you can just link to it.
This approach requires the data in the text file to be very consistent, but, if there are any problems in this area, they might be fleshed out during your first attempts at running your needed queries on the link. I virtually never recommend linking to an external file (like Excel, CSV, or TXT), but if the size of an import is going to exceed what can be stored in Access, you might as well put it on the list of options.

A plain text file of anywhere near that size is an enormous amount of data. I'm going to hazard a guess that you MAY not actually need all of those records....In which case you may need to go with a VBA method of evaluating line by line....Only import the ones you really need.
 

Users who are viewing this thread

Top Bottom