MS Access Table Import using Schema.ini

[jnlw09]

I'm stuck. All I want to do is import data from a fixed-length (fields are not delimited) text file into an MS Access database table. The table is already defined with names and field lengths. The Schema.ini file contains table name, the same field names and lengths as defined in the table. I just cannot seem to find a programming structure to do it.

Does anyone have a suggestion?

Many thanks.

 

[DCrake]

You can use the import wizard to create an import specification (similar to your schema) and save it then use it to do the import.

David

 

[jnlw09]

Thanks, David. I actually have about 20 tables to import, and the problem is that the layouts of the tables may change from time to time. I have a database table of 'table names' and another table of data names for each table. From this I build the actual structure of each table. When this changes, for example if I add a field to a table, I want to be able to regenerate the entire set automatically. That part I have done OK. What I cannot seem to get answered, is, how to get the actual data 'in'. It seems a simple enough scenario to envision, using the schema.ini as the 'view', but coding it is another matter.

Regards,
jnlw09

 

[DCrake]

Let me get this right you have a table in Access that list sthe table names and field names and the Mid and Length of each field postion in the string.

Record1:
TableName :Table1
FieldName :Field1
StartPos :1
Len :10

Record2
TableName :Table1
FieldName :Field2
StartPos :11
Len :15

Record3
TableName :Table1
FieldName :Field3
StartPos :26
Len :25

Etc

If this is correct then you are looking at using the Open TxtFile For Input routine to read in each line of your text file and seperate it out using the data obtained from the recordset for the coressponding table.


David

 

[jnlw09]

Yes, David. That's about it. I don't have the start position, but that's easily overcome. I think you've answered my question, though, with the suggestion of Open..for input. This means I need to do the calculating and looping myself, instead of a structure like "IMPORT INTO.." etc. Ah well, it was worth a try.

Thank you for your assistance!

Cheers,
.jnlw09

 

[DCrake]

This must be your lucky day, other than that I must be feeling generous. but find below the code you will need. It is untested but I think all angles are covered.

This code is part of a larger application that handles many text files manipulations.

Public Function ImportFixedWidthTextFile(TextPath As String, TextFileName As String, TableName As String)
'*************************************************************************************************************************
'Created by :D Crake X-Craft Limited
'Date       :30th June 2009
'Arguments  :TextPath        - folder where the incoming text file is located
'           :TextFileName    - name of file in above folder
'           :TableName       - name of table that text file is being import into
'
'Purpose    :Open up a fixed width text file and by reading the schema from a table it imports the relevant data
'           :into the relevant fields in the table.
'           :
'Comments   :Table name in col 1, fieldname in col 2, start pos in col 3, field length in col 4
'Example    :ImportFixedWidthTextFile("C:\Temp","TextFile.txt","TblSampleData")
'Issues     :Does not validate the existance of the table or that the field are named correctly
'**************************************************************************************************************************
'Copywrite  :There is no copywrite on this code, however, if passed on then pass on the above comments.
'**************************************************************************************************************************


'Does the source file exist in the target folder?

If Dir(TextPath & "\" & TextFileName) = "" Then
    MsgBox "Path or file name is invalid", vbExclamation + vbOKOnly, "Import Abandoned"
    Exit Function
End If

Dim Rs              As DAO.Recordset
Dim Rs2             As DAO.Recordset
Dim strFieldName    As String
Dim sPos            As Integer
Dim sLen            As Integer
Dim strText         As String

Set Rs = CurrentDb.OpenRecordset("Select * From tblSchemas Where fldTblName = '" & TableName & "'")
Set Rs2 = CurrentDb.OpenRecordset(TableName)

If Not Rs.EOF And Not Rs.BOF Then
    'read the contents of the text file one line at a time
    Open TextPath & "\" & TextFileName For Input As #1
    Do Until EOF(1)
        Line Input #1, strText
    
        'Get the table defnintions from the table
        Do Until Rs.EOF
            strFieldName = Rs(1)
            sPos = Rs(2)
            sLen = Rs(3)
        
            'add the portion of the string to the record in the table
            Rs2.AddNew
            Rs2(strFieldName) = Mid(strText, sPos, sLen)
            Rs2.Update
            
            Rs.MoveNext
        Loop
    Loop
    Rs.Close
    Close #1
End If

'close the instances of the objects
Set Rs = Nothing
Set Rs2 = Nothing
End Function

 

[jnlw09]

Thank you, David, for recognising my obvious frustration. I appreciate your generosity, and will let you know what happens.

Cheers,
.jnlw09.

 

[Macjnr]

Hi David,

Just came accross your post and tested it. It works fine for me but the data is imported such that each field has a single entry corresponding to the column matched from the schema.

i.e record one has only one entry in field on, record two has one entry in field 2 etc etc

What can I do to ensure that record 1 has a value in field 1,2, 3.... as is in the text file.

Thank you
Chris

 

[gemma-the-husky]

looks like nice elegant code, david.
clever idea


macjnr

take this bit of code

         Line Input #1, strText  [COLOR="Red"](note 1)[/COLOR]
    
        'Get the table defnintions from the table
        Do Until Rs.EOF
            strFieldName = Rs(1)
            sPos = Rs(2)
            sLen = Rs(3)
        
            'add the portion of the string to the record in the table
            Rs2.AddNew
            Rs2(strFieldName) = Mid(strText, sPos, sLen)
            Rs2.Update
            
            Rs.MoveNext
        Loop

note 1 - this line reads a whole line of the input file into a single text variable (strtext)

now you need a way of splitting this into fields

david is using an exernal table that defines the way the string should be split, showing the position and length of fields to be extracted. so the next bit of his code takes each segment and gets it from the string just imported. You don't have to use the whole string - just the bits you want - similar really to the file import spec itself, and terefore useful if you need to change the mapping.

i have sometimes done this by declariing a specific array into which i can map the fields

david's code obviously has an advantage in that you can edit and use HIS import mechanism by changing the mapping table, without needing to change the underlying code (which can therefore be distributed as a mde). If you use a hard-coded array (as I do) then you have to hard-code each change, and it is less flexible - although it still works fine.