8 Replies Latest reply on Nov 2, 2016 11:13 PM by SSN01

    Using Jython to transfer Excel files to txt files before import.

    H_Ww

      Hi there,

       

      My requirement is transferring the Excel format source files to flat files before I import them to fdmee.(We can not manfully save excel file as flat file due to the high volume of the source files.)

       

      What I have in mind right now is using the Jython in beforeImport event script. Is that possible? If so, what step I should do? Is there any other easier way?

       

      Thanks!

      Wendy

        • 1. Re: Using Jython to transfer Excel files to txt files before import.
          ThinkFDM

          Hi H_Ww.  Using Excel files for source files can be a bit problematic.  You sure the source systems can't get you files in flat file format?  That would be preferred.  Excel source files usually have some manual intervention along the way and I've found it challenging to guarantee a rigid, simplified format is followed, e.g. one sheet, only X columns, each used for specific type of data.

           

          But if you have to go down this path, you should be able to find some examples on the Internet.  Here's One - Jython Example: Read and Excel File.  And yes, you can add your code to the BefImport event script. 

           

          But again, this would A LOT easier if the source systems would provide you flat files.  Or if you can pull the data from the source system using SQL then you could use the open interface adapter of the new universal data adapter.

           

          Good luck.  By the way, my daughter just popped in my office.  She likes your pink bunny avatar.  She says it cute.

          • 2. Re: Using Jython to transfer Excel files to txt files before import.
            H_Ww

            Thank you ThinkFDM! I will take a look on the code.

             

            Glad to hear your daughter likes the bunny avatar, it s my fav too! Great minds think alike

            • 3. Re: Using Jython to transfer Excel files to txt files before import.
              H_Ww

              Hi ThinkFDM,

               

              I finally worked something out on this format transfer stuff. I am now able to use python to open an excel file on local -> read all the content -> copy all the data to a flat file and save it on local.

               

              However, I stuck at the file uploading and file reading parts on FDMEE.

              1.How can I save the txt file to FDMEE server as the FDMEE only can import data that on its own server?

              2.If the txt file is saved to FDMEE server successfully, how can I let the Data load rule use this txt to replace the xls file?

              xlsx.jpg

              My goal is still: I want the users upload the excel file and when they execute the data loading process, they trigger the BefImport event script and the script should convert the excel file to txt and import the txt file instead of the excel file. Unfortunately my code below does not work this way.

               

              ------Code starts here--------

              import xlrd

              workbook=xlrd.open_workbook("fdmContext["INBOXDIR"]"+"/ExcelFormatConvertTest.xlsx")

              sh=workbook.sheet_by_name("Sheet1")

              print sh.nrows

              print sh.ncols

              r=0

              c=0

              file=open("fdmContext["INBOXDIR"]"+"/ExcelFormatConverTest.txt","w") 

              for r in range(sh.nrows):

                  for c in range(sh.ncols):

                      data =sh.cell_value(r,c)+" "

                      print  data,

                      file.write(data+" ")

                  print

                  file.write("\n")

              file.close()

              ------Code Ends here--------

               

              Any idea or suggestion will be appreciate!

              Thanks,

              Wendy

              • 4. Re: Using Jython to transfer Excel files to txt files before import.
                SH_INT

                Hi Wendy,

                 

                Could you post this question in a new thread as the orinal was closed when it was marked as answered. You will get a better response as people will now there is still an outstanding question in the thread. That said why don't you just put the name of the converted file in the  data load rule source options instead of the Excel file as it will be populated before the import process accesses it

                • 5. Re: Using Jython to transfer Excel files to txt files before import.
                  Francisco Amores

                  FDMEE uses POI API for MS Documents.

                   

                  I would use the same :-)

                   

                  https://poi.apache.org/

                  • 6. Re: Using Jython to transfer Excel files to txt files before import.
                    H_Ww

                    Hi Francisco,

                     

                    Thanks for your reply!

                     

                    I was trying to find the answer for the following questions in this API link you provided:

                    1.How can I save the txt file to FDMEE server? (I thought workbook=xlrd.open_workbook("fdmContext["INBOXDIR"]"+"/ExcelFormatConvertTest.xlsx") could work but it didn't.)

                    2.If the txt file is saved to FDMEE server successfully, how can I let the Data load rule use this txt to replace the xls file?

                     

                     

                    I dont know if I searched correctly but I can find anything I need there. I am not sure if I quite understand this API but my understanding is this API is more about manipulating the content within Excel instead of the operating of the whole file?

                    I am new to this, any help would be appreciate!

                     

                    Thanks!!

                    Wendy

                    • 7. Re: Using Jython to transfer Excel files to txt files before import.
                      Francisco Amores

                      1) what error do you get?

                       

                      2) you would have to override the filepath in the database in the befimport script

                      • 8. Re: Using Jython to transfer Excel files to txt files before import.
                        SSN01

                        If the filename remain static, You can copy the .txt file (excel to .txt file) in the befimport  script to the inbox directory.

                        Save the rule file in Data load rule .