[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]

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]

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]

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]

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]

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]

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]

Dear Chris,

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

Thanks in advance,
Lakshman

[chrisplough]

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]

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