Results 1 to 5 of 5
  1. #1
    rhaner is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Location
    Clackamas, Oregon
    Posts
    2

    Import EDI file to Access

    I am trying to import a EDI file from a customer and it is not working the way I need it to. First Access does not allow you to specify what the end of record symbol is so it make a whole bunch of records. Second each record may have multiple sub records. This is a MRP record with a record for each part and different quantities and ship dates.

    If anyne has any ideas how to accomplish this I would appreciate it - I have attached a file for reference

    Richard
    Quality Manager


    CoSource USA

    830-A05025072.zip

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    What is an EDI file? If the import wizard doesn't handle that file type and if it isn't a consistent structure text file (your file opens in Notepad but as one long string of characters, Word handles it better) and data needs to be separated to multiple tables then TransferText method won't work. VBA to open file and read each line is only way. What is 'MRP'?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    ssanfu is offline Master of Nothing
    Windows XP Access 2000
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    If I understand right, you get data in and EDI file (Electronic data interchange). The the end of record symbol (in this case) is the paragraph marker.
    You can open the .edi file in Word and do a replace - replace the paragraph marker with a manual line break. In VBA, replace the paragraph marker with a CRLF.

    You also have to know the structure of the .edi file. I do not see any separators - tabs, commas, etc. to show the start/end of a field.
    Using VBA, you can read each character until you get to the paragraph marker. Then process the line, breaking it up into the fields you need. Then read more characters until you get to the next paragraph marker.

    I did the replace to see what the structure/data looked like:
    Code:
    ISA`00`FTL DATA  `00`FTL DATA  `ZZ`FTL  MFG       `ZZ`HE10 MFG       `121124`1725`U`00401`000001285`0`P`>
    GS`PO`FTL  MFG`HE10 MFG`121124`1725`1260`X`002000
    ST`830`000020932
    BFR`05``000199`SH`A`121124`130523`121124```A05025072
    N1`BY`FTL
    N1`SE`HE10
    LIN`001`IN`87330-3438`EC`-
    UNT`EA
    J2X``F`JUNCTION BOX ASSEMBLY W/ COVER
    N1`ST`TMP`92`001
    SDP`A`A
    FST`1`C`D`121206
    ATH`FI`121221
    SHP`02`47`051`060131``121124
    SHP`01`4`050`110930
    LIN`002`IN`87330-3438`EC`-
    UNT`EA
    J2X``F`JUNCTION BOX ASSEMBLY W/ COVER
    N1`ST`MTH`92`017
    SDP`A`A
    FST`0`C`D`121124
    ATH`FI`121221
    SHP`02`1`051`060131``121124
    SHP`01`1`050`091215
    LIN`003`IN`87330-3438`EC`-
    UNT`EA
    J2X``F`JUNCTION BOX ASSEMBLY W/ COVER
    N1`ST`UTR`92`058
    SDP`A`A
    FST`4`C`D`121107
    FST`5`C`D`121108
    FST`6`C`D`121115
    ATH`FI`121221
    SHP`02`3257`051`060131``121124
    SHP`01`4`ZZ3`060131``121124
    SHP`01`9`050`121123
    LIN`004`IN`87330-3438`EC`-
    UNT`EA
    J2X``F`JUNCTION BOX ASSEMBLY W/ COVER
    N1`ST`SAN`92`065
    SDP`A`A
    FST`0`C`D`121124
    ATH`FI`121226
    SHP`02`56`051`060131``121124
    SHP`01`5`050`100428
    CTT`4`16
    SE`45`000020932
    GE`1`1260
    IEA`1`000001285
    But I can't break it up into fields. We need more info.....

  4. #4
    rhaner is offline Novice
    Windows 7 32bit Access 2007
    Join Date
    Nov 2012
    Location
    Clackamas, Oregon
    Posts
    2

    More Info

    [QUOTE=ssanfu;148962]If I understand right, you get data in and EDI file (Electronic data interchange). The the end of record symbol (in this case) is the paragraph marker.
    You can open the .edi file in Word and do a replace - replace the paragraph marker with a manual line break. In VBA, replace the paragraph marker with a CRLF.

    You also have to know the structure of the .edi file. I do not see any separators - tabs, commas, etc. to show the start/end of a field.
    Using VBA, you can read each character until you get to the paragraph marker. Then process the line, breaking it up into the fields you need. Then read more characters until you get to the next paragraph marker.

    I did the replace to see what the structure/data looked like:
    Code:
    ISA`00`FTL DATA  `00`FTL DATA  `ZZ`FTL  MFG       `ZZ`HE10 MFG       `121124`1725`U`00401`000001285`0`P`>
    GS`PO`FTL  MFG`HE10 MFG`121124`1725`1260`X`002000
    ST`830`000020932
    BFR`05``000199`SH`A`121124`130523`121124```A05025072
    N1`BY`FTL
    N1`SE`HE10
    LIN`001`IN`87330-3438`EC`-
    UNT`EA
    J2X``F`JUNCTION BOX ASSEMBLY W/ COVER
    N1`ST`TMP`92`001
    SDP`A`A
    FST`1`C`D`121206
    ATH`FI`121221
    SHP`02`47`051`060131``121124
    SHP`01`4`050`110930
    LIN`002`IN`87330-3438`EC`-
    UNT`EA
    J2X``F`JUNCTION BOX ASSEMBLY W/ COVER
    N1`ST`MTH`92`017
    SDP`A`A
    FST`0`C`D`121124
    ATH`FI`121221
    SHP`02`1`051`060131``121124
    SHP`01`1`050`091215
    LIN`003`IN`87330-3438`EC`-
    UNT`EA
    J2X``F`JUNCTION BOX ASSEMBLY W/ COVER
    N1`ST`UTR`92`058
    SDP`A`A
    FST`4`C`D`121107
    FST`5`C`D`121108
    FST`6`C`D`121115
    ATH`FI`121221
    SHP`02`3257`051`060131``121124
    SHP`01`4`ZZ3`060131``121124
    SHP`01`9`050`121123
    LIN`004`IN`87330-3438`EC`-
    UNT`EA
    J2X``F`JUNCTION BOX ASSEMBLY W/ COVER
    N1`ST`SAN`92`065
    SDP`A`A
    FST`0`C`D`121124
    ATH`FI`121226
    SHP`02`56`051`060131``121124
    SHP`01`5`050`100428
    CTT`4`16
    SE`45`000020932
    GE`1`1260
    IEA`1`000001285
    But I can't break it up into fields. We need more info.....


    I have attached a screen shot of the webpage before it is exported into an edi file...
    Click image for larger version. 

Name:	FTL_screen.jpg 
Views:	8 
Size:	123.5 KB 
ID:	10181

  5. #5
    June7's Avatar
    June7 is online now VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,825
    That image is not helpful. Need a 'map' that relates the info in the EDI to tables. Provide your table structure. Which values from the EDI go into which fields of which table? For instance, lines that begin with FST appear to have 4 parts. What does FST signify? What are the 4 parts? The lines that begin with SHP are inconsistent in structure. Lines beginning with ATH look like maybe initials and a date for authorization and are duplicative.

    Import of text file is common topic.
    This thread demonstrates import of a very simple text file https://www.accessforums.net/import-...nto-23674.html
    More
    https://www.accessforums.net/program...ion-23820.html
    http://vbadud.blogspot.com/2007/06/v...h-leading.html
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Import KML File Into Access
    By JonMulder in forum Import/Export Data
    Replies: 2
    Last Post: 05-18-2012, 11:06 AM
  2. Import txt file into Access Table
    By redbull in forum Import/Export Data
    Replies: 3
    Last Post: 05-02-2012, 09:32 AM
  3. Replies: 2
    Last Post: 01-26-2012, 12:19 PM
  4. Replies: 5
    Last Post: 10-28-2011, 12:20 PM
  5. How to import a .dta file into Access 2010?
    By Louie in forum Import/Export Data
    Replies: 4
    Last Post: 07-26-2011, 06:14 PM

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums