将CSV导入Microsoft SQL Server 2014向导(Import CSV to Microsoft SQL Server 2014 Wizard)

将CSV导入Microsoft SQL Server 2014向导(Import CSV to Microsoft SQL Server 2014 Wizard)

我有一个非常简单（但很大）的CSV文件，我想将它导入Microsoft SQL Server 2014中的数据库（数据库/任务/导入数据）。 但是我收到以下错误：

转换返回状态值2和状态文本“由于潜在的数据丢失，无法转换该值”。

以下是我的CSV文件示例（包含约9百万行）：

1393013,297884，'20150414 15:46:25'

1393010,301242，'20150414 15:46:58'

理想情况下，我的第一列和第二列是bigint，第三列是datetime。 在向导中，我为前两个选择'unsigned 8字节整数'，为第三个选择'timestamp'，我收到错误。 即使我尝试将所有三列的字符串用作数据类型，但仍然收到相同的错误。

我也尝试在命令行中使用bcp命令。 它没有任何错误，也没有插入！ 另外使用“批量插入”命令使我错误地说：“列太长了！验证你的终结器”，但是它们被正确修复了！

我很欣赏您对这个简单问题的解决方案。

I have a very simple (but big) CSV file and I want to import it to my database in Microsoft SQL Server 2014 (Database/Tasks/Import Data). But I receive the following error :

The conversion returned status value 2 and status text "The value could not be converted because of a potential loss of data".

Here is a sample of my CSV file (containing ~ 9 million rows) :

1393013,297884,'20150414 15:46:25' 1393010,301242,'20150414 15:46:58'

Ideally my first and second columns are big-int and the third is datetime. In the wizard, I choose 'unsigned 8 byte integer' for first two and 'timestamp' for the third and I receive the error. Even I try to use string for all three columns as data type and still I receive the same error.

I also tried using bcp command in command line. It errs nothing and inserts nothing! Also using "bulk insert" command errors me that :

the column is too long! verify your terminators

But they are correctly fixed!

I appreciate any idea you have as a solution to this simple-looking problem.

最满意答案

您正在尝试更改输入类型：无符号8字节整数是源上的设置。 您根本不需要更改源设置。 'string [DT_STR]'和默认长度50将起作用。

'timestamp'是二进制类型。 我相信你所追求的类型是datetime，但是那个集合在目的地而不是源头。 无论如何，源仍然是一个字符串。

您仍然无法将日期值导入为日期时间数据类型。 这会工作（添加破折号） - > 2015-04-14 15:46:25。 导入您拥有的字符串并在导入后修复它，除非您可以更改文本文件。

You are trying to change the input types: unsigned 8 byte integer is a setting on the source. You don't need to change source setting at all. 'string [DT_STR]' and the default length of 50 will work.

'timestamp' is a binary type. I believe the type you are after is datetime, but that set is on the destination, not the source. The source is still a string regardless.

You still will not be able to import your date value as a datetime data type. This would work though (added dashes) -> 2015-04-14 15:46:25. Import what you have as string and fix it after import unless you can get your text file changed.