Announcement

Collapse
No announcement yet.

[WEBINAR] Improving CSV Uploads and Multiple CSVs via ZIP

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • [WEBINAR] Improving CSV Uploads and Multiple CSVs via ZIP

    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

  • #2
    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

    Comment


    • #3
      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

      Comment


      • #4
        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
        Cheers,
        Murthy

        Comment


        • #5
          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

          Comment


          • #6
            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)
            Cheers,
            Murthy

            Comment


            • #7
              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

              Comment


              • #8
                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
                Attached Files
                Cheers,
                Murthy

                Comment


                • #9
                  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
                  Attached Files

                  Comment


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

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

                    Comment


                    • #11
                      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


                      Attached Files

                      Comment


                      • #12
                        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.
                        Attached Files

                        Comment


                        • #13
                          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
                          -------
                          Thanks and Regards
                          Anurag Saini
                          MavenWire

                          Comment


                          • #14
                            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

                            Comment


                            • #15
                              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.
                              -------
                              Thanks and Regards
                              Anurag Saini
                              MavenWire

                              Comment

                              Working...
                              X
                              😀
                              🥰
                              🤢
                              😎
                              😡
                              👍
                              👎