Hello community,
I am facing issue while inserting the data from excel sheet to SQL. My excel data is stored from column A to S but row number containing data would change at every run.
Could you please help me how to get the data from excel dynamically and then insert to SQL?
I am able to connect to database.
Please let me know in case of any queries.
Regards
Vaibhav
Use Connect transaction activity and use execute query
use Insert into tablename(columnname)Values(“+row(“Columnname”).ToString+”)
Thanks
Ashwin S
You can do it two ways depending on how well your excel columns and database columns are aligned in terms of datatype.
You will need to use Read Range first to read the spreadsheet into a datatable.
First option, you can use Insert Datatable activity if they are aligned - this enables you to insert the whole datatable in one go.
Second option, you can use a for each loop on that datatable and then execute non-query and use insert statement (you can either use parameters or code in variables into the statement)
Hi @AshwinS2,
Thanks for the prompt response.
When you say “columnname” which column name should I put A or S?
and Is there no use of data table that was output from the read range activity?
Hi @timk
Thanks for the response.
I have created a workflow which reads the data from excel using read range and also created a SQL table with matching data types but somehow its getting stuck at the insert query. I assume since I havent mentioned the range and kept as blank (" ") the entire worksheet is getting read and its the reason its not inserting the data into table - my assumption . That is why want to read data only from column A to S with the rows which have data.
Are you able to share your insert query?
After Read Range, you can try using the filter datatable to specify only columns A-S (using SelectColumnsMode).
Otherwise, your best bet is use my second option of for each loop and then either parameters or as @ashwins2 suggested adding the variables within your query.
From the SQL query database package I am using Insert activity. I pass the output data table from excel as datatable and SQL table name.
In the second method as suggested by Ashwin what would be the columnname in insert query?
Anybody who can help me with the working workflow?
SQLUpdate.xaml (4.4 KB)
@vaibhavtandon1987,
You need to configure your database connecting string here in this xaml, and change the excel file path and excel sheet name which contains the data. Then run and check.
I assumed the excel and your database table has the same columns with same datatypes.
Hi @sarathi125
I am getting the error:
An error has occured
Message: Cannot create and populate list type System.Data.Odbc.OdbcErrorCollection. Path ‘$values’, line 1, position 175.
Source: Newtonsoft.Json
Exception Type: JsonSerializationException
Newtonsoft.Json.JsonSerializationException: Cannot create and populate list type System.Data.Odbc.OdbcErrorCollection. Path ‘$values’, line 1, position 175.
at Newtonsoft.Json.Serialization.JsonSerializerInternalReader.CreateNewList(JsonReader reader, JsonArrayContract contract, Boolean& createdFromNonDefaultCreator)
If I run your workflow by making changes then I get error:
SQLUpdate has thrown an exception
Message: Object reference not set to an instance of an object. This error usually occurs when using a variable with no set value (not initialized).
Source: Insert
Exception Type: NullReferenceException
System.NullReferenceException: Object reference not set to an instance of an object.
at UiPath.Database.Activities.InsertDataTable.EndExecute(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.System.Activities.IAsyncCodeActivity.FinishExecution(AsyncCodeActivityContext context, IAsyncResult result)
at System.Activities.AsyncCodeActivity.CompleteAsyncCodeActivityData.CompleteAsyncCodeActivityWorkItem.Execute(ActivityExecutor executor, BookmarkManager bookmarkManager)
Still getting the above 
An error has occured
Message: Cannot create and populate list type System.Data.Odbc.OdbcErrorCollection. Path ‘$values’, line 1, position 175.
Source: Newtonsoft.Json
Exception Type: JsonSerializationException
From where the excel is populated, Did you provide the correct database connecting string and other settings in the insert activity
Hello,
Yes the DB connection is tested successful dont know why the excel data is not getting pushed.
Try with single row in the excel and check.
Tried with 1 row but the same Jason serialization exception 
Can you show the screenshot of your workflow here, just want to understand where is the problem.
What you have in Execute Non Query, Do we need that here, I think we can directly use Insert to update the database.
Anil_G
ashokkarale
Yoichi
Julian3
sharazkm32
minako_tanaka
jast1631
A_Learner
Mr.BENJAMINE_STAIRRIERS
srdjan.suc