CSV to XML Order Upload

[adamwcarpenter]

I am trying to create an order template in CSV format that will be converted to XML to upload orders for our customers that do not have EDI capability. They will send us their orders on this template, and we will convert to xml and upload.

Has anyone had any experience doing this, or possibly have an example of what it would look like, suggestions? Our Oracle consultants did not have any examples or specific guidance on how to accomplish.

Regards,
Adam Carpenter

[josephliang]

Re: CSV to XML Order Upload

I have 2 options in mind (used to be 3, PythonTransformerServlet)

1) The standard/common way - using integration server to transform CSV to XML and send it to OTM

2) The primitive way - using python and ClientUtil
You may refer to samples under [OTM]/utils/integration/python folder.
SampleOrderFlatFile.txt
SampleOrderTransform.py

[adamwcarpenter]

Re: CSV to XML Order Upload

Joseph, great suggestions, I will look into that, appreciate the help.

[LourensGlog]

Re: CSV to XML Order Upload

Adam,
For customers that do no have EDI, I would recommend giving them access to OTM to enter their orders manually in the UI.
Many reasons may exist why you don't want this, but as many other solutions have been used in the past to this end: order management web-portals integrated to OTM, Excel templates, ...
Lourens

[vsevs]

Re: CSV to XML Order Upload

Can anyone please give me the process of creating orders using thr python scripts?

Thanks,
Venkat.

[jacekalbert]

Re: CSV to XML Order Upload

Here is sample code for conversion CSV to XML

This can be used to create OTM XML

You do not need advanced software for 20K euro to do transformation.

' Create the text file. Dim csvString As String = "GREAL,Great Lakes Food Market,Howard Snyder,Marketing Manager,(503) 555-7555,2732 Baker Blvd.,Eugene,OR,97403,USA" & vbCrLf & _ "HUNGC,Hungry Coyote Import Store,Yoshi Latimer,Sales Representative,(503) 555-6874,City Center Plaza 516 Main St.,Elgin,OR,97827,USA" & vbCrLf & _ "LAZYK,Lazy K Kountry Store,John Steel,Marketing Manager,(509) 555-7969,12 Orchestra Terrace,Walla Walla,WA,99362,USA" & vbCrLf & _ "LETSS,Let's Stop N Shop,Jaime Yorres,Owner,(415) 555-5938,87 Polk St. Suite 5,San Francisco,CA,94117,USA" File.WriteAllText("cust.csv", csvString)


'here is VBA code just make it by your self just adjust to OTM XML
' Read into an array of strings. Dim source As String() = File.ReadAllLines("cust.csv") Dim cust As XElement = _ <Root> <%= From strs In source _ Let fields = Split(strs, ",") _ Select _ <Customer CustomerID=<%= fields(0) %>> <CompanyName><%= fields(1) %></CompanyName> <ContactName><%= fields(2) %></ContactName> <ContactTitle><%= fields(3) %></ContactTitle> <Phone><%= fields(4) %></Phone> <FullAddress> <Address><%= fields(5) %></Address> <City><%= fields(6) %></City> <Region><%= fields(7) %></Region> <PostalCode><%= fields( %></PostalCode> <Country><%= fields(9) %></Country> </FullAddress> </Customer> _ %> </Root> Console.WriteLine(cust)

XML result:

<Root> <Customer CustomerID="GREAL"> <CompanyName>Great Lakes Food Market</CompanyName> <ContactName>Howard Snyder</ContactName> <ContactTitle>Marketing Manager</ContactTitle> <Phone>(503) 555-7555</Phone> <FullAddress> <Address>2732 Baker Blvd.</Address> <City>Eugene</City> <Region>OR</Region> <PostalCode>97403</PostalCode> <Country>USA</Country> </FullAddress> </Customer> <Customer CustomerID="HUNGC"> <CompanyName>Hungry Coyote Import Store</CompanyName> <ContactName>Yoshi Latimer</ContactName> <ContactTitle>Sales Representative</ContactTitle> <Phone>(503) 555-6874</Phone> <FullAddress> <Address>City Center Plaza 516 Main St.</Address> <City>Elgin</City> <Region>OR</Region> <PostalCode>97827</PostalCode> <Country>USA</Country> </FullAddress> </Customer> <Customer CustomerID="LAZYK"> <CompanyName>Lazy K Kountry Store</CompanyName> <ContactName>John Steel</ContactName> <ContactTitle>Marketing Manager</ContactTitle> <Phone>(509) 555-7969</Phone> <FullAddress> <Address>12 Orchestra Terrace</Address> <City>Walla Walla</City> <Region>WA</Region> <PostalCode>99362</PostalCode> <Country>USA</Country> </FullAddress> </Customer> <Customer CustomerID="LETSS"> <CompanyName>Let's Stop N Shop</CompanyName> <ContactName>Jaime Yorres</ContactName> <ContactTitle>Owner</ContactTitle> <Phone>(415) 555-5938</Phone> <FullAddress> <Address>87 Polk St. Suite 5</Address> <City>San Francisco</City> <Region>CA</Region> <PostalCode>94117</PostalCode> <Country>USA</Country> </FullAddress> </Customer> </Root>

[harishpv]

Re: CSV to XML Order Upload

Hi,

I suggest you to try the below solution to convert csv to xml.

1. Create a custom table based on fields in csv template with extra field to indicate if record is processed.

2. Load data into custom table from csv

3. Create a package which generates xml using XMLELEMENT AND XMLFOREST functions as shown below
and then process the generated xml in otm by calling package glogowner.pkg_integration_util.insert_transmission


Sample code as shown below.

Select XMLCONCAT((XMLELEMENT ("Transmission",
(XMLELEMENT ("TransmissionHeader" ,XMLFOREST ( 'DBA.ADMIN' as "UserName", '123' as "Password" , 'Y' as "IsProcessInSequence" ,'Y' as "StopProcessOnError"))),
(XMLELEMENT ("TransmissionBody", XMLELEMENT ("GLogXMLElement
--
---
--
(XMLFOREST ('IU' as "TransactionCode"))))
)))))))
into p_ob_xml
from custom_schema/glogowner.custom_table where processed = 'N'

Call glogowner.pkg_integration_util.insert_transmission with necessary parameters to process generated xml in OTM.

Update processed field to value Y for the record.


Thanks,
Harish