import a .hse file

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

need to right a script to import this hse file.
..hse is an EDI format (Xlate Flat File) designed for use over EDI networks
inside the file looks like this:
TYP 0350
SDT 5013546020497 C0094
CDT 5010011900016
FIL 4424 0001 050418
CLO 5010011090036
ORD C00940003043 050418
DIN 050420
DINAZ929901
OLD 0000000000000 95010012734563
OLDA000000000000000 000000000000006
OLDB 000000000000017
OLDCTRAD ROST JNT SMAL
OLD 0000000000000 95010012734594
OLDA000000000000000 000000000000004
OLDB 000000000000010
OLDCJS TTD ROST JNT LRG
OLD 0000000000000 95010012386878
OLDA000000000000000 000000000000004
OLDB 000000000000006
OLDCTRAD B/LESS RIB
OLD 0000000000000 95010012526434
OLDA000000000000000 000000000000006
OLDB 000000000000004
OLDCJO TTD RSTING JNTCW
OLD 5010012750331
OLDA000000000000000 000000000000004
OLDB 000000000000015
OLDCJS TWIN RSTING JNT
OLD 0000000000000 95010012593900
OLDA000000000000000 000000000000006
OLDB 000000000000033
OLDCJS ROASTING JNT SML
OLD 0000000000000 95010012593917
OLDA000000000000000 000000000000006
OLDB 000000000000011
OLDCJS BRISKET
OLD 0000000000000 95010012387219
OLDA000000000000000 000000000000004
OLDB 000000000000044
OLDCJS B/LS RIB ROST SML
OLD 0000000000000 95010012734075
OLDA000000000000000 000000000000004
OLDB 000000000000011
OLDCJS ROASTING JNT LRG
OLD 0000000000000 95010012703972
OLDA000000000000000 000000000000006
OLDB 000000000000008
OLDCJS ROLD SIRLOIN JONT
OLD 0000000000000 95010012734419
OLDA000000000000000 000000000000006
OLDB 000000000000003
OLDCJS ROAST JNT&FAT SML
OLD 0000000000000 95010012948977
OLDA000000000000000 000000000000008
OLDB 000000000000065
OLDCJS MINI ROASTING JNT
OLD 0000000000000 95010012105622
OLDA000000000000000 000000000000004
OLDB 000000000000036

can you please tell me how to transfer this data into an access table, i
have no ideia here to start..

thank you
 
You have to open the file in code and read one line at a time.
Your code needs to parse each line.
This works as long as the file is layed out the same way *each time* you
want to import it.
If it changes, then you will have a difficult time automating it.
However, you may be able to write lots of small Subs to process a given type
of line.
e.g. If the line starts with OLDB do one thing, if OLDA do another.

Here is a sample outline:

Public Sub ImportFile(strPath As String)

Dim db As Database, rs As Recordset
Dim sLine As String, sTrimmed As String
Set db = CurrentDb
Set rs = db.OpenRecordset("TableName", dbOpenTable)

Open strPath For Input As #1

'Read a single line from an open sequential file and assign it to a String
variable.
Line Input #1, sLine
'Trim the leading blanks
sTrimmed = LTrim(sLine)

Do While Not EOF(1)
'read the next line of the file
Line Input #1, sLine
sTrimmed = LTrim(sLine)

'manipulate the string if necessary, then add it to the rs table.
If rs.BOF = True Then
rs.AddNew
Else
rs.Edit
End If
rs.Update
Loop
End Sub
 
thanks joe

Joe Fallon said:
You have to open the file in code and read one line at a time.
Your code needs to parse each line.
This works as long as the file is layed out the same way *each time* you
want to import it.
If it changes, then you will have a difficult time automating it.
However, you may be able to write lots of small Subs to process a given type
of line.
e.g. If the line starts with OLDB do one thing, if OLDA do another.

Here is a sample outline:

Public Sub ImportFile(strPath As String)

Dim db As Database, rs As Recordset
Dim sLine As String, sTrimmed As String
Set db = CurrentDb
Set rs = db.OpenRecordset("TableName", dbOpenTable)

Open strPath For Input As #1

'Read a single line from an open sequential file and assign it to a String
variable.
Line Input #1, sLine
'Trim the leading blanks
sTrimmed = LTrim(sLine)

Do While Not EOF(1)
'read the next line of the file
Line Input #1, sLine
sTrimmed = LTrim(sLine)

'manipulate the string if necessary, then add it to the rs table.
If rs.BOF = True Then
rs.AddNew
Else
rs.Edit
End If
rs.Update
Loop
End Sub
Click to expand...
 
Back
Top