Test Data Manager

Expand all | Collapse all

Append data from multiple excels into a single excel spreadsheet using Javelin

Jump to Best Answer
  • 1.  Append data from multiple excels into a single excel spreadsheet using Javelin

    Posted 07-02-2020 05:48 PM
    Hi,

    I have a requirement where I would like to create a Javelin workflow which must be able to read each spreadsheet in specific folder and write the entire set of records from each spreadsheet onto an output Excel say Output.xlsx in a single worksheet. As a result, we should get a consolidated dataset from all the excels onto a single Output.xlsx file under 1 worksheet. Below is the workflow which I have created however it does not write anything to the output file. Not sure what is wrong with this workflow. Also could you please tell me how I can read the entire excel records as datatable and write it as datatable using the FileActivity - Append component?


    ------------------------------
    Thanks,
    Prasad
    ------------------------------


  • 2.  RE: Append data from multiple excels into a single excel spreadsheet using Javelin

    Posted 07-07-2020 09:38 AM
    Hi @Prasad Raghu,

    I'll take a look at this today, however, I would recommend Powershell as a more straight-forward method of performing this operation.  It appears there are numerous samples in a quick Internet search.   You can always use the Javelin invoke-process command to call the Powershell script if you need it to run under Javelin/TDM.

    Scott



  • 3.  RE: Append data from multiple excels into a single excel spreadsheet using Javelin

    Posted 07-08-2020 03:42 PM
    Hi Scott,

    Thank you for your response. I recently found that the latest version of Javelin is capable of handling append functionality of excel. I am in the process of getting the Javelin upgraded in my customer environment. Once I have it installed, I shall try to implement the workflow and let you know if I face any issues.

    ------------------------------
    Thanks,
    Prasad
    ------------------------------



  • 4.  RE: Append data from multiple excels into a single excel spreadsheet using Javelin
    Best Answer

    Posted 07-09-2020 09:36 AM
    There are two ways how to control decryption.
    Either the decryption is carried out at the variable level OR in some of the activity levels. 

    I would suggest for you to change the configuration entry 
        <add key="EnableGlobalDecrypt" value="False" />
    and set to 
        <add key="EnableGlobalDecrypt" value="True" />

    This entry can be found  in Javelin.exe.Config and JavelinExecutor.exe.config

    Regards, Dahman






  • 5.  RE: Append data from multiple excels into a single excel spreadsheet using Javelin

    Posted 28 days ago
    Edited by Gene HOWARD 28 days ago

    Hi all,
    I have attached a working flow that will read through a directory for excel files that have the same layout and reads them into a dataset.
    it will then export the dataset to a new excel file on a single sheet.
    Hope this helps.

    You will want to make sure you are using the latest version of Javelin 4.8.133 or newer for this.
    ftp://tdmpuser:mjki7ujm@ftp.ca.com/TDM/Javelin/Javelin-4.8.133.0.zip



    ------------------------------
    Gene Howard
    Principal Support Engineer
    Broadcom
    ------------------------------



  • 6.  RE: Append data from multiple excels into a single excel spreadsheet using Javelin

    Posted 28 days ago
      |   view attached
    Not sure why the upload did not work in the last post. But it is here in zip file

    ------------------------------
    Gene Howard
    Principal Support Engineer
    Broadcom
    ------------------------------

    Attachment(s)



  • 7.  RE: Append data from multiple excels into a single excel spreadsheet using Javelin

    Posted 28 days ago
    Hi Gene,

    Thanks for working on my problem statement and providing a solution. I tried to view the workflow however its not loading a component which is present inside "Import Each Spreadsheet into a single Datatable" component as shown in the image it may be probably due to older version of Javelin that I have. I shall retry once I upgrade to the latest version. Appreciate your support!


    ------------------------------
    Thanks,
    Prasad
    ------------------------------



  • 8.  RE: Append data from multiple excels into a single excel spreadsheet using Javelin

    Posted 12 days ago
      |   view attached
    Hi Gene,

    I was finally able to get the latest version of Javelin installed in client's network. I tried your workflow and I am getting an error "Cannot convert null object" as shown below. I believe this is due to empty cells for certain columns in the input spreadsheet. As part of the requirement we are expected to get spreadsheets which contains empty cells for certain rows and columns. Could you please tell me how to handle empty cells while reading into Datatable in READEXCELACTIVITY component? I have attached the error log as well. Please advise..


    ------------------------------
    Thanks,
    Prasad
    ------------------------------

    Attachment(s)

    txt
    Error log.txt   1K 1 version


  • 9.  RE: Append data from multiple excels into a single excel spreadsheet using Javelin

    Posted 11 days ago
    Can you provide a sam[le excel spreadsheet for testing?

    ------------------------------
    Gene Howard
    Principal Support Engineer
    Broadcom
    ------------------------------



  • 10.  RE: Append data from multiple excels into a single excel spreadsheet using Javelin

    Posted 11 days ago
      |   view attached

    Hi Gene,

    Please find some sample files which has dummy values. Could you please test your workflow using these files? You should be able to replicate the error which I described in my previous comment.



    ------------------------------
    Thanks,
    Prasad
    ------------------------------

    Attachment(s)



  • 11.  RE: Append data from multiple excels into a single excel spreadsheet using Javelin

    Posted 11 days ago
      |   view attached
    I copied your file to my temp directory changed the location values and ran it with no errors:



    Running flow...
    2020-08-03 14:27:10 - Run flow: C:\Users\Administrator\Desktop\examples\PushCSVIntoMSSQLtable\excelFilesInFolderToSingleSheet.vwf
    2020-08-03 14:27:11 - Running Test Case: Flowchart
    2020-08-03 14:27:11 - Executing Step: FilesInFolder
    2020-08-03 14:27:11 - With Params:
    2020-08-03 14:27:11 - Variable Name = Timeout		Value = 0
    2020-08-03 14:27:11 - Variable Name = InputFolder		Value = C:\temp\Merge Excel Sample files
    2020-08-03 14:27:11 - Executing Step: LogActivity
    2020-08-03 14:27:11 - With Params:
    2020-08-03 14:27:11 - Variable Name = Timeout		Value = 0
    2020-08-03 14:27:11 - Variable Name = MessageToLog		Value = FileName For read: C:\temp\Merge Excel Sample files\ABCD.xlsx
    2020-08-03 14:27:11 - FileName For read: C:\temp\Merge Excel Sample files\ABCD.xlsx
    2020-08-03 14:27:12 - Executing Step: LogActivity
    2020-08-03 14:27:12 - With Params:
    2020-08-03 14:27:12 - Variable Name = Timeout		Value = 0
    2020-08-03 14:27:12 - Variable Name = MessageToLog		Value = Just before export. Table Header value: True
    2020-08-03 14:27:12 - Just before export. Table Header value: True
    2020-08-03 14:27:12 - Executing Step: LogActivity
    2020-08-03 14:27:12 - With Params:
    2020-08-03 14:27:12 - Variable Name = Timeout		Value = 0
    2020-08-03 14:27:12 - Variable Name = MessageToLog		Value = Append Value: False
    2020-08-03 14:27:12 - Append Value: False
    2020-08-03 14:27:12 - Executing Step: ExportDataTableActivity
    2020-08-03 14:27:12 - With Params:
    2020-08-03 14:27:12 - Variable Name = Timeout		Value = 0
    2020-08-03 14:27:12 - Variable Name = ExportFormat		Value = Excel
    2020-08-03 14:27:12 - Variable Name = FilePath		Value = C:\temp\Merge Excel Sample files\Out.xlsx
    2020-08-03 14:27:12 - Variable Name = InElement		Value = 
    2020-08-03 14:27:12 - Variable Name = WorksheetName		Value = Sheet_1_new
    2020-08-03 14:27:12 - Executing Step: LogActivity
    2020-08-03 14:27:12 - With Params:
    2020-08-03 14:27:12 - Variable Name = Timeout		Value = 0
    2020-08-03 14:27:12 - Variable Name = MessageToLog		Value = FileName For read: C:\temp\Merge Excel Sample files\CDUA.xlsx
    2020-08-03 14:27:12 - FileName For read: C:\temp\Merge Excel Sample files\CDUA.xlsx
    2020-08-03 14:27:12 - Executing Step: LogActivity
    2020-08-03 14:27:12 - With Params:
    2020-08-03 14:27:12 - Variable Name = Timeout		Value = 0
    2020-08-03 14:27:12 - Variable Name = MessageToLog		Value = Just before export. Table Header value: False
    2020-08-03 14:27:12 - Just before export. Table Header value: False
    2020-08-03 14:27:12 - Executing Step: LogActivity
    2020-08-03 14:27:12 - With Params:
    2020-08-03 14:27:12 - Variable Name = Timeout		Value = 0
    2020-08-03 14:27:12 - Variable Name = MessageToLog		Value = Append Value: False
    2020-08-03 14:27:12 - Append Value: False
    2020-08-03 14:27:12 - Executing Step: ExportDataTableActivity
    2020-08-03 14:27:12 - With Params:
    2020-08-03 14:27:12 - Variable Name = Timeout		Value = 0
    2020-08-03 14:27:12 - Variable Name = ExportFormat		Value = Excel
    2020-08-03 14:27:12 - Variable Name = FilePath		Value = C:\temp\Merge Excel Sample files\Out.xlsx
    2020-08-03 14:27:12 - Variable Name = InElement		Value = 
    2020-08-03 14:27:12 - Variable Name = WorksheetName		Value = Sheet_1_new
    2020-08-03 14:27:13 - Executing Step: LogActivity
    2020-08-03 14:27:13 - With Params:
    2020-08-03 14:27:13 - Variable Name = Timeout		Value = 0
    2020-08-03 14:27:13 - Variable Name = MessageToLog		Value = FileName For read: C:\temp\Merge Excel Sample files\DCAB.xlsx
    2020-08-03 14:27:13 - FileName For read: C:\temp\Merge Excel Sample files\DCAB.xlsx
    2020-08-03 14:27:13 - Executing Step: LogActivity
    2020-08-03 14:27:13 - With Params:
    2020-08-03 14:27:13 - Variable Name = Timeout		Value = 0
    2020-08-03 14:27:13 - Variable Name = MessageToLog		Value = Just before export. Table Header value: False
    2020-08-03 14:27:13 - Just before export. Table Header value: False
    2020-08-03 14:27:13 - Executing Step: LogActivity
    2020-08-03 14:27:13 - With Params:
    2020-08-03 14:27:13 - Variable Name = Timeout		Value = 0
    2020-08-03 14:27:13 - Variable Name = MessageToLog		Value = Append Value: False
    2020-08-03 14:27:13 - Append Value: False
    2020-08-03 14:27:13 - Executing Step: ExportDataTableActivity
    2020-08-03 14:27:13 - With Params:
    2020-08-03 14:27:13 - Variable Name = Timeout		Value = 0
    2020-08-03 14:27:13 - Variable Name = ExportFormat		Value = Excel
    2020-08-03 14:27:13 - Variable Name = FilePath		Value = C:\temp\Merge Excel Sample files\Out.xlsx
    2020-08-03 14:27:13 - Variable Name = InElement		Value = 
    2020-08-03 14:27:13 - Variable Name = WorksheetName		Value = Sheet_1_new
    2020-08-03 14:27:13 - Executing Step: LogActivity
    2020-08-03 14:27:13 - With Params:
    2020-08-03 14:27:13 - Variable Name = Timeout		Value = 0
    2020-08-03 14:27:13 - Variable Name = MessageToLog		Value = FileName For read: C:\temp\Merge Excel Sample files\HGJH.xlsx
    2020-08-03 14:27:13 - FileName For read: C:\temp\Merge Excel Sample files\HGJH.xlsx
    2020-08-03 14:27:14 - Executing Step: LogActivity
    2020-08-03 14:27:14 - With Params:
    2020-08-03 14:27:14 - Variable Name = Timeout		Value = 0
    2020-08-03 14:27:14 - Variable Name = MessageToLog		Value = Just before export. Table Header value: False
    2020-08-03 14:27:14 - Just before export. Table Header value: False
    2020-08-03 14:27:14 - Executing Step: LogActivity
    2020-08-03 14:27:14 - With Params:
    2020-08-03 14:27:14 - Variable Name = Timeout		Value = 0
    2020-08-03 14:27:14 - Variable Name = MessageToLog		Value = Append Value: False
    2020-08-03 14:27:14 - Append Value: False
    2020-08-03 14:27:14 - Executing Step: ExportDataTableActivity
    2020-08-03 14:27:14 - With Params:
    2020-08-03 14:27:14 - Variable Name = Timeout		Value = 0
    2020-08-03 14:27:14 - Variable Name = ExportFormat		Value = Excel
    2020-08-03 14:27:14 - Variable Name = FilePath		Value = C:\temp\Merge Excel Sample files\Out.xlsx
    2020-08-03 14:27:14 - Variable Name = InElement		Value = 
    2020-08-03 14:27:14 - Variable Name = WorksheetName		Value = Sheet_1_new
    2020-08-03 14:27:14 - Running Test Case: Flowchart
    2020-08-03 14:27:14 - Test Case: Flowchart Execution Complete
    Execution complete



    I attached the logs as well.



    ------------------------------
    Gene Howard
    Principal Support Engineer
    Broadcom
    ------------------------------

    Attachment(s)

    zip
    Out.zip   1K 1 version


  • 12.  RE: Append data from multiple excels into a single excel spreadsheet using Javelin

    Posted 11 days ago
    Gene,

    This is strange, when I execute the targeted excel files stored in a folder I get the "Error in implicit conversion: cannot convert null object" however when I open each of the spreadsheet and even without making any modifications and run your workflow, it gets executed without error. I dont understand the difference. I am unable to share those spreadsheets without modifying the sensitive information here. But when I modify the values and share it with you, it works just fine. Would it be possible to have a working session so that I can demonstrate the issue?

    ------------------------------
    Thanks,
    Prasad
    ------------------------------



  • 13.  RE: Append data from multiple excels into a single excel spreadsheet using Javelin

    Posted 11 days ago
    so how are these files being created?
    Is an application creating them?
    Might be a compatibility issue that is corrected when you open them and save them back using excel.

    ------------------------------
    Gene Howard
    Principal Support Engineer
    Broadcom
    ------------------------------



  • 14.  RE: Append data from multiple excels into a single excel spreadsheet using Javelin

    Posted 11 days ago
    Gene,
    I believe these excel files are being published from datamaker after mining the data from DB2 and oracle databases. Let me confirm from my team and get back to you tomorrow. Thank you so much for looking into this. Appreciate your support!

    ------------------------------
    Thanks,
    Prasad
    ------------------------------



  • 15.  RE: Append data from multiple excels into a single excel spreadsheet using Javelin

    Posted 10 days ago
    Hi Gene,

    I checked with my team and got the information that these source excel spreadsheets are being created using Javelin which is called from datamaker. Is there any property we need set so that it doesnt throw this error while executing READEXCELACTIVITY component of Javelin?


    ------------------------------
    Thanks,
    Prasad
    ------------------------------



  • 16.  RE: Append data from multiple excels into a single excel spreadsheet using Javelin

    Posted 10 days ago

    So if I understand the flow it is the following
    1) connect to DB2 with Javelin and extract data into multiple excel spreadsheets.
    2) then use Javelin to combine them into a single spreadsheet.
    The second step is where we are failing.

    is this correct?

    I would need to look at the original flow I think to see if there is something there we can do to convert the null so character fields or something of that nature.



    ------------------------------
    Gene Howard
    Principal Support Engineer
    Broadcom
    ------------------------------



  • 17.  RE: Append data from multiple excels into a single excel spreadsheet using Javelin

    Posted 10 days ago
      |   view attached
    Just had another thought.
    Why not just read in your tables and post them all to a single spreadsheet in Javelin?
    I have attached a flow that looks at multiple tables in a database and adds them to a single excel spreadsheet.

    ------------------------------
    Gene Howard
    Principal Support Engineer
    Broadcom
    ------------------------------

    Attachment(s)