Importing from a text file

[filnigeria]

I have EDI's in the form of text files that come in periodically once a week
or something like that, and the lines are 128 bytes long and the data or
text appears like this:

(widen the screen to see it properly)


41 001961590001 000001 BIGCUBICLE
000038000000000000000000010649CONSOLIDATED CARGO
44 001MEGA VENTURES LTD. C/O BONEY MARCUS IND LTD.
ANGEL-SITE C/NO.1-200


wat i have been cracking my brain about is how to import this into access

THE CATCH

there is a EDI data format sheet i have printed that tell you were each bit
of information is

ie
1 RECORD-ID M 9(2) 1 2 41

4 Commodity code M X(9) 9 17 Left justified (9 char)
5 Filler O X(1) 18 18
6 No of packages/Containers M 9(6) 19 24
7 Filler O X(3) 25 27
8 Package in Words M X(15) 28 42 refer to Package list
9 Cargo Gross Weight O 9(8)v99 43 52
10 Cargo Nett Weight O 9(8)v99 53 62


so thus were the first two characters of a line are 41 from the 9th to the
17 character comes the commodity code and so on
i need to put each of these in there own table cos i have 15 records to go
through and each record goes in a seperate

i have got this working in excel but have no idea how to import the txt in
to access
here an example of the vba code from access (made it will help you help me)

Sub Jordan_Extraction_CAL()

Dim i As Integer
Dim j As Integer
Dim K As Integer
Dim m As Integer
Dim n As Integer

Dim BLNO As String
Dim PreVESScode As String
Dim PreVESSname As String
Dim PreVOYno As String
Dim POD As String
Dim POL As String
Dim BLcy_cfsITEMS As String
Dim BLprepaidCOLLECT As String
Dim BLtranshipID As String
Dim BLallemptyctnID As String
Dim LoadDATE As String
Dim BLnumber As String
Dim portISSUEoriginalBL As String
Dim preVOYarrDATE As String


Dim CargoWeight As Long
Dim ContainerTareWeight As Long
j = 4

For i = 1 To 5000


'Mainly for record 12
If Left((Worksheets("Sheet1").Cells(i, 1).Value), 2) = 12 Then
BLNO = Mid(Worksheets("Sheet1").Cells(i, 1).Value, 6, 17)
PreVESScode = Mid(Worksheets("Sheet1").Cells(i, 1).Value, 37, 3)
PreVESSname = Mid(Worksheets("Sheet1").Cells(i, 1).Value, 40, 20)
PreVOYno = Mid(Worksheets("Sheet1").Cells(i, 1).Value, 60, 8)
POD = Mid(Worksheets("Sheet1").Cells(i, 1).Value, 68, 5)
POL = Mid(Worksheets("Sheet1").Cells(i, 1).Value, 73, 5)
BLcy_cfsITEMS = Mid(Worksheets("Sheet1").Cells(i, 1).Value, 78, 9)
BLprepaidCOLLECT = Mid(Worksheets("Sheet1").Cells(i, 1).Value, 87, 1)
BLtranshipID = Mid(Worksheets("Sheet1").Cells(i, 1).Value, 88, 1)
BLallemptyctnID = Mid(Worksheets("Sheet1").Cells(i, 1).Value, 89, 1)
LoadDATE = Mid(Worksheets("Sheet1").Cells(i, 1).Value, 90, 8)
BLnumber = Mid(Worksheets("Sheet1").Cells(i, 1).Value, 98, 17)
portISSUEoriginalBL = Mid(Worksheets("Sheet1").Cells(i, 1).Value, 115,
5)
preVOYarrDATE = Mid(Worksheets("Sheet1").Cells(i, 1).Value, 120, 8)


'to show on the sheet
m = m + 1
Worksheets("Sheet3").Cells(m, 1).Value = BLNO
Worksheets("Sheet3").Cells(m, 2).Value = PreVESScode
Worksheets("Sheet3").Cells(m, 3).Value = PreVESSname
Worksheets("Sheet3").Cells(m, 4).Value = PreVOYno
Worksheets("Sheet3").Cells(m, 5).Value = POD
Worksheets("Sheet3").Cells(m, 6).Value = POL
Worksheets("Sheet3").Cells(m, 7).Value = BLcy_cfsITEMS
'display words
If BLprepaidCOLLECT = "P" Then
Worksheets("Sheet3").Cells(m, 8).Value = "Prepaid"
End If
If BLprepaidCOLLECT = "C" Then
Worksheets("Sheet3").Cells(m, 8).Value = "Collect"
End If
If BLprepaidCOLLECT = "C" Then
Worksheets("Sheet3").Cells(m, 8).Value = "Collect"
End If
If BLprepaidCOLLECT = "F" Then
Worksheets("Sheet3").Cells(m, 8).Value = "Foreign"
End If
'more to display
Worksheets("Sheet3").Cells(m, 9).Value = BLtranshipID
Worksheets("Sheet3").Cells(m, 10).Value = BLallemptyctnID
Worksheets("Sheet3").Cells(m, 11).Value = LoadDATE
Worksheets("Sheet3").Cells(m, 12).Value = BLnumber
Worksheets("Sheet3").Cells(m, 13).Value = portISSUEoriginalBL
Worksheets("Sheet3").Cells(m, 14).Value = preVOYarrDATE

If Len(Trim(BLnumber)) = 0 Then
BLnumber = Mid(Worksheets("Sheet1").Cells(i, 1).Value, 6, 17)
End If

End If


'Mainly for record 13
If Left((Worksheets("Sheet1").Cells(i, 1).Value), 2) = 13 Then
PORTorigin = Mid(Worksheets("Sheet1").Cells(i, 1).Value, 6, 10)
PORTdischarge = Mid(Worksheets("Sheet1").Cells(i, 1).Value, 11, 15)
finalDESTINATION_portcode = Mid(Worksheets("Sheet1").Cells(i, 1).Value,
21, 25)
finalDESTINATION_name = Mid(Worksheets("Sheet1").Cells(i, 1).Value, 26,
45)

n = n + 1
Worksheets("Sheet4").Cells(n, 1).Value = PORTorigin
Worksheets("Sheet4").Cells(n, 2).Value = PORTdischarge
Worksheets("Sheet4").Cells(n, 3).Value = finalDESTINATION_portcode
Worksheets("Sheet4").Cells(n, 4).Value = finalDESTINATION_name

End If

get the drift

can anybody body help me import txt from a text file character by character
or by a range of bytes into access tables

PLEASE HELP

Jordan

 

[Ken Snell]

Before you resort to character-by-character, first try using an Import
Specification for the text file. You can set it up as fixed-width and define
the width of each field.

Go to File | Get External Data ... and do the import manually. On the last
screen of the wizard (where it asks you the name of the table to use), click
the Advanced button in the lower left corner. There you can save all the
specifications as a named specification (Save As). Then you can use that
specification name in the TransferText method (VBA or macro) in the future
for importing these files.

Suggestion: Import the data to a temporary table, and then run an append
query to copy the data into a permanent table; then delete the data from the
temporary table for the next time.

 

[filnigeria]

This method won't work because every line is different depending on the type
of record that it is
there are 15 different types of records thus 15 different types 128 byte
line arrangements
the lines begin with a record ID this is from byte 1 to byte 2 this is wat i
used to determine the process to be exectuted on it in excel.
The character-by-character in ranges is the only way that i can figure out
how to correctly extract the information from the text file
please is there any other way that anyone knows
OR
Can anybody help me out with an example script to import text
character-by-character into a table

thanks
Jordan

 

[Ken Snell]

I'll show you some basic code that you can modify for importing one
character at a time, but also consider importing each line into a temporary
table's record, using a memo field in that record. Then you can run an
append query that parses the data and copies it correctly to your permanent
table.

This generic code to import a line of data and parse that line into one
character at a time:

Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Dim intFile As Integer, intFields As Integer, intLoc As Integer
Dim strFile As String, strLine As String
Dim varArray As Variant
Set dbs = CurrentDb()
Set rst = dbs.OpenRecordset("TableToWhichDataWillBeAppended", _
dbOpenDynaset, dbAppendOnly)
strFile = Dir("C:\MyFolderName\*.txt")
Do While strFile <> ""
intFile = FreeFile()
' open the text file in order to read the data
Open "C:\MyFolderName\" & strFile As #intFile For Input
' discard the first two lines in the text file
Line Input #intFile, strLine
Line Input #intFile, strLine
' append each record from the text file to the table
Do While EOF(intFile) = False
Line Input #intFile, strLine
For intLoc = 1 To Len(strLine)
' This will put each character into a field, one character per record --
' you can modify to concatenate characters into text strings and
' then write that string into a field -- or you can parse the data
' from the entire strLine text string using the Left, Mid, and Right
' functions as needed for each type of line of data
rst.AddNew
rst.Fields("FieldName").Value = Mid(strLine, intLoc, 1)
rst.Update
Loop
Close #intFile
strFile = Dir()
Loop
rst.Close
Set rst = Nothing
dbs.Close
Set dbs = Nothing