Table conversion - variable columns

James Monroe

Active Member
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.
 

Attachments

  • SupplierInformation_20180606_050340.txt
    6.5 KB · Views: 23
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.
 
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!
 
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.
 
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.
 
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
 
Back
Top