SQL 从 Excel 导入数据向导自动检测数据类型
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/20924554/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Import data from Excel Wizard automatically detects data types
提问by Marek
Hello I'm trying to import data from excel file (xls)to new SQL tableso I use Import and Export data 32/bitto achieve that. When I load the excel file it automatically detects data types of columns. e.g. column with phone numbers is as data type to new table floatand in excel is as Double(15) when I try to change the floatto nvarchar
您好，我正在尝试将数据从 excel 文件导入(xls)到新文件中，SQL table所以我Import and Export data 32/bit用来实现这一目标。当我加载 excel 文件时，它会自动检测列的数据类型。例如，与电话号码列的数据类型新表float并在Excel是双（15）当我尝试改变float，以nvarchar
I get this :
我明白了：
Found 2 unknown column type conversion(s) You have selected to skip 1 potential lost column conversion(s) You have selected to skip 3 safe column conversion(s)
发现 2 个未知的列类型转换 您已选择跳过 1 个可能丢失的列转换 您已选择跳过 3 个安全的列转换
And I'm not allowed to continue with export.
我不允许继续出口。
Is there any way to change the data types when trying to import them?
尝试导入时有什么方法可以更改数据类型吗？
Thank you for your time.
感谢您的时间。
These data are set as textdata type in excel
Sample data from one of the columns in excel:
这些数据text在 excel中设置为数据类型 来自 excel 列之一的示例数据：
5859031783
5851130582
8811014190
This is what I get:
这就是我得到的：
回答by M.Ali
Select the Column in your Excel sheet and change the data type to text
选择 Excel 工作表中的列并将数据类型更改为文本
Then go to your sql server open import-export wizard and do all the steps of select source data and bla bla when you get to the point of Mapping Column, it will select Floatdata type by default, You will have to change it to NVARCHAR(N)in my test I changed it to NVARCHAR(400), it gave me a warning that I might lose some data as I am converting data from 1 datatyep to another.
然后去你的sql server open import-export向导，做选择源数据和bla bla的所有步骤，到了点Mapping Column，它会Float默认选择数据类型，你必须NVARCHAR(N)在我的测试中将其更改为我将其更改为 NVARCHAR(400)，它警告我可能会丢失一些数据，因为我将数据从 1 个数据类型转换为另一个数据类型。
When you get to the Data Type Mappingpage make sure you select Convert checkbox. and stop the process of failure of as appropriate.
当您到达该Data Type Mapping页面时，请确保选中“转换”复选框。并酌情停止失败的过程。
Going through all these steps finally got my data in the destination table with the same Warningthat I have converted some data n bla bla after all microsoft worries too much :)
经过所有这些步骤，我终于在目标表中得到了我的数据，Warning因为我已经转换了一些数据 n bla bla 毕竟微软太担心了:)
Finally Data in Sql-Server Table
Sql-Server 表中的最后数据
╔═══════╦════════════╦═════════╦════════════════╦══════════════╗
║ Name ║ City ║ Country ║ Phone ║ Float_Column ║
╠═══════╬════════════╬═════════╬════════════════╬══════════════╣
║ Shaun ║ London ║ UK ║ 04454165161665 ║ 5859031783 ║
║ Mark ║ Newyork ║ USA ║ 16846814618165 ║ 8811014190 ║
║ Mike ║ Manchester ║ UK ║ 04468151651651 ║ 5851130582 ║
╚═══════╩════════════╩═════════╩════════════════╩══════════════╝
