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

[Prasad Raghu]

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

[Scott Schmitz]

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
​

[Prasad Raghu]

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

[Abderrahmane Zahrir]

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

[Gene Howard]

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:[e-mail]/TDM/Javelin/Javelin-4.8.133.0.zip

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

[Gene Howard]

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)

excelFilesInFolderToSingleSheet.zip   2 KB 1 version

[Prasad Raghu]

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

[Prasad Raghu]

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)

Error log.txt   1 KB 1 version

[Gene Howard]

Can you provide a sam[le excel spreadsheet for testing?

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

[Prasad Raghu]

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)

Merge Excel Sample files.zip   34 KB 1 version

[Gene Howard]

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)

Out.zip   1 KB 1 version

[Prasad Raghu]

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

[Gene Howard]

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

[Prasad Raghu]

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

[Prasad Raghu]

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

[Gene Howard]

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

[Gene Howard]

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)

MultipleOracleTablesToXls1.zip   4 KB 1 version

[Prasad Raghu]

Gene,

Its more complicated than that. There are multiple steps involved starting from reading the records from database and writing into individual excel spreadsheets using Javelin. I have attached a sample workflow of one such javelin file which created these individual excel spreadsheets. This is orchestrated using ARD.

Once we have these individual spreadsheets we have to merge them into a single spreadsheet. The end user must have these individual spreadsheets as well as a combined single spreadsheet for their automation framework.

Kindly review the attached WF which i have recreated without any critical information.

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

Attachment(s)

Sample WF.zip   12 KB 1 version

[Gene Howard]

I downloaded this and it is a very complicated flow.
With no way to run this in my lab, I do not think I will be able to troubleshoot this further for you as I have no way of stepping through all of the logic paths to see where the problem may be coming in.

The one thing I can think of the might help would be to export the files to CSV file format instead of EXCEL and see if that does not help resolve the issue.

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

[Prasad Raghu]

Thank you very much, Gene! Appreciate your support. I shall try with CSV format.

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

[Gene Howard]

the other thought would be to leave the section you have to export to the multiple files.
But then change the section that combines that and just use the multiple datasets to write out to a single excel spreadsheet.

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

[Gene Howard]

So I do not have access to DB2. However, I created a simple sample table in MS SQL that had a null column.
I used javelin to export this to 3 different excel spreadsheets and then tried to merge them to a single one and I received and conversion error.
I am trying to find a work around to this.

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

[Prasad Raghu]

Are you getting the same "Error in implicit conversion: Cannot convert null object" error?

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

[Gene Howard]

yes that is the error I am getting.

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

[Gene Howard]

Good morning,
So my short term workaround for this is using the CSV file format.
In my sample flow, I added an export to CSV at the same time I did the excel export.
Then in the section to combine to a single spreadsheet I read in the CSV files instead of the excel spreadsheets and this allowed the flow to go through.

I am still looking into why when using excel spreadsheets from and javelin export that they can not be used for other operations.

I can tell that if I open and save the files that there is a difference in the file size so something is changing even though they visually are the same.

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

[Prasad Raghu]

Thank you Gene! While you continue to investigate the excel issue, I shall test it with CSV instead of excel and see if I am able to append the records. Will keep you posted..

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

[Prasad Raghu]

Hi Gene,

Sorry for the delay. I checked with CSV files and it works fine. I was able to append all the CSV files in a given folder into a single excel spreadsheet without any issues. Just wanted to check if you were able to rectify the excel null object error issue?

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

[Gene Howard]

HI So dev has this now and are working on it as a defect as the files created by javelin should be able to be used later in the flow properly.
I do not have a timeline for this fix.

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

[Jaimini Bhatt]

Hi Raghu,

This is cool. Can you share the sample solution with a few sample csv files?

Thanks,
Jaimini​

[Gene Howard]

Hi so this example connects to a database.
Exports data to multiple CSV files in a single directory
Then combines them into a single excel spreadsheet.

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

Attachment(s)

exporttomultipleexcelFilesInFolderToSingleSheet_CSV.zip   4 KB 1 version

[Prasad Raghu]

Hi Jaimini,

Please review the file which Gene has attached above. I had also created a similar one.

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

[Gene Howard]

Good morning @Prasad Raghu​,

I just wanted to let you know that the dev team has provided a hotfix for Javelin that corrects the issue of writing out multiple excel files from database tables and then using them further in the Javelin flow such as combining into a single sheet.
I have tested this in my lab and it is working fine.

If you would like to have this fix please open a support case and ask for Javelin version 4.8.137.0 or newer.

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

[Prasad Raghu]

Thank you Gene! This really helps. I shall open a support case and request for latest javelin version.

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