Fixed Format by Starting Characters

[JDeezy]

I see where your coming from but the fields have multiple blanks in them. The only way to split them is using the starting characters and the length of the field.

 

[gemma-the-husky]

no - but someone is producing the file - its much better to fix it as source, then after import.

 

[JDeezy]

I wish I could but this company will not change there file layout for just 1 company.

 

[vbaInet]

So has Allan been online yet to solve your problem?

 

[JDeezy]

Not yet. I sent him some specs and test file but I am not really in a huge rush. I have been trying to figure it out myself but to no avail.

 

[vbaInet]

Alright. He's a very busy man.

You're looking to insert each record right? Not update. What is the delimeter for each field? That is what character seperates each field, a comma or a tab?

Here's a possible solution (based on Allan's idea):

Option Compare Database
Option Explicit

Dim fieldName As String, fieldLen As Integer

Function ImportFromFile(FileName As String)
    Dim db As DAO.Database, rst As DAO.Recordset
    Dim strRecord As String, arrayFields() As String, xCol As Integer, i As Integer, aIndex As Integer
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset("[COLOR=Red]TheTableToFillWithTheImportData[/COLOR]")
    
    ' Initialise the array
    ReDim arrayFields(0)
    
    Open FileName For Input As #1  ' Open the import file.
    While Not EOF(1)
        Line Input #1, strRecord
        
        If Len(strRecord & "") > 0 Then
            ' Increase the size of the array if needs be and preserve old values
            If Int(Len(strRecord) / 5) > UBound(arrayFields) + 1 Then
                ReDim Preserve arrayFields(Int(Len(strRecord) / 5) - 1)
            End If
            
            aIndex = 0
            ' Save each part of the record into the array
            For i = 1 To Len(strRecord)
                aIndex = aIndex + 1
                SetFieldVal Mid(strRecord, i, 5)
                arrayFields(aIndex) = Mid(strRecord, i, fieldLen)
                i = i + (fieldLen - 1)
            Next

            With rst
                .AddNew
                    For xCol = 0 To aIndex
                        SetFieldVal Left(arrayFields(xCol), 5)
                        If fieldName <> "" Then
                            .Fields(fieldName) = Trim(arrayFields(xCol))
                        End If
                    Next
                .Update
            End With
        End If
    Wend
    Close #1
    
    rst.Close
    Set rst = Nothing
    
    MsgBox "Successfully Imported!"

End Function

Function SetFieldVal(fieldID As String)
    Select Case fieldID
        Case "AC120"
            '-- Name Field
            fieldName = "[COLOR=Red]NameField[/COLOR]"
            fieldLen = [COLOR=Red]25[/COLOR]
       Case "AC130"
            '-- Address field
            fieldName = "[COLOR=Red]AddressField"[/COLOR]
            fieldLen = [COLOR=Red]25[/COLOR]
       Case "BC120"
            '-- Shipping Address
            fieldName = "[COLOR=Red]ShippAddField[/COLOR]"
            fieldLen = [COLOR=Red]25[/COLOR]
       Case "BC130"
            '-- Bankruptcy Info
            fieldName = "[COLOR=Red]BankField[/COLOR]"
            fieldLen = [COLOR=Red]25[/COLOR]
       Case "AF120"
            '-- Connection Status
            fieldName = "[COLOR=Red]ConnStatField[/COLOR]"
            fieldLen = [COLOR=Red]25[/COLOR]
       Case Else
            '-- Unknown field identity
            fieldName = ""
            fieldLen = 0
    End Select
End Function

Amend only the bits in red.

To call the function do something like this:

ImportFromFile("Full_Path_To_Your_File")

 

[JDeezy]

Thats where it gets tricky. The delimiter really doesnt exists because it is just spaces and inside the field there are spaces also. So, you really have to do it by field size.

 

[vbaInet]

It splits each field into 25 characters each as you mentioned earlier.

 

[JDeezy]

I was just using that as an example so I could get an idea of the coding so I could program it myself. I sent Allan a list of file specs and which ones I need to pick up. Some fields have up to 168 characters others have as few as 14.

 

[vbaInet]

Not a fixed width file then.

 

[JDeezy]

Do you want to see the sample file I sent Allan and a list of specs? It is a fixed width but it shifts when fields are missing. Doesnt really make a lot of sense unless you see it.

 

[vbaInet]

Well this is how I understand it. I'm talking length of characters here, not position

1. The width is fixed for a particular type of column. That is all Name fields would have a field size of 24, all Address fields would have a fixed size of 100 etc.

OR

2. None of the field widths are fixed. If a field begins with one of the 5 characters you mentioned then count from there until you find another 5.

 

[JDeezy]

Choice 1. Choice 2 would work except I would have to program for around 50-75 possible codes.

 

[vbaInet]

Amended the code. Have a look at it.

 

[JDeezy]

' Save each part of the record into the array
For i = 1 To Len(strRecord)
aIndex = aIndex + 1
SetFieldVal Mid(strRecord, i, 5)
arrayFields(aIndex) = Mid(strRecord, i, fieldLen)
i = i + (fieldLen - 1)
Next

The above part of the code is not working correctly. The red text is where is says "Subscript out of Range". Not sure how to resolve this since it is not an error message I have experienced before.

 

[ChrisO]

Similar to vbaInet’s code but using a Table

Setup a Table of field specifications as: -

DataName       FieldLength	DestinationFieldName
AC120		20		Field 1
AC130		20		Field 2
BC120		20		Field 3
BC130		20		Field 4
AF120		20		Field 5
XYZ		160		FRED

Line input one line.

Loop through the FieldSpecificationRecordset
InStr for DataName
If found then Trim() the following Data over the next FieldLength and save to DestinationFieldName.

Get next line.


It would be better if we had a few lines of sample data in a text file.

 

[vbaInet]

Show me the SetFieldVal function you amended.

 

[JDeezy]

Function SetFieldVal(fieldID As String)
Select Case fieldID
Case "PC01"
'-- Name Field
fieldName = "NameField"
fieldLen = 95
Case "FI01"
'-- Address field
fieldName = "AddressField"
fieldLen = 23
Case "NM01"
'-- Shipping Address
fieldName = "ShippAddField"
fieldLen = 70
Case "PR01"
'-- Bankruptcy Info
fieldName = "BankField"
fieldLen = 168
Case Else
'-- Unknown field identity
fieldName = ""
fieldLen = 0
End Select
End Function

 

[vbaInet]

Change fieldLen = 0 to 1.

 

[JDeezy]

Still the same.