[WEBINAR] Improving CSV Uploads and Multiple CSVs via ZIP

[chrisplough]

Hello!

Last year I gave a webinar to the OTM SIG regarding ways to leverage and improve the CSV upload process in OTM, including how to package multiple CSV files into a single ZIP file and have OTM load them in the correct order. I've decided to share these documents here on the forums. Please let me know if you have any questions.

Presentation: Webinar - Improving / Leveraging CSV Uploads in OTM
MavenWire_ImprovingCSVUploads_Webinar.pdf

Document: Loading Multiple CSVs Via ZIP
MavenWire_LoadingMultipleCSVsViaZIP.pdf

Reference Files for Loading Multiple CSVs Via ZIP
mw_RateRecordInsert.zip
mw_RateRecordInsert.bg.zip

Hope this helps!

--Chris

[jeff.depaul]

Re: Webinar: Improving CSV Uploads and Multiple CSVs via ZIP

Hi Chris,
Thanks for the info on uploading zipped .csv files - looks very similar to what is in the Data Management guide.

One thing I would like to know or see if possible is executing the CSVUtil from the command line (on the app server) while processing the .bg.zip file? I am able to run single data file loads (even multiple tables using a recursive shell script I wrote) with "glog.database.admin.CSVUtil" from the command line but would like to know if I can leverage the ".bg.zip" ext from the command line.

I could not find this info in the guide and your documentation also indicates to run from the Web interface. The conversion effort is more of an IT function here and we are processing large amounts of ASCII data (rates). This introduces .xls limitations and issues with the web timeouts, etc. Can you tell me if the above can be done?

Thanks,
Jeff

[chrisplough]

Re: Webinar: Improving CSV Uploads and Multiple CSVs via ZIP

Jeff,

Unfortunately, not that I'm aware of. I understand your need and the ability to script these CSV ZIP files from the command line would indeed help the process.

Another OTM client (Dupont) also had a significant amount of pain loading their rate records in (millions of them) and several others are experiencing issues with managing and updating their rates.

I wish I had better news for you I can say that we're currently developing a Rate Management tool in collaboration with our partner (EII), which is intended to solve this very problem. We will use it to decrease implementation times, but also (more significantly) to help clients with massive amounts of rates or rate updates manage the process effectively. The tool handles the reformatting, validation and loading (directly into OTM) of rates.

Thanks,
Chris

[MURTHYKO]

Re: [WEBINAR] Improving CSV Uploads and Multiple CSVs via ZIP

Hi Chris,

I down loaded the zip file you attached above and edited the csv to use my domain in place of domain you used in csv. and tried to upload the zip. It gave an error that "java.io.FileNotFoundException: /U04/opt/otm/temp/upload/mw_RateRecordInsert.zip.1202470455031/mw_RateRecordInsert/csvutil.ctl (No such file or directory)"
I am using OTM 5.5 CU#3 version. Could you pls let me know what is the reason and how can I resolve this?

Thanks in advance,
Lakshman

[chrisplough]

Re: [WEBINAR] Improving CSV Uploads and Multiple CSVs via ZIP

Hello - it sounds like there may be a problem with the zip file that you're uploading. Can you verify that the csvutil.ctl file exists within the zip file? Also, you can check your OTM server and see if that directory and associated files exist - to ensure that OTM is extracting the zip file correctly:

/U04/opt/otm/temp/upload/mw_RateRecordInsert.zip.1202470455031/mw_RateRecordInsert/csvutil.ctl

/U04/opt/otm/temp/upload/mw_RateRecordInsert.zip.1202470455031/mw_RateRecordInsert/*


Thanks,
Chris

[MURTHYKO]

Re: [WEBINAR] Improving CSV Uploads and Multiple CSVs via ZIP

Dear Chris,

I guess the problem is with extraction of zip file. Because I prepared another set of csv for creating a location record with the follwing zip file. The zip file contain all csv and and the "ctl" file. In the file I mentioned the order in which OTM has to load the csv files. But it is not loading.

Every time OTM throwing an error that the first file in "zip" file is not there. For Ex: in case of attached "Run1.zip" it is showing that:

java.io.FileNotFoundException: /U04/opt/otm/temp/upload/Run1.zip.1202711603035/Run1/ACTIVITY_CALENDAR.csv (No such file or directory)

java.io.FileNotFoundException: /U04/opt/otm/temp/upload/Run1.zip.1202711603035/Run1/ACTIVITY_CALENDAR.csv (No such file or directory)
at java.io.FileOutputStream.open(Ljava.lang.StringV(Native Method)
at java.io.FileOutputStream.<init>(FileOutputStream.j ava:179)
at java.io.FileOutputStream.<init>(FileOutputStream.j ava:131)
at glog.util.zip.UnZip.unZip(UnZip.java:29)
at glog.database.admin.UnZipProcessor.<init>(UnZipPro cessor.java:130)
at glog.integration.servlet.TransmissionReceiver.proc ess(TransmissionReceiver.java:225)
at glog.webserver.util.BaseServlet.service(BaseServle t.java:614)
at javax.servlet.http.HttpServlet.service(HttpServlet .java:802)
at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:252)
at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:173)
at glog.webserver.screenlayout.ClientSessionTracker.d oFilter(ClientSessionTracker.java:54)
at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:173)
at glog.webserver.util.SetCharacterEncodingFilter.doF ilter(SetCharacterEncodingFilter.java:44)
at org.apache.catalina.core.ApplicationFilterChain.in ternalDoFilter(ApplicationFilterChain.java:202)
at org.apache.catalina.core.ApplicationFilterChain.do Filter(ApplicationFilterChain.java:173)
at org.apache.catalina.core.StandardWrapperValve.invo ke(StandardWrapperValve.java:213)
at org.apache.catalina.core.StandardContextValve.invo ke(StandardContextValve.java:17
at org.apache.catalina.core.StandardHostValve.invoke( StandardHostValve.java:126)
at org.apache.catalina.valves.ErrorReportValve.invoke (ErrorReportValve.java:105)
at org.apache.catalina.core.StandardEngineValve.invok e(StandardEngineValve.java:107)
at org.apache.catalina.connector.CoyoteAdapter.servic e(CoyoteAdapter.java:14
at org.apache.jk.server.JkCoyoteHandler.invoke(JkCoyo teHandler.java:199)
at org.apache.jk.common.HandlerRequest.invoke(Handler Request.java:282)
at org.apache.jk.common.ChannelSocket.invoke(ChannelS ocket.java:754)

[chrisplough]

Re: [WEBINAR] Improving CSV Uploads and Multiple CSVs via ZIP

Please post up your zip file, and I'll take a look at it, to see if I can figure out what's going on.

--Chris

[MURTHYKO]

Re: [WEBINAR] Improving CSV Uploads and Multiple CSVs via ZIP

Dear Chris,

Zip file attached. Pls update me where i am going wrong.

Thanks in advance,
Lakshman

[chrisplough]

Re: [WEBINAR] Improving CSV Uploads and Multiple CSVs via ZIP

Hello Lakshman,

Try this file instead. The problem is that your zip file has an embedded directory (Run1) and all of the files need to be in the root of the zip file. All I've done is repackaged your files in this way.

Hope this helps!

--Chris

[MURTHYKO]

Re: [WEBINAR] Improving CSV Uploads and Multiple CSVs via ZIP

Dear Chris,
It worked. Thanks a lot.
Cheers,
Lakshman

[kumar]

Re: [WEBINAR] Improving CSV Uploads and Multiple CSVs via ZIP

Hi chris,

We need to upload Location and Location_corporation csv's , when we try to upload this we get following Exception java.io.FileNotFoundException: /u00/otmv55/temp/upload/Run4-fixed.zip.1239118993122/Run4-fixed/CALENDAR.csv (No such file or directory)

But, if we include all the location related csv's as mentioned in your Zip file then it is working fine.

we have attached our zip file.Kindly check if any modification has to be done.

Regards,
Kumar

[dv3]

Re: [WEBINAR] Improving CSV Uploads and Multiple CSVs via ZIP

Hi Chris,

It was a great document for CSVUTIL, but I am also geeting the same error as Laskhman.


I tried the same way it was mentioned but I am not able to fidn the proper solution.
I just want to create contact.


Can you please help me on this. I have attached the required zip file.

[Anurag_Saini]

Re: [WEBINAR] Improving CSV Uploads and Multiple CSVs via ZIP

The problem is the name of your files.

The file names are case sensitive hence your actually filename is

CONTACT.csv and CONTACT_COM_METHOD.csv

But your ctlutil.ctl file the filenames are CONTACT.CSV and CONTACT_COM_METHOD.CSV so either change your file extension to be in CAPS or change CTLUTIL file to have lower case like
CONTACT.csv and CONTACT_COM_METHOD.csv

[dv3]

Re: [WEBINAR] Improving CSV Uploads and Multiple CSVs via ZIP

Hi Anurag,

Thank you for your help. Now it is not giving the error which I have mentioned.

But once I try to upload the csv it says to save result file. In result file log file contains the following error message.

((Time=2015/05/07 11:03:31)(Message=Processing: /u01/oracle/otm/temp/upload/CONTACT.zip))
((Time=2015/05/07 11:03:31)(Message=Processing Control File Line: -dataFileName CONTACT.csv –command iu))
((Time=2015/05/07 11:03:31)(Message=ERROR: The line: <-dataFileName CONTACT.csv –command iu> is missing the -command option))
((Time=2015/05/07 11:03:31)(Message=Processing Control File Line: -dataFileName CONTACT_COM_METHOD.csv –command iu))
((Time=2015/05/07 11:03:31)(Message=ERROR: The line: <-dataFileName CONTACT_COM_METHOD.csv –command iu> is missing the -command option))

please suggest

[Anurag_Saini]

Re: [WEBINAR] Improving CSV Uploads and Multiple CSVs via ZIP

Your hyphen ("-") before command text is Microsoft Word Special Character which is a bit longer in size "–" and unrecognized by OTM

Look at below, you should be using the smaller one in Notepad

-
–

Not the longer one copied from Word file.