Copying selected range from SQL to Excel sheet

Help
1

I have a column of length more than 10 char, ex:“2019081234590” in SQL. When I copy that to Excel sheet, it will come in decimal format, ex:“2.01981234590E12” something la that. When I select this cell, and assign to string it will be in same decimal format, but I want it to be in normal format. How can I achieve this?

1 Like
2

As i can see in your issue your column in excel has a format of decimal thats why it change to that format. @Abhishek_Nayak

cheers :smiley:

Happy learning :smiley:

3 Likes
3

But when I double click on the cell it will display entire character. Even in fx it is visible, so i don’t think so it is in decimal format.

1 Like
4

Hi @Abhishek_Nayak

Try to do it cint(row(“columnname”).ToString

Thanks
Ashwin.S

5

I will check with that. Thank you

6

Tried to do this, but i am getting “Arithmetic operation resulted in an overflow”

7

Any other suggestions?

8

I’m not sure if when UiPath writes to a cell if it can only write values or if formulas work… You could try having your items stored in the datatable as =TEXT(<value_from_SQL>, "0")

If you open up Excel and write this to a cell, for example something like =TEXT(1237345E10,"0") You should get 12373450000000000 but the issue is if you have to do more calculations on this data set you’ll run into issues because of the formula. If this is just for a reporting purpose, then you could do this with no issues as it will be the last use of the data.

Otherwise forcing the type to string is the best bet, but I don’t know why that didn’t work before, I’d try doing row("ColumnName").ToString instead of the version that also converts it to an int as well beforehand.

1 Like
9

Is possible that you see this in excel, just because the column width cant fit the whole number but it is there:Capture

1 Like
10

Yeah, the issue is I think the strings he’s trying to add to the Excel sheet are likely date strings, IDs, etc that he doesn’t want Excel to format as a number. Since they’re so long it’s formatting it in Scientific Notation, which means the Excel sheet while it isn’t technically wrong, it’s just an inconvenience to browse.

11

The problem is when i copy values from SQL to data table it will convert into that formula format. I want that ID value in a string variable so that i can use that variable to write another select query. Example, if the variable is “Val”, then i want to write " Select * from table where column name="+val

12

Try converting it:
Decimal.Parse(yourscientificnotationvalue, System.Globalization.NumberStyles.Float).ToString

13

It’s showing error “Option strict on disallows implicit conversion from long to string”

1 Like
14

Decimal.Parse(yourscientificnotationvalue.ToString, System.Globalization.NumberStyles.Float).ToString

3 Likes
15

If you want your datatable to have that column as string, you have 2 options, format the column in excel to be text OR do the conversion from excel to datatable yourself, create manually a datatable with that column being string and copy each row of the original datatable converting only that column to text…

1 Like
16

Okay, so there are a few misconceptions happening here… The number stored in SQL is likely an Integer or String already, which is fine, and when you paste it to Excel it IS NOT a Decimal value, it is typecasting it to an Integer, which again, is fine. I’ll write out a small example here:

So if for example your number is 2456356784522348, you read this from the SQL Database and load it into your DataTable variable…

From here, you print it to Excel, and Excel reads the following: 2.45636E+15. Again, THIS IS NOT TECHNICALLY A DECIMAL. It is just displayed in a decimal format so an excel user can get a better understanding of the magnitude of the value when reading information within Excel. You can see how it may be confusing without Scientific Notation with the following example image:
intSciNotation

  • The values in A1 and A2 are IDENTICAL. As you can see by forcing the long printed version in A1, when a neighboring value exists in B1, you cannot get a grasp of what the value in A1 actually is. Now in A2, using the Scientific Notation, someone can easily tell that A2 is many magnitudes larger than B2 rather than just a few.

This number in A1 is 2.45636x10^15, unfortunately UiPath reads this as a Decimal. So what we can do it one of two things… Firstly, the easiest of the two is to add a ' (apostrophe) before the number in Excel. Then it is read as a string and forces a full print of the number in the Excel Sheet. This is read by UiPath as a String and can easily be casted to an Integer.
IMPORTANT NOTE: When UiPath reads this value from Excel, it IGNORES the apostrophe.

So instead of printing 237384563457 we would print '237384563457 into Excel. This would provide us the format shown in A1 in the image above.

The second messier option is to print all the values and if you need to use them again, you would split them into an array by splitting on E+ or E-. Assuming you then had an array arrSplit you would have to then do this operation: (cint(arrSplit(0))*10^cint(arrSplit(1))).ToString which is obviously more tedious than the first option.

IN SUMMARY
Print your values to excel with an apostrophe added to the beginning of them. This will print them in long form, it is Excel’s way of forcing that format regardless of the cell formatting. Instead of 237384563457 you would print '237384563457 into Excel. UiPath INGORES the apostrophe when reading this value from that cell in the future as well. So it will still read it as a String, but you can easily cast it to an Integer with cint().
Note you would want to write this to Excel as "'" + SQLDTVar.Rows(x).Item(y).ToString or something similar.
As a result you would see 237384563457 instead of 2.4E+11.

Hope this clears things up, sorry for the long and likely messy read.

2 Likes
17

just two small things, Cint(x) is convert and not cast and the other thing is he cant use Integer because the number is too big for it, must use like CDbl(x) for this one.

2 Likes
18

Tysm for all your suggestion. This worked perfectly

19

This will also work, but the previous solution was short and crisp.

1 Like
20

Thank you all for your valuable suggestions.