F
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
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