Support > Forums > OpenBots Studio > Can't load Excel from a Data Table
December 06,2022 at 7:20 PM
I have a workflow that seems harder than it should be. Perhaps I am unaware of the best approach,
This is my workflow:
I create Excel application with New Workbook option
I run a SQL query and output results to data table, DTreportData.
Excel Append Sheet with Sheetname assigned
Excel Activate Sheet.
This next command raises an error...
Excel Append Range Data Table: DTreportData. Column Letter: "A" Add Headers: Yes
I don't see a way to specify columns in the data table. But, right now I am ok with writing all columns.
The error logged is:
Line: 17, Exception Type: ArgumentException, Exception Message: Input string was not in a correct format.Couldn't store
This is the contents of the DT. System.Data.DataTable
[[Batch#, ReportYear, Project, WorkMonth, Billable, Hrs]] [144, 2022, BAE/Cash App, 12, Yes, 2.00] [144, 2022, Konica/API/Boeing, 12, Yes, 0.50] [144, 2022, SPGlobal/Develop, 12, Yes, 3.00] [144, 2022, Staff Training/Open Bots, 12, No, 4.50] [144, 2022, BAE/Cash App, 12, No, 1.50] [144, 2022, DCG-HMI/Support, 12, Yes, 0.50] [144, 2022, Roth/Internal Development, 12, No, 16.83] [144, 2022, SPGlobal/IHSM Integration, 12, Yes, 0.50]
I tried to alter the SQL to cast the Batch# as String (to resolve t same error reported on that column) Then I received the next error reported on the WorkMonth column. Following this logic, it appears I may need to cast all numeric data as String. Batch#, ReportYear, WorkMonth, Hrs
Why does the Append Range command care about the data type? Do I need to prepare the Excel columns in some way before I Append Range?
Can you recommend a better workflow? Thank You.
This forum has 314 topics, 681 replies, and was last updated 23 days ago by Support Agent
December 12,2022 at 7:42 PM Hi gsanfilippo,
Our team has investigated the reported case, and noted the behavior as a bug with the Append/Write range commands. A ticket has been reported, and the fix will be included in the next Studio release v2.0.0.
For now, as a workaround we recommend to convert the table columns to string type, before writing the table to Excel. This can be done by using a C# code snippet via the Evaluate Snippet command.
See sample below:
vClonedDatatable = vDatatable1.Clone(); vClonedDatatable.Columns["col2"].DataType = typeof(String); foreach (DataRow row in vDatatable1.Rows) { vClonedDatatable.ImportRow(row); }
December 08,2022 at 7:47 PM The recommendation to use Format Range seems logical. I hope the next post will explain what I did wrong.
December 08,2022 at 4:12 PM The first two columns are numeric.
Excel Format Range. Range: "A1:B1000", Date Type: Number, Thousands Separator: No, Number of Decimals: 0.
I made the change and the error was not resolved.
The error was logged as follows:
2022-12-08 10:36:31.280 -05:00 [ERR] Error Occurred at Line 24: Excel Append Range [Column '"A"' - DataTable 'DTreportData' - Instance Name 'OB_Excel_Instance'] - [Source: 'C:\Users\guyma\OneDrive\Documents\OpenBots Studio\OB Scripts\Timesheets\TS_1070_CreateExcelSummary.obscript', Type: 'ArgumentException', Message: 'Input string was not in a correct format.Couldn't store <Batch#> in Batch# Column. Expected type is Int64.']
I tried something else. Excel Append Range with Add Headers: No. Then the error disappears. This points to a MicroSoft issue. It is typing the columns as strings - using the column headings, then it rejects the integer data.
As a matter of fact, when I remove headers then the Excel Format Range. Range: "A1:B1000", etc. is not needed. This seems to confirm my theory. The question is how to overcome the MS typing based on the headers. Toi allow the typing to occur on the first row of data.???
December 08,2022 at 11:57 AM Hi guy Sanfilippo,
Thank you for sharing your query.
As per your workflow we suggest you that, try to use Excel Format Range for that particular column where you get the error, that will resolve it, and then use Excel Append Range. Please let us know if its work.
Thanks
You are not authorized to reply, please click here to login and add your reply to this topic.