How do I load text or csv file data into SQL Server?
If you need to load data into SQL Server (e.g. from log files, csv files, chat transcripts etc), then chances are, you’re going to be making good friends with the BULK INSERT command.
The command to bulk insert comma-delimite data would be:
BULK INSERT OrdersBulk
FROM ‘c:\file.csv’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)If the csv file has a header row, try this:
BULK INSERT OrdersBulk
FROM ‘c:\file.csv’
WITH
(
FIRSTROW = 2,
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)Finally, you can also specify how many errors you want to allow before considering that the BULK INSERT failed.
BULK INSERT OrdersBulk
FROM ‘c:\file.csv’
WITH
(
FIRSTROW = 2,
MAXERRORS = 0,
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’
)
I was in need of it recently and this bulk insert works fine on my local machine but web hosting provider was not supporting this command on server due to some security reason. they were also not supporting microsoft text driver or any odbc driver. I then use this component to read csv and load data row by row…:)
http://www.codeproject.com/KB/database/CsvReader.aspx?fid=142714&df=90&mpp=25&noise=3&sort=Position&view=Quick&select=2482321&fr=26
Hi ,
I did the same think, it worked .Thanks a lot
But if the CSV file doesnt have a value in the rows like 1,tim,doyle, ,60789. Then while loading into the table. it inserts “NULL”. Is there are so that i can have empty value for that field in the table column instead of NULL.
Thanks a lot
I don’t understand this code.
-Do you execute it as a SQL statement?
-Which table does the data go in to?
-Which column is the data inserted to?
Please help!