Insert empty column of excel into sql

Help Activities
, , ,
1

I have one Excel file there are some columns that do not have any value.I want to insert the data into SQL when I run the workflow it shows I cannot find column 21. This error occurs because column no 21 is empty.

2

Hey @Cuberoot ,

Is column exists in the Datatable variable after reading from excel?

Thanks,
Sanjit

1 Like
3

yes.it exists

4

Can you please attach a screenshot here of datatable with 21st column

5

image
image1229Ă—125 7.12 KB

input file

6

The number of columns are 21, but the index of first column is 0 and last column is 20, so to access the last column you need to put 20 instead of 21, while entering the value to database.

7

I have found the solution.Do you know how to resolve this-

image
image773Ă—172 7.47 KB

8

thanks. yes when I shared the image then I realized this

1 Like
9

@Cuberoot

Please use log message and print the sql statement so that we can see what the syntax error is

Cheers

10

right now I am getting this error-

image
image1280Ă—720 170 KB

11

@Cuberoot

Either connection string is wrong

if that is not true then please try changing the database package version and check mostly 1.6 works

cheers

12

after downgrade the package form 1.7.1 to 1.6.1 it shows me the below error:

image
image1280Ă—720 147 KB

13

Hi @Cuberoot

Iterate through the columns of the DataTable using a For Each activity. Set the TypeArgument property of the For Each activity to System.Data.DataColumn.
Within the loop, check if the column is empty using an If activity. You can use the IsNullOrWhiteSpace method to check if the column is empty.

Example-

The Condition to check if a column at index 20 (column number 21) is empty:-

String.IsNullOrWhiteSpace(row.Item(20).ToString)

Thanks!!

14

@Cuberoot

Did you happen to install the data providers?

Cheers

15

how to install data providers

16

@Cuberoot

Here you have for sql server

If using some other db then downlod accordingly

Cheers

17

is it suitable for oracle ?

18

@Cuberoot

For oracle can use this…check the compatibility depending on version youa re using

Cheers

19

Should I download both ?

20

@Cuberoot

You can download any one …one is odbc drivers and other is oledb drivers…anything can be used…the connection string should be created accordingly using the installed driver

Cheers