How to import big numbers on general formart from Excel to SQL Server 17 on Varchar()?

Question

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

Answer 1

When importing large numbers from Excel to SQL Server, Excel may automatically format the number in scientific notation (e.g., 7.04116e+012) when the number is too large. This is a common issue when dealing with large numbers and Excel.

The best way to deal with this is to ensure that the data in Excel is in a suitable format before the import. You can force Excel to treat the numbers as text, preserving the original formatting.

1. In Excel, select the column that contains the numbers.
2. Right-click and select "Format Cells."
3. Under "Category," select "Text."

This will cause Excel to treat the contents of the cells as text. The numbers will be preserved exactly as they are, with no conversion to scientific notation.

Once you have done this, you can then import the data into SQL Server. Since you are importing the data as text, you should use the varchar or nvarchar datatype in SQL Server to store the data.

Another thing you mentioned is leading zeros in your data. You should note that Excel will remove leading zeros from numbers because they are mathematically insignificant. By setting the cells to text format before entering the data, you will ensure that Excel preserves any leading zeros.

If the Excel file is already created and you cannot modify it, and the numbers are already in the scientific notation, you could handle this at the SQL Server end with some data conversion. You would read the data into a float or real data type column then convert to varchar or nvarchar. However, this approach may still not handle the leading zeros correctly. In such a case, the data preparation would really need to be done in Excel or the source system.

Answer 2

1. In Excel, select the column that contains the numbers.
2. Right-click and select "Format Cells."
3. Under "Category," select "Text."

When I put Format Cells and Category = Text, Excel immediately transform to scientific notation. I got it by formatting Excel Personalized and, as on Brazil used to CNPJ with 14 characters, I fill it with 14 repetitions of number '0'(zero) as parameter. On SQL Server I'm using bigint or varchar(14).