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.
Original Message:
Sent: 08-20-2020 12:43 PM
From: Prasad Raghu
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
Hi Jaimini,
Please review the file which Gene has attached above. I had also created a similar one.
------------------------------
Thanks,
Prasad
Original Message:
Sent: 08-20-2020 09:20 AM
From: Jaimini Bhatt
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
Hi Raghu,
This is cool. Can you share the sample solution with a few sample csv files?
Thanks,
Jaimini
Original Message:
Sent: 08-20-2020 08:55 AM
From: Gene HOWARD
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
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
Original Message:
Sent: 08-19-2020 05:08 PM
From: Prasad Raghu
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
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
Original Message:
Sent: 08-05-2020 10:28 AM
From: Gene HOWARD
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
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
Original Message:
Sent: 08-04-2020 05:09 PM
From: Prasad Raghu
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
Are you getting the same "Error in implicit conversion: Cannot convert null object" error?
------------------------------
Thanks,
Prasad
Original Message:
Sent: 08-04-2020 05:04 PM
From: Gene HOWARD
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
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
Original Message:
Sent: 08-04-2020 04:12 PM
From: Prasad Raghu
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
Thank you very much, Gene! Appreciate your support. I shall try with CSV format.
------------------------------
Thanks,
Prasad
Original Message:
Sent: 08-04-2020 03:17 PM
From: Gene HOWARD
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
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
Original Message:
Sent: 08-04-2020 02:19 PM
From: Prasad Raghu
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
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
Original Message:
Sent: 08-04-2020 01:39 PM
From: Gene HOWARD
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
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
Original Message:
Sent: 08-04-2020 01:28 PM
From: Prasad Raghu
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
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
Original Message:
Sent: 08-03-2020 03:03 PM
From: Gene HOWARD
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
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
Original Message:
Sent: 08-03-2020 02:59 PM
From: Prasad Raghu
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
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
Original Message:
Sent: 08-03-2020 02:32 PM
From: Gene HOWARD
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
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.vwf2020-08-03 14:27:11 - Running Test Case: Flowchart2020-08-03 14:27:11 - Executing Step: FilesInFolder2020-08-03 14:27:11 - With Params:2020-08-03 14:27:11 - Variable Name = Timeout Value = 02020-08-03 14:27:11 - Variable Name = InputFolder Value = C:\temp\Merge Excel Sample files2020-08-03 14:27:11 - Executing Step: LogActivity2020-08-03 14:27:11 - With Params:2020-08-03 14:27:11 - Variable Name = Timeout Value = 02020-08-03 14:27:11 - Variable Name = MessageToLog Value = FileName For read: C:\temp\Merge Excel Sample files\ABCD.xlsx2020-08-03 14:27:11 - FileName For read: C:\temp\Merge Excel Sample files\ABCD.xlsx2020-08-03 14:27:12 - Executing Step: LogActivity2020-08-03 14:27:12 - With Params:2020-08-03 14:27:12 - Variable Name = Timeout Value = 02020-08-03 14:27:12 - Variable Name = MessageToLog Value = Just before export. Table Header value: True2020-08-03 14:27:12 - Just before export. Table Header value: True2020-08-03 14:27:12 - Executing Step: LogActivity2020-08-03 14:27:12 - With Params:2020-08-03 14:27:12 - Variable Name = Timeout Value = 02020-08-03 14:27:12 - Variable Name = MessageToLog Value = Append Value: False2020-08-03 14:27:12 - Append Value: False2020-08-03 14:27:12 - Executing Step: ExportDataTableActivity2020-08-03 14:27:12 - With Params:2020-08-03 14:27:12 - Variable Name = Timeout Value = 02020-08-03 14:27:12 - Variable Name = ExportFormat Value = Excel2020-08-03 14:27:12 - Variable Name = FilePath Value = C:\temp\Merge Excel Sample files\Out.xlsx2020-08-03 14:27:12 - Variable Name = InElement Value = 2020-08-03 14:27:12 - Variable Name = WorksheetName Value = Sheet_1_new2020-08-03 14:27:12 - Executing Step: LogActivity2020-08-03 14:27:12 - With Params:2020-08-03 14:27:12 - Variable Name = Timeout Value = 02020-08-03 14:27:12 - Variable Name = MessageToLog Value = FileName For read: C:\temp\Merge Excel Sample files\CDUA.xlsx2020-08-03 14:27:12 - FileName For read: C:\temp\Merge Excel Sample files\CDUA.xlsx2020-08-03 14:27:12 - Executing Step: LogActivity2020-08-03 14:27:12 - With Params:2020-08-03 14:27:12 - Variable Name = Timeout Value = 02020-08-03 14:27:12 - Variable Name = MessageToLog Value = Just before export. Table Header value: False2020-08-03 14:27:12 - Just before export. Table Header value: False2020-08-03 14:27:12 - Executing Step: LogActivity2020-08-03 14:27:12 - With Params:2020-08-03 14:27:12 - Variable Name = Timeout Value = 02020-08-03 14:27:12 - Variable Name = MessageToLog Value = Append Value: False2020-08-03 14:27:12 - Append Value: False2020-08-03 14:27:12 - Executing Step: ExportDataTableActivity2020-08-03 14:27:12 - With Params:2020-08-03 14:27:12 - Variable Name = Timeout Value = 02020-08-03 14:27:12 - Variable Name = ExportFormat Value = Excel2020-08-03 14:27:12 - Variable Name = FilePath Value = C:\temp\Merge Excel Sample files\Out.xlsx2020-08-03 14:27:12 - Variable Name = InElement Value = 2020-08-03 14:27:12 - Variable Name = WorksheetName Value = Sheet_1_new2020-08-03 14:27:13 - Executing Step: LogActivity2020-08-03 14:27:13 - With Params:2020-08-03 14:27:13 - Variable Name = Timeout Value = 02020-08-03 14:27:13 - Variable Name = MessageToLog Value = FileName For read: C:\temp\Merge Excel Sample files\DCAB.xlsx2020-08-03 14:27:13 - FileName For read: C:\temp\Merge Excel Sample files\DCAB.xlsx2020-08-03 14:27:13 - Executing Step: LogActivity2020-08-03 14:27:13 - With Params:2020-08-03 14:27:13 - Variable Name = Timeout Value = 02020-08-03 14:27:13 - Variable Name = MessageToLog Value = Just before export. Table Header value: False2020-08-03 14:27:13 - Just before export. Table Header value: False2020-08-03 14:27:13 - Executing Step: LogActivity2020-08-03 14:27:13 - With Params:2020-08-03 14:27:13 - Variable Name = Timeout Value = 02020-08-03 14:27:13 - Variable Name = MessageToLog Value = Append Value: False2020-08-03 14:27:13 - Append Value: False2020-08-03 14:27:13 - Executing Step: ExportDataTableActivity2020-08-03 14:27:13 - With Params:2020-08-03 14:27:13 - Variable Name = Timeout Value = 02020-08-03 14:27:13 - Variable Name = ExportFormat Value = Excel2020-08-03 14:27:13 - Variable Name = FilePath Value = C:\temp\Merge Excel Sample files\Out.xlsx2020-08-03 14:27:13 - Variable Name = InElement Value = 2020-08-03 14:27:13 - Variable Name = WorksheetName Value = Sheet_1_new2020-08-03 14:27:13 - Executing Step: LogActivity2020-08-03 14:27:13 - With Params:2020-08-03 14:27:13 - Variable Name = Timeout Value = 02020-08-03 14:27:13 - Variable Name = MessageToLog Value = FileName For read: C:\temp\Merge Excel Sample files\HGJH.xlsx2020-08-03 14:27:13 - FileName For read: C:\temp\Merge Excel Sample files\HGJH.xlsx2020-08-03 14:27:14 - Executing Step: LogActivity2020-08-03 14:27:14 - With Params:2020-08-03 14:27:14 - Variable Name = Timeout Value = 02020-08-03 14:27:14 - Variable Name = MessageToLog Value = Just before export. Table Header value: False2020-08-03 14:27:14 - Just before export. Table Header value: False2020-08-03 14:27:14 - Executing Step: LogActivity2020-08-03 14:27:14 - With Params:2020-08-03 14:27:14 - Variable Name = Timeout Value = 02020-08-03 14:27:14 - Variable Name = MessageToLog Value = Append Value: False2020-08-03 14:27:14 - Append Value: False2020-08-03 14:27:14 - Executing Step: ExportDataTableActivity2020-08-03 14:27:14 - With Params:2020-08-03 14:27:14 - Variable Name = Timeout Value = 02020-08-03 14:27:14 - Variable Name = ExportFormat Value = Excel2020-08-03 14:27:14 - Variable Name = FilePath Value = C:\temp\Merge Excel Sample files\Out.xlsx2020-08-03 14:27:14 - Variable Name = InElement Value = 2020-08-03 14:27:14 - Variable Name = WorksheetName Value = Sheet_1_new2020-08-03 14:27:14 - Running Test Case: Flowchart2020-08-03 14:27:14 - Test Case: Flowchart Execution CompleteExecution complete
I attached the logs as well.
------------------------------
Gene Howard
Principal Support Engineer
Broadcom
Original Message:
Sent: 08-03-2020 01:28 PM
From: Prasad Raghu
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
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
Original Message:
Sent: 08-03-2020 10:16 AM
From: Gene HOWARD
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
Can you provide a sam[le excel spreadsheet for testing?
------------------------------
Gene Howard
Principal Support Engineer
Broadcom
Original Message:
Sent: 08-02-2020 02:16 PM
From: Prasad Raghu
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
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
Original Message:
Sent: 07-17-2020 04:13 PM
From: Gene HOWARD
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
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
Original Message:
Sent: 07-17-2020 04:07 PM
From: Gene HOWARD
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
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
Original Message:
Sent: 07-08-2020 03:42 PM
From: Prasad Raghu
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
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
Original Message:
Sent: 07-07-2020 09:38 AM
From: Scott Schmitz
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
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
Original Message:
Sent: 07-02-2020 05:48 PM
From: Prasad Raghu
Subject: Append data from multiple excels into a single excel spreadsheet using Javelin
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
------------------------------