Help Importing Large Text File

[Seti]

I am trying to import a text file that has a record length of 1300 bytes and is roughly 250 MB in size. I am importing into about 130 fields and all fields are declared as text through the import wizard.

Once I import, the database goes from almost 0 MB (nothing in the database) to almost 900 MB. Compacting and repairing after import does not help.

My simple brain says import a text file into text fields means almost no overhead, not 600 MB on a 250 MB file. Am I doing something wrong? Is this expected in Access 2000?

Thanks for any insights.

Seti

 

[dk]

Hi,

My first thought upon reading your post was some sort of Unicode issue. Lo and behold! http://support.microsoft.com/defaul...port/kb/articles/q239/5/27.asp&NoWebContent=1

I did a quick test:

Text file of approx 1300 bytes per record x 192000 records = 246,188KB.

When I imported the text file into a new table i.e. with UnicodeCompression for each field set to No the database size was 768,204KB. I then tried the same thing but this time importing into an existing table with the UnicodeCompression set to Yes. The resulting database size was 384,204KB - almost exactly half as would be expected.

HTH
Dan

 

[Seti]

Thanks Dan. That was dead on.

 

[mdmilner]

Interesting. So what would be a good way to change the UnicodeCompression on all appropriate fields via VBA?

I was digging around in TableDef & Recordset and didn't see an obvious way to change the value -- yet. Normal par for me is to find the answer about 5 minutes after I post so the race is on...

Here's where I was going with this:

Sub SetUnicode()
Dim db As DAO.Database
Dim tbl As TableDef
Dim rs As DAO.Recordset
Dim x As Integer
Dim strSQL As String
Set db = CurrentDb

For Each tbl In db.TableDefs
  strSQL = "SELECT * FROM " & tbl.Name
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
  For x = 0 To rs.Fields.Count - 1          ' or tbl.Fields.Count
    'rs.Fields(x).
  Next x
Next tbl

End Sub

Mike

 

[dk]

After quite a bit of experimentation I'm not sure if the property can be set programatically after the table has been created. I tried to use ADOX to change the property and it failed:-

Sub SetUnicodeCompression()

    Dim oADOXCat As Object    'ADOX.Catalog
    Dim oADOXColumn As Object    'ADOX.Column

    'Establish a catalog object and connect it to this database
    Set oADOXCat = CreateObject("ADOX.Catalog")
    Set oADOXCat.ActiveConnection = CurrentProject.Connection


    'Loop through all fields in the table 'Test' and set the
    'unicode compression property to true

    For Each oADOXColumn In oADOXCat.Tables("Test").Columns
        'Failed here
        oADOXColumn.Properties("Jet OLEDB:Compressed UNICODE Strings").Value = True
    Next oADOXColumn


    'Clean up
    Set oADOXColumn = Nothing
    Set oADOXCat.ActiveConnection = Nothing
    Set oADOXCat = Nothing
End Sub

The value of the Attributes property of the "Jet OLEDB:Compressed UNICODE Strings" property at the time the code failed property was 1537. According to the MSDN this indicates the following:-

<ul>[*]adPropReadWrite - we can set this property.[*]adPropRead - we can read this property.[*]adPropRequired - value must be specified before data source is initialised[/list]

I think therefore that if you want the Unicode compression to be false then you need to set up the table before you import the data. Interestingly, if you create the table using SQL (which is how I usually create temp tables) the compression is still false by default. This is the opposite of what happens when you create a table using the Access UI. A 'solution' is to use ADOX to create the table. The Unicode Compression property will be true when you do this:-

Sub SetUnicodeCompression()

    Dim oADOXCat As Object 'ADOX.Catalog
    Dim oADOXColumn As Object    'ADOX.Column
    Dim oADOXTable As Object 'ADOX.Table
    Dim lngNameSuffix As Long

    'Establish a catalog object and connect it to this database
    Set oADOXCat = CreateObject("ADOX.Catalog")
    Set oADOXCat.ActiveConnection = CurrentProject.Connection

    Set oADOXTable = CreateObject("ADOX.Table")
    oADOXTable.Name = "MyTable"

    For lngNameSuffix = 1 To 3
        Set oADOXColumn = CreateObject("ADOX.Column")
        oADOXColumn.Name = "Field" & lngNameSuffix
        oADOXColumn.Type = 202 'adVarWChar
        oADOXColumn.DefinedSize = 255
        oADOXTable.Columns.Append oADOXColumn
    Next lngNameSuffix


    oADOXCat.Tables.Append oADOXTable

End Sub

Anyway, I've been playing around with this for over an hour now so should get on with some 'real' work.

Dan