Fixed Format by Starting Characters (1 Viewer)
- Thread starter JDeezy
- Start date
Also JDeezy (this might also interest you too Allan), if "TU4R" is 62, the next 4 characters after that (i.e. after the first 62 chars) is "PH01". Should that not be part of the spec?
So it seems that one should be searching for these identifiers in the string and counting off their segment lengths?
Glad you're having fun Allan
So it seems that one should be searching for these identifiers in the string and counting off their segment lengths?
Glad you're having fun Allan
Ahhh!! So just the fields marked with an asterisk are the fields to be imported. So my thought about searching within the each input line for that ID was correct. Thanks. I'm just playing around with it for the sake of it
I know you will have solution soon.This seems to work for me:
Only ran two tests It could certainly be optimised.
What would make this even more reuseable is if you create a table that will contain all your import specs with four fields - ID (PK auto), SpecID, SegLen and SpecImport (Yes/No) field. From that we can run to check which spec needs to be imported based on Yes/No field.
Code:
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, pos As Integer
Dim recSpec() As String, containsData As Boolean
ReDim [COLOR=Red]recSpec(6)
recSpec(0) = "CP01"
recSpec(1) = "FI01"
recSpec(2) = "NM01"
recSpec(3) = "PR01"
recSpec(4) = "SC01"
recSpec(5) = "SH06"
recSpec(6) = "TU4R"[/COLOR]
Set db = CurrentDb
Set rst = db.OpenRecordset("[COLOR=Red]tblImport[/COLOR]")
' Initialise the array to store the fields
ReDim arrayFields(UBound(recSpec))
Open FileName For Input As #1 ' Open the import file.
While Not EOF(1)
Line Input #1, strRecord
If Len(strRecord & "") > 0 Then
' Save each part of the record into the array
For i = 0 To UBound(recSpec)
SetFieldVal recSpec(i)
pos = InStr(1, strRecord, recSpec(i), vbBinaryCompare)
If pos > 0 Then
arrayFields(i) = Mid(strRecord, pos, fieldLen)
containsData = True
End If
Next
' Create new record and insert data if at least one of the fields contain data.
If containsData = True Then
With rst
.AddNew
For xCol = 0 To UBound(recSpec)
If Len(recSpec(xCol) & "") > 0 Then
SetFieldVal recSpec(xCol)
.Fields(fieldName) = arrayFields(xCol)
End If
Next
.Update
End With
containsData = False
End If
End If
Wend
Close #1
rst.Close
Set rst = Nothing
MsgBox "Successfully Imported!"
End Function
[COLOR=Red]Function SetFieldVal(fieldID As String)[/COLOR]
Select Case fieldID
Case "CP01"
'-- Name Field
fieldName = "Field1"
fieldLen = 95
Case "FI01"
'-- Address field
fieldName = "Field2"
fieldLen = 23
Case "NM01"
'-- Shipping Address
fieldName = "Field3"
fieldLen = 70
Case "PR01"
'-- Bankruptcy Info
fieldName = "Field4"
fieldLen = 168
Case "SC01"
'-- Connection Status
fieldName = "Field5"
fieldLen = 34
Case "SH06"
'-- Connection Status
fieldName = "Field6"
fieldLen = 14
Case "TU4R"
'-- Unknown field identity
fieldName = "Field7"
fieldLen = 62
End Select
[COLOR=Red]End Function[/COLOR]What would make this even more reuseable is if you create a table that will contain all your import specs with four fields - ID (PK auto), SpecID, SegLen and SpecImport (Yes/No) field. From that we can run to check which spec needs to be imported based on Yes/No field.
And here's a universal one based on my previous recommendations and Chris' db All you do is tick the spec IDs for importing and enter their corresponding field names. You would need to include the "Microsoft DAO 3.n Object library".
To call the function you do this:
ImportFromFixed "Full_Path_To_File.txt"
All you do is tick the spec IDs for importing and enter their corresponding field names. You would need to include the "Microsoft DAO 3.n Object library".To call the function you do this:
ImportFromFixed "Full_Path_To_File.txt"