Unanswered

Update query misses out the numbers starting with apostrophe in excel

(1) Share

Report

Posted on by Atharvazd

Hi All,

I have a column 'TestColumn1' to be updated in excel file using sql query. The column type is General and it has few rows which has numeric values and one of the number starts with apostrophe ' as it has 0 in the beginning. while updating i am using following query.

%NewVar% = 1234,0567

UPDATE [Sheet1$]

SET [TestColumn2]= 'Pass'

WHERE [TestColumn1] IN (%NewVar%)

Result is it is only updated for number 1234.

Any hint please?

Categories:

General topics

I have the same question (0)

All responses (4)

Answers (0)

Sort by

sandeepstw 312 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

Hi @Atharvazd ,

When using SQL queries to update Excel files, ensure that numeric values containing leading zeros are formatted as text by enclosing them in single quotes. Modify your query like this :

%NewVar% = '1234.0567'

UPDATE [Sheet1$]
SET [TestColumn2] = 'Pass'
WHERE [TestColumn1] IN (%NewVar%)

This is will update TestColumn2 based on numeric value matches for TestColumn1.

Please mark as solution if it helps.

Thanks,

Sandeep Mishra

Was this reply helpful? Yes No
rishabhgupta584 4 on at

Like
a
(1)

Report
Copy link

Link copied!

Hello, I am also having the same issue. Like in Excel I have a column of type 'General' but when I use this query it updates the row by 0 and showing this error message in excel "Number stored as text" convert to number.

UPDATE [%Sheet%$]
SET [%Balance%]=0.00, ------> Numeric Value
[%Monthly%] = 0.00 --------> Numeric Value
where [DocumentNo]= '%CurrentItem3['DocumentNo']%'

Any Idea, as you can see here I am using 0.00 which is a Number not a string value.

Was this reply helpful? Yes No
sandeepstw 312 Moderator on at

Like
a
(0)

Report
Copy link

Link copied!

HI @rishabhgupta584 ,

Ensure your Excel column is correctly recognized as numeric before running the update query.

Right-click and choose "Format Cells". Select "Number" and set decimal places as needed.

Mark as solution if it helps.

Thanks,

Sandeep Mishra

Was this reply helpful? Yes No
rishabhgupta584 4 on at

Like
a
(0)

Report
Copy link

Link copied!

Thanks Sandeep, for your reply.
But I tried that in Excel my format is Number for the whole column. But after I run the update query the values are stored as Text instead of Number.

Was this reply helpful? Yes No