如何将非常大的csv文件导入到现有的SQL Server表中? [英] How to import a very large csv file into an existing SQL Server table?

问题描述

我有一个非常大的csv文件，具有约500列，约35万行，我正尝试将其导入到现有的SQL Server表中.

I have a very large csv file with ~500 columns, ~350k rows, which I am trying to import into an existing SQL Server table.

我尝试了BULK INSERT，我得到了-Query executed successfully, 0 rows affected.有趣的是，BULK INSERT在几秒钟内就完成了类似的操作，但对于较小的csv文件，不到50列，约77k行.

I have tried BULK INSERT, I get - Query executed successfully, 0 rows affected. Interestingly, BULK INSERT worked, in a matter of seconds, for a similar operation but for a much smaller csv file, less than 50 cols., ~77k rows.

我也尝试过bcp，我得到-Unexpected EOF encountered in BCP data-file. BCP copy in failed.

I have also tried bcp, I get - Unexpected EOF encountered in BCP data-file. BCP copy in failed.

任务很简单-纯粹受挫的极限应该不难.有什么想法或建议吗?您是否已成功使用其他工具，实用程序来完成批量导入操作或类似的操作?谢谢.

The task is simple - it shouldn't be hard to the limits of pure frustration. Any ideas or suggestions? Any other tools, utilities that you have successfully used to accomplish a bulk import operation or something similar? Thanks.

-批量插入

USE myDb  
BULK INSERT myTable  
FROM 'C:\Users\myFile.csv'  
WITH  
(  
FIRSTROW = 2,  
-- DATAFILETYPE = 'char',  
-- MAXERRORS = 100,  
FIELDTERMINATOR = ',',  
ROWTERMINATOR = '\n'  
);

-bcp

bcp myDb.dbo.myTable in 'C:\Users\myFile.csv' -T -t, -c

更新
我现在改变了路线.我决定在SQL Server外部加入csv文件，这是我的目标，因此我现在不必将数据上传到表中.但是，尝试从csv文件仅上传(大容量插入或"bcp")仅1条记录(约490列)会很有趣，否则将失败，然后查看其是否有效.

UPDATE
I have now changed course. I've decided to join the csv files, which was my goal to begin with, outside of SQL Server so that I don't have to upload the data to a table for now. However, it'll be interesting to try to upload (BULK INSERT or 'bcp') only 1 record (~490 cols.) from the csv file, which otherwise failed, and see if it works.

推荐答案

最后一行很可能缺少\n.另外，尽管T-SQL应该提到这一点，但SQL-Server中的行大小(8060字节)有一个限制.但是，请检查此链接:

Most likely the last line lacks a \n. Also, there is a limitation in the row size (8060 bytes) in SQL-Server although T-SQL should have mention this. However, check this link:

我的建议:从一行开始，然后开始工作.然后休息.

My advice: Start with one row and get it to work. Then the rest.

这篇关于如何将非常大的csv文件导入到现有的SQL Server表中?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！