14,502 questions
I think you are better off importing them as bigint. I believe that Excel saves numbers this big as floating-point values, so the scientific format is perfectly logical.
Wrong on coversion different types
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(188.79999999999998, 43%, 28%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EHA%3C/text%3E%3C/svg%3E)
Helder Arnoldo Flores
20
Reputation points
I need urgently to import a big numbers on general format from Excel to SQL Server 2017. When I do by ImportExport SQL function, the data result on Hexadecimal caracters. Example:
On Excel = 7041162000117 general or SQL float type. When Import to Varchar(14) the result is
7.04116e+012. I need to continue on normal caractere format, not on Hexa, ASCII or similar.
It's a document numbers on my database and many the first caracatere is '0'. How can I do Please? It's urgently. Thanks a lot
Helder Flores
SQL Server | Other
{count} votes
-
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(128, 8%, 22%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EA%3C/text%3E%3C/svg%3E)
Anonymous2023-05-01T02:34:43.99+00:00 We have not received a response from you. Did the reply could help you? If the response helped, do "Accept Answer". If it doesn't work, please let us know the progress. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.
-
Helder Arnoldo Flores 20 Reputation points
2023-05-01T20:01:28.39+00:00 HEY.
I apologize for not having commented on your answers. I decided to matter to Bigint, then I passed the field to Varchar (14), then completed with 0 (zeros) left those who had less than 14 characters. I solved the emergence, but I am not able to automate the process, because you can not be changing the Type of the field in all imports. And when I convert to String, again he becomes a pile of numbers and letters, incomprehensible. Very bad that there is no more viable solution to these situations. But thank you very much for your will and help from you.
Sign in to comment
Accepted answer
-
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator2023-04-28T20:48:15.21+00:00 -
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(137.6, 31%, 23%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3ENN%3C/text%3E%3C/svg%3E)
Naomi Nosonovsky 8,431 Reputation points2023-04-28T21:25:38.8433333+00:00 How would you handle 0 as a first character?
-
Helder Arnoldo Flores 20 Reputation points
2023-04-28T22:40:15.11+00:00 Yes, but the problem is number with many digits, when importing data, it's became hexa, I Think. I want to find a way to import that data type without that kind os problem.
-
Helder Arnoldo Flores 20 Reputation points
2023-04-28T22:44:02.2333333+00:00 Ok, but the numbers lost the first digit, if that is 0(zero). Ok, but it's demand more work after. If possible I want to import and still thar digits when there are.
-
Erland Sommarskog 121.9K Reputation points MVP Volunteer Moderator
2023-04-29T08:42:56.25+00:00 To answer Naomi's question: I put a single quote before the number to tell Excel that it is a string and not a number. And I think that Helder will have to do this as well, if you wants the values in varchar(14). But If he does not want to edit the Excel file, you need to use the bigint data type for the import. You can alter the column later.
And, by the way, that is not hexadecimal format. This notation is known as scientific notation, and is often used with values from natural sciences where values can be very large or very small, but you never have 14 digits of precision.
Sign in to comment -
1 additional answer
Sort by: Most helpful
-
Helder Arnoldo Flores 20 Reputation points
2023-05-01T20:02:31.1166667+00:00 HEY.
I apologize for not having commented on your answers. I decided to matter to Bigint, then I passed the field to Varchar (14), then completed with 0 (zeros) left those who had less than 14 characters. I solved the emergence, but I am not able to automate the process, because you can not be changing the Type of the field in all imports. And when I convert to String, again he becomes a pile of numbers and letters, incomprehensible. Very bad that there is no more viable solution to these situations. But thank you very much for your will and help from you.