Value of type 'String' cannot be converted to 'System.Data.DataTable' error when trying to store values into excel using Write Range activity

[Bhaskar_Mukka]

Hi All,

In my workflow I am connecting to SQL database using “Execute Query” activity with required SQL query and storing the output as type “DataTable” . Using “For Each Row” activity I am able to iterate through the DataTable successfully. Using “Get Row Item” activity I am able to get the required “ColumnName” successfully, when I print the output using “Message Box” activity I am getting the output as ( Item1, Item2, Item3, Item4). So I used “Assign” activity and assigned a variable type String (String Array) and splitted the output with comma successfully. When the print the output I am able to see Item1 for the first time and Item2 so on …
So now I am using “Excel Application Scope” activity and provided excel path. Using “For Each” activity I looped the String Array variable successfully by printing the output using “Message Box” activity.
But, When I am trying to write the output into excel using “Write Range” activity I am getting Value of type ‘String’ cannot be converted to ‘System.Data.DataTable’ error message.

Can anyone tell me how can I resolve this issue.

Second screenshot

[pathLessTaken (Hose Son)]

@Bhaskar_Mukka you’re getting the error because ‘EachItem’ is a string. The Write Range Activity only takes in DataTable variables and not strings. If you need to only write one cell, you can replace Write Range with Write Cell and it will work. If you want to write in multiple cells inside your For Each loop, you can create an Int32 variable called index and increment it every time you loop. Inside your Write Cell Activity you can can change your target cell from “A1” → “A”+index.ToString and it will write each item in consecutive cells in your excel file.

P.S.
I prefer using a DataTable to jumble together everything I need and then writing just once to Excel using Write Range, due to DRM issues in large companies. However, this takes a lot of practice and requires more knowledge regarding DataTables, such as utilizing the Build DataTable Activity and Add Data Row and so on. If you want to study DataTables, that will help you write Excel files more efficiently in the long run.

[Bhaskar_Mukka]

Hi @pathLessTaken,

@ClaytonM

Thanks for the support. I tried Utilizing Build Data Table activity and Add Data Row activity and I am able to write the output to my excel sheet successfully but while writing to excel I am see some unnecessary text is writing into my excel. Please find the excel and suggest what can I do.

I highlighted in Yellow.

[ClaytonM]

Hey @Bhaskar_Mukka

Check in your Build Data Table activity. I think you might have info in the first row, which you need to delete so it starts out as empty.

Let me know if that solves it.

Thanks.

[Bhaskar_Mukka]

@ClaytonM Thanks you. I forgot to remove the first row in Build Data Table Activity