Oledb select query

Help Studio
, ,
1

I have an excel file with a column name Amount. The amount column has integer and null datarow.

I am connecting the excel file with oledb connection. Want to run select query which convert integer to double(CDbl) and skip null row simultaneously .

2

Double.TryParse(InputValue.ToString,0) can be used to convert the value to string if possible, the empty spaces will be converted to 0

3

I dont want to replace null field with 0. Just want to ignore that field.

4

Hi @Pinky_AG

1.Use the “Excel Application Scope” activity to connect to the Excel file. Inside the “Excel 2.Application Scope,” use the “Read Range” activity to read the data from the Excel file and store it
in a DataTable variable, let’s call it dtExcelData.
3.Add an “Invoke Code” activity after the “Excel Application Scope.”
4.In the “Invoke Code” activity, create a VB.NET code snippet to convert the integer column to
double and skip null rows.
5.After the “Invoke Code” activity, you will have the dtExcelData DataTable with the integer column converted to double and null rows skipped.
6.You can now use the data in dtExcelData for further processing or write it back to another Excel file using the “Write Range” activity, if required.

Hope it works!!

5

Hello @vrdabberu

My requirement is to execute select query on excel file which i have connected as oldedb

6

Basically I want to write an Select query to select rows from the [sheet] where the [amount] is not null, has a length greater than 0, and the [amount] is equal to 123 after converting it to a double.

7

@Pinky_AG

Try this

SELECT *
FROM [SheetName$]
WHERE [amount] IS NOT NULL AND LEN([amount]) > 0 AND CDbl([amount]) = 123

Hope it works!!

8

This is the same syntax i am using but it is giving me following error

16898722363655853538093529489985
168987223636558535380935294899851920×1440 196 KB

Error:

16898723129593942222196226947467
168987231295939422221962269474671920×1440 265 KB

9

@Pinky_AG

Have you replace the sheet and amount with the actual headers?

Regards

10

Yes i did. And the error is coming becaue of the double conversion…looks like i cant have all the 3 condition at once… i need to use nested select statement where cdbl will work on already slected rows where amount column is not null and length is >0

11

@Pinky_AG

Try not to convert to double instead compare the same number as string and check

TRIM([Amount]) = '13975000'

cheers

12

Yes, it may work but i need the double conversion. So cant avoid that part as well

13

@Pinky_AG

Where clause has nothing to do with your double conversion…your select * part is responsible for double…if you use * even if you do cdbl in where it wont take effect in anyway

cheers

14

I am using

SELECT *
FROM (
SELECT [amount]
FROM [sheet]
WHERE [amount] IS NOT NULL
AND LEN([amount]) > 0
) AS subquery
WHERE CDBL(subquery.[amount]) = CDBL(‘123’).tostring;

But it is giving error as invalid use of null

Can someone help?

15

Hi @Pinky_AG

Try this

SELECT *
FROM (
  SELECT [amount]
  FROM [sheet]
  WHERE [amount] IS NOT NULL
  AND LEN([amount]) > 0
) AS subquery
WHERE subquery.[amount] IS NOT NULL
AND CDBL(subquery.[amount]) = CDBL('123');

I hope it helps!!

16

@Pinky_AG

No need of using not null…>0 should suffice…did you first try use select * from table…that way you can see how the data looks in datatable and then decide

Cheers

17

SELECT *
FROM (
SELECT [amount]
FROM [sheet]
WHERE [amount] IS NOT NULL
AND LEN([amount]) > 0
) AS subquery
WHERE CDBL(ISNULL(subquery.[amount], 0)) = CDBL(‘123’);
try this

18

Hi @lrtetala

Its giving error as invalid use of Null

19

Hello @tazunnisa.badavide

i am getting error as

Wrong number of arguments used with function in query expression
CDBL(ISNULL(subquery.[amount], 0)) = CDBL(‘123’)