Bulk upload to sqlserver from excel - Error in Column Names

Hi Everyone,

I am having error from Uipath while using an insert activity (Couldn,t Find column0,coloumn1…) whereas my excel columns and table columns starts with namea , nameb . How to manipulate the column names and match the excel column names to DB table column names ?

Thanks.

2 Likes

you were almost done
while using read range make sure the ADD HEADERS property is enabled in the property panel
Cheers @vboddu

1 Like

Thank you very much. It worked and how much data it can handle? Can it handle 10k rows with 20 columns?

1 Like

yah ofcourse
Cheers @vboddu

1 Like

Great Thankyou.

1 Like

Awesome
Cheers @vboddu

@Palaniyappan: what if my headers don’t start in the first row and start in the 4th and 5th row . will uipath take that as a header? and columns are fixed in a sheet but not rows we don’t know the range it will differ for each file . should we use read range or read column(output is IEnumerable), where DB insert activity is expecting datatable as input. please help me

1 Like

what if my headers don’t start in the first row and start in the 4th and 5th row .

@vboddu let me summarize your requirements:
EXCEL sheet contains a row on e.g 4th position holding in each the cell the column name

In this case you can do following:
Readin the row from Excel or take it from datatable if available e.g. from a read range

with a for each activity you can interate over each column and can set the name of the column in the datatable with an assign activity
dataTableVariable.dt.Columns(idxOftheIteration).ColumnName = ValueFromTheCell/Column(idxOftheIteration)

Fine
In that case let’s do one thing
—uncheck the ADD HEADERS property in the property panel of read range activity
—he the output with a variable of their datatable named outdt
—before accessing the value of each column or any column in specific let’s validate whether it is taken next to the column name (to check whether the first column is column header or not) with a if condition like this
Not String.IsNullOrEmpty(Outdt.Columns(0).ColumnName.ToString)
If this is true it will go to THEN part where we can use a for each row loop and pass the datatable as input and inside this for each row loop where we can access the Column values of each row with assign activity like this row(“yourcolumnname”).ToString

Or it will go to ELSE part where we can
We can use same for each row loop with datatable variable passed as input but inside the loop use a if condition that validates whether the row has any value or not
Not String.IsNullOrEmpty(row(0).ToString)
If true it will go to THEN part where we can get the value we want or it will go to ELSE apart where we can leave it without any activity so that it will loop through the next row from the datatable as we are still inside the for each row loop (inside the previous ELSE part) and Again will check with next value whether it has the or not and if has it will be there in the THEN part

Simple isn’t it
Hope this would help you
You were almost done
Kindly try this and let know for any queries or clarification
Cheers @vboddu

the information above the header will not be null , it will information regarding reporting and i dont want to iterate each row as my data will have 10 k rows

and one more question is the output of read range is IENumerable , but insert activity is expecting an datatable as input. so how to tackle this ?

@ppr :I don’t want to iterate as I have around 6k columns which may take time.i just want to take from excel and dump in DB. my requirement is the data will be from the first column (‘A’ column to nth (‘k’ column) column, but rows are not same as it might be 5k rows today and 4k rows another day. In this case should I use read range or read column activity?

@vboddu
in the case of extracting only a subset from excel:

  • calculate no. of rows (rowcount of Excel data)
  • calculate the no of columns that you want to extract

use a read range activity

  • pass the the following range information A1:nthColumnRowCount
  • configure other options e.g. untick add Headers

the result will be a DataTable

if required -

  • changing / adopting columnnames of the datatable can be done afterwards (s. post above)
  • doing a offset for readin the data can be done e.g. by range information "AOffsetRowNo

In case you require the Excel column name in the A, B, AA, AC syntax this component is helpfully:

In case you need further help then please list down all constraints and requirements in clear list. So we can work out a better solution.

This topic was automatically closed 3 days after the last reply. New replies are no longer allowed.