Hi am trying to load the excel data into sql through uipath,I have checked in forum I haven’t get better solution for this ,please help me out in this.
Dependencies for project is not restoring .
@DivyaT From the package manager install Database activities
You can complete this using below steps
- Read Excel and store in Data table
- Build another data table with same column names and data types of database tables and merge both datatables
change the column name and datatype as per the column names and data types in database table.
- Use Execute non-query activity to insert the datatable into database.
does the above work for redshift databases? Could you please share a sample working file? I have been trying to insert data from excel but not been able to for a week.
Are you able to connect to database using Connect activity?
Can you please share us the error message you face and the connection string you pass to the activity.
Yes I am able to connect. I was able to pull data from the table to csv as well.
The problem is I am not able to insert data into the table.I have tried multiple things but nothing worked. In the last attempt I am using Insert activity but its not accepting the value in ‘Data table’ field of the activity.
Can you please share me the error screenshot or the workflow.
If you’re able to connect then it can insert as well. Make sure the column names and column datatypes are same as in Database.
Thanks for solution,I got it.Main.xaml (5.1 KB)
Please check this xml file.
An ExceptionDetail, likely created by IncludeExceptionDetailInFaults=true, whose value is:
System.Data.Odbc.OdbcException: ERROR  [Amazon][Amazon Redshift] (30) Error occurred while trying to execute a query: [SQLState 42703] ERROR: column “column1” does not exist in test_ui
Server stack trace:
at System.Data.Odbc.OdbcConnection.HandleError(OdbcHandle hrHandle, RetCode retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader, Object methodArguments, SQL_API odbcApiMethod)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior behavior, String method, Boolean needReader)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Odbc.OdbcCommand.ExecuteDbDataReader(CommandBehavior behavior)
at System.Data.Common.DbCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.Common.DbCommandBuilder.GetSchemaTable(DbCommand sourceCommand)
at System.Data.Common.DbCommandBuilder.BuildCache(Boolean closeConnection, DataRow dataRow, Boolean useColumnsForParameterNames)
at System.Data.Common.DbCommandBuilder.GetInsertCommand(DataRow dataRow, Boolean useColumnsForParameterNames)
at UiPath.Database.DatabaseConnection.InsertDataTable(String tableName, DataTable dataTable)
at System.Runtime.Remoting.Messaging.StackBuilderSink._PrivateProcessMessage(IntPtr md, Object args, Object server, Object& outArgs)
at System.Runtime.Remoting.Messaging.StackBuilderSink.AsyncProcessMessage(IMessage msg, IMessageSink replySink)
Exception rethrown at :
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)
I think there is a error in the query. Please refer this
Error says it cant find the column1. You can try with single quotes for the column name.
Please check the column names which you have provided,the column name should be same as excel table column.
Ok, I did try that and for that purpose what I did was - I created a test excel sheet with only 3 columns and that had ‘general’ as a format in excel sheet. I then created a table with varchar as datatype for the table. Then also it was not able to push the data.
I am not using any query as of now but ‘Insert’ activity to push data to redshift cluster.
you are pushing data from excel to database or database to excel , is this possibility to share your workflow?
Your workflow worked. I am pushing from excel to database. I tested for a table with 3 columns and it worked. Thanks a lot for your help!
I would now try it on 48 column table to see if there are challenges or issues. Otherwise the solution you provided worked like a charm!
I think I was missing configure connection initially, but thanks for the solution!