How to import a very large csv file into an existing SQL Server table?

7
votes

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.

4
sqlsql-servercsvbulkinsertbcp
You need to provide more info. What commands are you running for BULK INSERT or bcp? – JNK
Maybe the file itself it corrupt/incorrect. – GolezTrol
@JNK, USE myDb BULK INSERT myTable FROM 'C:\Users\myFile.csv' WITH ( FIRSTROW = 2, -- DATAFILETYPE = 'char', -- MAXERRORS = 100, FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' ); – Micky W.
@MickyWalia - put it in your question with code tags so it's readable please. – JNK
@JNK, I was editing it - hit Enter by mistake. – Micky W.

4 Answers

1
votes

Check your file for an EOF character where it shouldn't be - BCP is telling you there is a problem with the file.

Notepad ++ may be able to load the file for you to view and search.

1
votes

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.

0
votes

How are you mapping the fields in the file with the columns in the table? Are the number of columns in the table the same as the number of fields in the file? Or are you using a format file to specify the column mapping? If so, is the format file formatted correctly?

If you are using the format file and if you have the "Number of columns" parameter wrong, it will cause the error "Unexpected end of file". See this for some other errors/issues with bulk uploading.

0
votes

It is probably not the solution your expecting but with Python you could create a table out of the csv very easily (just uploaded a 1GB CSV file):

import pandas as pd
import psycopg2
from sqlalchemy import create_engine

# Read the csv to a dataframe
df = pd.read_csv('path_to_csv_file', index_col='name_of_index_column',  sep=",") 

# Connect and upload
engine = create_engine('postgresql+psycopg2://db_user_name:db_password@localhost:5432/' + 'db_name', client_encoding='utf8')
df.to_sql('table_name', engine, if_exists='replace', index =True, index_label='name_of_index_column')