This is a very common request recently – How to import CSV file into SQL Server? How to load CSV file into SQL Server Database Table? How to load comma delimited file into SQL Server? Let us see the solution in quick steps.
CSV stands for Comma Separated Values, sometimes also called Comma Delimited Values.
Create TestTable
USE TestData
GO
CREATE TABLE CSVTest
(ID INT,
FirstName VARCHAR(40),
LastName VARCHAR(40),
BirthDate SMALLDATETIME)
GO
Create CSV file in drive C: with name sweetest. text with the following content. The location of the file is C:\csvtest.txt
1,James,Smith,19750101
2,Meggie,Smith,19790122
3,Robert,Smith,20071101
4,Alex,Smith,20040202
Now run following script to load all the data from CSV to database table. If there is any error in any row it will be not inserted but other rows will be inserted.
BULK
INSERT CSVTest
FROM 'c:\csvtest.txt'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\n'
)
GO
--Check the content of the table.
SELECT *
FROM CSVTest
GO
--Drop the table to clean up database.
DROP TABLE CSVTest
GO
Reference : Pinal Dave (https://blog.sqlauthority.com)
839 Comments. Leave new
I need help to insert 4GB csv into SQL Table using SSIS .Thanks in advance.
I have 85 fields. Is there a way to do this without hand typing each and every field?? I just want to import the data and be done. Is there a shortcut? Thanks!
This will help you
I am fresher in batch script and sqlserver
I have one csv file like
1,ajit,kakore,pune
2,vijay,patil,mumbai
i have to retrieve values by , delimiter and insert those values into
database on “employee” table.
but through batch file…So how can i work on that?????????????
my database is on another machine so please guide more about sqlcmd
Hello
when I try to run this Query,
SELECT * INTO TEST FROM userrecht(‘Microsoft.Jet.OLEDB.4.0’, ‘Excel 8.0;Database=C:\Users…\Desktop\table.csv’, ‘SELECT * FROM [Sheet1$]’) WITH( FIELDTERMINATOR = ‘;’, ROWTERMINATOR = ‘\n’ );
I become the following ERROR:
ORA-00933: SQL-Befehl wurde nicht korrekt beendet 00933. 00000 – “SQL command not properly ended” *Cause:
*Action: Fehler in Zeile: 37 Spalte: 15
BULK Insert is not defined for my SQl Developer!
Thanks a lot for your answer.
Hello
Sorry the error message was in German!
Also,
when I try to run this Query,
SELECT * INTO TEST FROM table (‘Microsoft.Jet.OLEDB.4.0′, ‘Excel 8.0;Database=C:\Users…\Desktop\table.csv’, ‘SELECT * FROM [Sheet1$]‘)
WITH( FIELDTERMINATOR = ‘;’, ROWTERMINATOR = ‘\n’ );
I become the following ERROR:
ORA-00933: SQL command was not terminated correctly
00933. 00000 – “SQL command not properly ended” *Cause:
*Action: Error in row: 37 Column: 15
BULK Insert is not defined for my SQl Developer!
Thanks a lot for your answer.
The code will work only for SQL Server
Hello
I have found that the Problem was in this row:
FROM userrecht(‘Microsoft.Jet.OLEDB.4.0′,’Excel 8.0;Database=C:\Users\ashkan.taassob\Desktop\userrechte.csv’,’SELECT * FROM [Sheet1$]’)
what can be here the Problem? It dose not knwo the PaTH?
Thanks for your answer :)
Make sure that the file is located in the Server’s directory
How about a script and file all in one? Possible? Like this…
set nocount on
delete from lw_code_township
–exec sp_Create_Index ‘LW_CODE_TOWNSHIP’, ‘IX_CODE_TWP$TWP_NAME_DESC’, ‘TWP_NAME_DESC,STATE_CODE, COUNTY_CODE’, ‘Y’
print ‘Loading Township Names …’
BULK INSERT lw_code_township
FROM ‘lw_load_townships_ss.sql’
WITH
(
FIELDTERMINATOR = ‘,’,
ROWTERMINATOR = ‘\n’,
FIRSTROW = 16
);
‘AASTAD’,’MN’,’111′
‘ABBOT’,’ME’,’021′
‘ABBOTT’,’PA’,’105′
‘ABERCROMBIE’,’ND’,’077′
hey can any one tell me how to load data which is of type position based
Any ideas why I get
Invalid object name ‘CSVTest’.
I created CSVTest
How to import data into a table from a ftp location?
Thanks a lot Dave. One question pls. If the table has a primary key, is there any sentence in order to ignore duplicates? (just list them)
You can import to some other table, delete duplicates and then import to final table/
great query senior it is useful for me
Thanks for the comment Vijay.
Hello Pinal,
An imp issue with bulk insert.
syntax used: bulk insert Data from ‘F:ESmartDataData_TP.csv’
with (DATAFILETYPE = ‘widechar’,
fieldterminator =’,’, rowterminator =’ n’)
the last column of my table Data is a float value. While i run the query, with float, it gives the error. If i use varchar, it inserts in a single row and appends all content from csv in the last column eg.(0.235 CCNNode– entire data in csv)
Plz help
hi,
In my data file contains amount fields and that contains amount fields like 45,56,000,
and in systems it’s going to 3 columns.how can i change..?please tell..?
What is the column separator?
column separator is ,
and in some columns amount fields also there like creditamt,debamt like that..
id,name,amt,desi,
1,mani,24,450,se,
2,kiran,65,760,eng
in this 24,450 is amt
and 65,760 is amt
but these r going into different columns..
change the delimiter.
thanks bro..
Welcome Mani.
Hi Pinal sir,
Is there any way to import hundred of csv files into SQL once at a time or any tool available to do this?
How to insert into a same table with creating columns dynamically if it is not there in table and its in csv file on importing
Are there any tools to automate testing required for this? I have a tool which accepts data from text files(comma separated field values) and creates/updates data in different tables. I need to validate if the data is pumped to DB properly or not. I am looking for an automated test tool which will be useful for this type of requirement. I may need to validate data types, field sizes, correct data etc. Please suggest.
Very Useful Blog… Appreciate your effort.
Dave I have a problem and worked little with csv files. I have two joined tables. Can I use one CSV file to save data in these two tables?
Msg 4866, Level 16, State 1, Line 9
The bulk load failed. The column is too long in the data file for row 1, column 93. Verify that the field terminator and row terminator are specified correctly.
Msg 7399, Level 16, State 1, Line 9
The OLE DB provider “BULK” for linked server “(null)” reported an error. The provider did not give any information about the error.
Msg 7330, Level 16, State 2, Line 9
Cannot fetch a row from OLE DB provider “BULK” for linked server “(null)”.
————————————————————————————
if somebody facing like that error..
then try this
ROWTERMINATOR = ‘0x0a’
Most of the requirements on this post have nothing to do with bulk loading. If you want to split rows in a CSV and send to 2 different tables, use conditional split in SSIS package. If you want to loop through a number of files in a folder and then move them to history, use an SSIS package. If you don’t have SSIS, then for the first example, first load the whole file into a table and then split it. Try using PowerShell to manipulate files.
SSIS supports multithreading and BULK insert.