Import Flat File

[bbrendan]

Hi All,

I need some help regarding importing a fixed width text file.

Does anyone know how to programatically import a fixed width flat file into an MSSQL table.

Note I cannot use the docmd.transfertext, as I am using an Access Data Project ADP. As in ADP it doesnt allow the use of the access2000 Specification files


Note I need it to be in ADO

I have looked at this example but cannot get it to work in ADO.
-----------------------------------------
Dim Dbs as Database
Dim Rst as Recordset
Dim FileName

Set Dbs = CurrentDb
Set Rst=dbs.OpenRecordset"TableToAddInfo",dbOpenDynaset)

Open "C:\Temp\" & FileName & ".txt" For Input As #1
Do While Not EOF(1)
Line Input #1, LineData
Rst.AddNew
Rst!FieldName = LineData
Rst.Update
Loop
Close #1
Rst.Close

--------------------------------------------

any help would be great!

 

[Travis]

Try this:

Option Compare Database

'Define fields and Sizes
Private Type MyRecord
 sField1 As String * 10
 sField2 As String * 8
 sField3 As String * 14
End Type


Public Sub ParseFile()

    Dim Dbs As Database
    Dim Rst As Recordset
    Dim FileName As String
    Dim filenum As Integer
    Dim Record As MyRecord

        Set Dbs = CurrentDb
        Set Rst = Dbs.OpenRecordset("TableToAddInfo", dbOpenDynaset)
        
        filenum = FreeFile

        Open "C:\Temp\" & FileName & ".txt" For Random As filenum Len = Len(Record)
        Do While Not EOF(filenum)
            Get #filenum, , Record
            If Left(Record.strLine, 1) = "D" Then
                With Rst
                    .AddNew
                    .Fields(0) = Record.sField1
                    .Fields(1) = Record.sField2
                    .Fields(2) = Record.sField3
                    .Update
                End With
            End If
        Loop
        
Exit_Command:
        Close #filenum
        Set Rst = Nothing
        Me.pbFile.Value = 0
        
Exit Sub
ErrorHandler:
    MsgBox Error$
    GoTo Exit_Command

End Sub

 

[bbrendan]

hi travis,

thanks for you reply on this!

I have slightly modified your code, but get an error 91,
"object variable or with block variable not set"

any ideas??

thanks

---------------------------------------------------------------------------------
Option Compare Database

'Define fields and Sizes
Private Type MyRecord
sField1 As String * 10
sField2 As String * 8
sField3 As String * 14
End Type

Public Function ParseFile()

Dim Dbs As Database
Dim Rst As Recordset
Dim FileName As String
Dim filenum As Integer
Dim Record As MyRecord



Set Dbs = CurrentDb
Set Rst = Dbs.OpenRecordset("w_imp_CreditCardTransactions_recd", dbOpenDynaset)

filenum = FreeFile

Open "\\trillion\databases\webImport\CardTrans\in\BRIFN150.REQ" For Random As filenum Len = Len(Record)
Do While Not EOF(filenum)
Get #filenum, , Record
If Left(Record.sField1, 1) = "D" Then
With Rst
.AddNew
.Fields(0) = Record.sField1
.Fields(1) = Record.sField2
.Fields(2) = Record.sField3
.Update
End With
End If
Loop

Exit_Command:
Close #filenum
Set Rst = Nothing
'Me.pbFile.Value = 0

Exit Function
ErrorHandler:
MsgBox Error$
GoTo Exit_Command

End Function

--------------------------------

 

[Travis]

What Line are you getting the Error 91 on?

 

[bbrendan]

hi Travis,

many thanks for your initial input, you pointed me in the right direction.

Anyway, for anyone wanting to know in the future here's my code

--------------------------------------------------------------------------------

Option Compare Database

'Define fields and Sizes
Private Type MyRecord
sRecord_Processed As String * 1
sRecord_Type As String * 1
sTransaction_Type As String * 1
sContinuation As String * 1 'specifies width of 1 characters
sLive_Test As String * 1 'specifies width of 1 characters
sTransaction_Date As String * 8 'specifies width of 8 characters
sTransaction_Time As String * 6 'specifies width of 6 characters
sMerchant_ID As String * 15 'specifies width of 15 characters
sTerminal_ID As String * 8 'specifies width of 8 characters
sCapture_Method As String * 1 'specifies width of 1 characters
sCard_No As String * 19 'specifies width of 19 characters
sExpiry_Date As String * 4 'specifies width of 4 characters
sStart_Date As String * 4 'specifies width of 4 characters
sIssue_No As String * 2 'specifies width of 2 characters
sAmount As String * 12 'specifies width of 12 characters
sCash_Back_Amount As String * 12 'specifies width of 12 characters
sCard_Track_2 As String * 40 'specifies width of 40 characters
sOriginators_Trans_Reference As String * 25 'specifies width of 25 characters
sCurrency_Code As String * 3 'specifies width of 3 characters
sStore_Code As String * 1 'specifies width of 1 characters
sAuthorisation_Method As String * 1 'specifies width of 1 characters
sUser_ID As String * 8 'specifies width of 8 characters
sReserved As String * 9 'specifies width of 9 characters
sCard_Scheme_Code As String * 3 'specifies width of 3 characters
sNetwork_Terminal_No As String * 4 'specifies width of 4 characters
sTransaction_No As String * 11 'specifies width of 11 characters
sStatus As String * 1 'specifies width of 1 characters
sAuthorisation_Code As String * 8 'specifies width of 8 characters
sError_No As String * 4 'specifies width of 4 characters
sError_Authorisation_Message As String * 84 'specifies width of 84 characters
sFiller As String * 2 'specifies width of 2 characters

End Type

Public Function ParseFile()

Dim mydb2 As adodb.Recordset
Dim db As adodb.Connection
Dim rst As New adodb.Recordset
Dim strSQL As String
Dim FileName As String
Dim filenum As Integer
Dim Record As MyRecord

Set db = New adodb.Connection
Set rst = New adodb.Recordset
Set mydb2 = New adodb.Recordset

mydb2.ActiveConnection = CurrentProject.Connection
mydb2.Open "select file_name from w_imp_ccard_file"

cnn = CurrentProject.Connection
strSQL = "SELECT * FROM w_imp_CreditCardTransactions_recd"
rst.Open strSQL, cnn, adOpenDynamic, adLockOptimistic

filenum = FreeFile

Open "\\trillion\databases\webImport\CardTrans\in\" & mydb2.Fields("File_Name").Value & ".ANS" For Random As filenum Len = Len(Record)
Do While Not EOF(filenum)
Get #filenum, , Record

With rst
.AddNew
.Fields("Record_Processed") = Record.sRecord_Processed
.Fields("Record_Type") = Record.sRecord_Type
.Fields("Transaction_Type") = Record.sTransaction_Type
.Fields("Continuation") = Record.sContinuation
.Fields("Live_Test") = Record.sLive_Test
.Fields("Transaction_Date") = Record.sTransaction_Date
.Fields("Transaction_Time") = Record.sTransaction_Time
.Fields("Merchant_ID") = Record.sMerchant_ID
.Fields("Terminal_ID") = Record.sTerminal_ID
.Fields("Capture_Method") = Record.sCapture_Method
.Fields("Card_No") = Record.sCard_No
.Fields("Expiry_Date") = Record.sExpiry_Date
.Fields("Start_Date") = Record.sStart_Date
.Fields("Issue_No") = Record.sIssue_No
.Fields("Amount") = Record.sAmount
.Fields("Cash_Back_Amount") = Record.sCash_Back_Amount
.Fields("Card_Track_2") = Record.sCard_Track_2
.Fields("Originators_Trans_Reference") = Record.sOriginators_Trans_Reference
.Fields("Currency_Code") = Record.sCurrency_Code
.Fields("Store_Code") = Record.sStore_Code
.Fields("Authorisation_Method") = Record.sAuthorisation_Method
.Fields("User_ID") = Record.sUser_ID
.Fields("Reserved") = Record.sReserved
.Fields("Card_Scheme_Code") = Record.sCard_Scheme_Code
.Fields("Network_Terminal_No") = Record.sNetwork_Terminal_No
.Fields("Transaction_No") = Record.sTransaction_No
.Fields("Status") = Record.sStatus
.Fields("Authorisation_Code") = Record.sAuthorisation_Code
.Fields("Error_No") = Record.sError_No
.Fields("Error_Authorisation_Message") = Record.sError_Authorisation_Message
.Fields("Filler") = Record.sFiller
.Update
End With

Loop

Exit_Command:
Close #filenum
Set rst = Nothing
mydb2.Close


Exit Function
ErrorHandler:
MsgBox Error$
GoTo Exit_Command

End Function

-----------------------------------------------------------------------------------