Access text file import

[bob]

I have done this before in Foxpro. Need to do in Access.

Need to take a text file that is fixed and import into a database [no wizards!]

Example

table1

field1 = 2
Field2 = 5

Text file data looks like this
AABBBBB
AABBBBB

Want to import automatically from the text file where the AA goes into field1 and the BBBBB goes into field2

[Edited by bob on 03-30-2001 at 05:04 AM]

[mosquito]

I think the fastest way to do this is to use ADO. To achieve this, create a reference to "Microsoft Active X data object" in your MsAccess vba project.
Then you have to create a schema.ini file in the directory with the file you want to import. This schema.ini file will define the structure of your text file.

Here is an example of how the schema.ini file could look like. Do note that the "Sample.txt" can be replaced with the filename you are using

[Sample.txt]
Format=FixedLength
CharacterSet=ANSI
Col1=field1 Text Width 2
Col2=field2 Text Width 5

Now the code to include in your VBA code.

    Dim conn        As ADODB.Connection
    Dim rs	        As ADODB.Recordset
    
    Set conn = New Connection
        
    'Connect to the Directory where the textfile is                                     '
    With conn
        .CursorLocation = adUseClient
        .ConnectionString = "Provider=MSDASQL;DRIVER={Microsoft Text Driver (*.txt; *.csv)};DefaultDir=" & sDir
        .Mode = adModeRead
        .Open
    End With
    
    
    Set rs = New Recordset
    With rs
        .ActiveConnection = conn
        .CursorType = adOpenForwardOnly
        .LockType = adLockReadOnly
        .Open "select * from [" & sFile & "]"
    End With
    
    Do While Not rs.EOF
        docmd.runquery "Insert into t_table (field1, field2, field3) values (" & _
                       rs.fields("field1") & ", " & rs.fields("field2") & _
                       ", " & rs.fields("field3") & ")"
        rs.movenext
    loop
    
    rs.close
    
    set rs = nothing
    conn.close
    set conn = nothing

That's it. I always create the schema.ini file with code to make the application more flexible.

[bob]

Thanks. Really wanted to avoid that - may just have to do it anyways. Must deal with 20+ different flat files with from 10 to 40 fields each.

[Great_One]

Another option would be to create a macro and use the transfer text option to import the file. you will need to run the import wizard the first time to create and save the import specification, but after that simply running the macro will handle the import of the marco (assuming the file locations and the text file layouts remain static).
the macro can handle multiple transfer text statements so you should be able to do it all with one click.

[bob]

You are the great one for sure. That was exactly what I was thinking on how to solve the problem. Also have GUI programs that need data input too. Macros have the best hope for a solution.

All input files are text and fixed - no deliminators.
I can use ADO - thanx mosquito - Foxpro (because I know it well) can be the main app to automate the processes.

In the old days I only needed to create a simple import with foxpro.
...
open database
append from xxxxxx.txt sdf
...

Grrrrrrrrrrrrrrr.
Worked my way up to WAN admin and now I need to manage data processing and programming. It would be nice if I could reuse my experience but things change fast.