asked on

Import CSV file in SQL using Bulk insert

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

Vitor Montalvão

How did you create the CSV file?
It might need a ROWTERMINATOR also.

ASKER CERTIFIED SOLUTION

Vitor Montalvão

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

PAGANED

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
.
.

arnololo123

ASKER

great thanks