server导出csv文件不去除0 sql,如何将非常大的csv文件导入到现有的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.

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.

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.

-- BULK INSERT

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

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.

解决方案

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.