Solved

Import CSV file in SQL using Bulk insert

Posted on 2016-11-03
4
24 Views
Last Modified: 2016-11-03
Hello,

I have created the table that match exactly the header of the CSV file but when I  run the bulk insert query it returns ( 0 row(s) afftected)
CREATE TABLE [dbo].[TCountry](
	[CountryCode] [varchar](3) NULL,
	[IsValid] [char](1) NULL,
	[CountryDesignation] [varchar](150) NULL,
	[LCC] [char](1) NULL,
	[RegionCode] [nchar](10) NULL,
	[User] [varchar](50) NULL,
	[UpdateDateTime] [varchar](50) NULL
) ON [PRIMARY]

bulk insert TCountry from 'C:\COUNTRY.csv'
with ( FIELDTERMINATOR =';', FIRSTROW = 2 )

Open in new window

COUNTRY.csv
0
Comment
Question by:arnololo123
  • 2
4 Comments
 
LVL 45

Expert Comment

by:Vitor Montalvão
ID: 41872322
How did you create the CSV file?
It might need a ROWTERMINATOR also.
0
 
LVL 45

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41872326
Try with the ROWTERMINATOR:
bulk insert TCountry from 'C:\temp\COUNTRY.csv'
with ( FIELDTERMINATOR =';', ROWTERMINATOR='0x0a',FIRSTROW = 2)

Open in new window

0
 
LVL 1

Expert Comment

by:PAGANED
ID: 41872345
Your problem stems from your CSV file
.
Change your CSV file into a TXT file , and it will work fine
Make sure the file at the location "C:\"  is on the SQL Server !
..........
bulk insert TCountry from 'C:\COUNTRY.txt'
with ( FIELDTERMINATOR =';', FIRSTROW = 2 )
...........
CountryCode;IsValid;CountryDesignation;LCC;RegionCode;User;UpdateDateTime
ABW;Y;Aruba;Y;ROW;youbi;2015-08-11T16:04:08
AFG;Y;Afghanistan;Y;ASI;youbi;2015-08-11T16:04:08
AGO;Y;Angola, Republic of;Y;ROW;youbi;2015-08-11T16:04:08
AIA;Y;Anguilla;Y;ROW;youbi;2015-08-11T16:04:08
ALB;Y;Albania, People's Socialist Republic of;Y;EUR;youbi;2015-08-11T16:04:08
AND;Y;Andorra, Principality of;Y;EUR;youbi;2015-08-11T16:04:08
ANT;Y;Netherlands Antilles;Y;ROW;youbi;2015-08-11T16:04:08
ARE;Y;United Arab Emirates;Y;ASI;youbi;2015-08-11T16:04:08
ARG;Y;ARGENTINA;Y;SAM;youbi;2015-08-11T16:04:08
ARM;Y;Armenia;Y;ASI;youbi;2015-08-11T16:04:08
ASM;Y;American Samoa;Y;ROW;youbi;2015-08-11T16:04:08
ATA;Y;Antarctica (the territory South of 60 deg S);Y;ROW;youbi;2015-06-16T15:49:31
ATF;Y;French Southern Territories;Y;ROW;youbi;2015-06-16T15:49:31
ATG;Y;Antigua and Barbuda;Y;ROW;youbi;2015-08-11T16:04:08
AUS;Y;Australia, Commonwealth of;N;ROW;youbi;2015-08-11T16:04:08
.
.
0
 

Author Closing Comment

by:arnololo123
ID: 41872365
great thanks
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
MS SQL server Varchar and nvarchar, GMT_DATE 23 50
Backup Job question 4 20
Test a query 23 19
DATETIMEOFFSET feature 1 5
When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now