Results 1 to 6 of 6

Thread: Table conversion - variable columns

  1. #1

    Table conversion - variable columns

    We are on apps 9.0, tools 9.1.4.7.

    We have a request to bring data in from a third party into our JDE system. This is for new vendors but the question here is pretty generic. We do many of these type of things and have a table conversion to bring in the data into the F0911Z or F0411Z depending on the requirement.

    Typically we would have a file layout where the data is specified by position, or if its a comma delimited file then it can be mapped as needed in the JDE table conversion design aid.

    The issue is that this third party is stating that the file we will be given will be comma delimited, and have multiple formats (Header, Address, Contact, etc). That is fine but they are stating that we actually need to read the headings to know what data is in any given element. For example, for Vendor Name, they cannot say that it will always be the sixth element in the Header type record. But we should read the headings, and if the heading 'Vendor Name' is the sixth element, then the vendor name will be the sixth element in the Header record. But it could be the fifth or seventh or whatever.

    What I cannot see is how I can code this in JDE. The Table Conversion requires you to map the incoming fields but I don't see how I could do what they are asking, essentially not knowing what

    Has anyone else had this requirement, to import a flat file with multiple formats and variable columns, where you have to look at the headings to determine what element a particular value is in. Any thoughts would be appreciated.

    A sample file is attached also.
    Attached Files Attached Files

  2. #2
    Member arrozpegao's Avatar
    Join Date
    Jan 2003
    Location
    Madrid - Spain
    Posts
    72
    Just a quick idea (and perhaps, stupid but...). Why don´t you create a middle table and you fill it with all raw data with a TC and later with a report over that table you fill the final table, using event rules of the report to fit each field in the right position in you second table?

    Regards.
    --------------------------------------------------------------------
    --------------------------------------------------------------------
    B7334 SP22 SQL SERVER 2000

  3. #3
    New Member
    Join Date
    Mar 2009
    Location
    Phoenix, AZ USA
    Posts
    44
    Adding to arrozpegao's idea, JDE already has a pair of generic text tables that might be ideal as a middle table - F007101 (batch header) and F007111 (detail). Just dump your third party data into the 1500 character text field in F007111 with a simple TC. Then the hard part will be to rearrange the data into the correct column order. Hopefully they will be consistent with the column names in the header record!
    Regards,
    Kim Schmidt

    - E1 9.1.4 ,TR 9.2.2 , iSeries, DB2
    - formerly Xe, 8.0, SQL Server, Oracle

  4. #4
    Senior Member Larry_Jones's Avatar
    Join Date
    Nov 2000
    Location
    Spokane, WA, USA
    Posts
    3,223
    A Oracle HCM Reportwriter sends us a CSV file that behaves similarly in that the columns sometimes move around (gotta love that!).
    I dealt with it by writing a VBScript program that basically creates a map based on the column headings and populates variables based on the data map.
    Something like a JDE 'Z' database table is then populated / written to.
    It was a bit tedious to setup but at least its not sensitive to the vagaries of the report generator.

    I'm betting you could do something in a regular UBE to handle this but ... it would be really really tedious to do it in ER.
    Larry Jones
    E1 9.2 - TR 9.2.2.6 on Win 2016 R2. SQL Server 2016
    Wintel, BI Publisher

  5. #5
    New Member
    Join Date
    Mar 2009
    Location
    Phoenix, AZ USA
    Posts
    44
    Another thought: If your process is not running as a scheduled batch job, AND if your 3rd party uses consistent column names, you can import the CSV into Microsoft Access if you tell Access the first data row has column names. Your landing table should have its columns named the same way as your 3rd party. Depending on your JDE database, you might be able to define your JDE Z table as a linked table and use an Access insert query to populate it after the columns are arranged correctly.
    Regards,
    Kim Schmidt

    - E1 9.1.4 ,TR 9.2.2 , iSeries, DB2
    - formerly Xe, 8.0, SQL Server, Oracle

  6. #6
    Senior Member Chan Rana's Avatar
    Join Date
    Aug 2005
    Location
    PA,USA
    Posts
    1,022
    If you dont want to go out of JDE then you can read csv file line by line and keep on decoding the header tags and mark the positions to read the fields further down. This will not require TC.

    This solution does require that they don't change tags names.

    Chan
    Chandrakant Rana
    E1 Xe-9.0, AS/400, XML, BSSV
    E1 Integration,C,HTML,Java,CreateForm 3.0

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
The legal restrictions and terms of use applicable to this site are available here.
Use of this site signifies your agreement to the terms of use.
JDELIST is NOT affiliated with JD Edwards® & Company, Oracle or Peoplesoft. Contents of this site are neither endorsed nor approved by JD Edwards® & Company and, or Oracle.