Bulk insert messing up fields

salma.pinky (2/10/2013)

GO
Use test
DROP TABLE PDTEST
create table pdtest
(
CustID INT IDENTITY(1,1),
work_phone_extension varchar(5),
residential_postal_or_zip_code varchar(30),
residential_street_address_line_1 varchar(30),
residence_phone varchar(17),
work_phone varchar(17),
name_part varchar(30),
mailing_city varchar(20),
mailing_postal_or_zip_code varchar(30),
mailing_street_address_line_2 varchar(30),
mailing_street_address_line_1 varchar(30),
cell_phone varchar(17),
residential_city varchar(20),
residential_country_and_province_or_state varchar(10),
mailing_country_and_province_or_state varchar(10)
)
BULK INSERT pdtest
FROM 'C:\Python27\Scripts\pd.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '',
KEEPNULLS,
FIRSTROW = 2
)
GO

Here is my textfile row ,91234,,,,WOLFIE HOWLETT,416-,Toronto,,,,,Toronto,CA_ON,CA_ON

All my columns are messed up.The name part should have Wolfie Howlett.It has 416- and then the work phone extension should be null but it has got value 91234 and residential_postal_or_zip_code is null which is wrong.

The field mapping is wrong here.What should I do to correct it?

if exists(select 1 from sys.tables where object_id = object_id('pdtest')) drop table pdtest;

create table pdtest
(
work_phone_extension varchar(5),
residential_postal_or_zip_code varchar(30),
residential_street_address_line_2 varchar(30),
residential_street_address_line_1 varchar(30),
work_phone varchar(17),
name_part varchar(30),
residence_phone varchar(17),
mailing_city varchar(20),
mailing_postal_or_zip_code varchar(30),
mailing_street_address_line_2 varchar(30),
mailing_street_address_line_1 varchar(30),
cell_phone varchar(17),
residential_city varchar(20),
residential_country_and_province_or_state varchar(10),
mailing_country_and_province_or_state varchar(10)
);
go
BULK INSERT pdtest
FROM 'C:\Users\Mydir\SQLCode\Testdata2.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\ n', -- you need to remove the space between the \ and n
KEEPNULLS,
firstrow = 2
)
GO
select * from pdtest
go

if exists(select 1 from sys.tables where object_id = object_id('pdtest')) drop table pdtest;
if exists(select 1 from sys.tables where object_id = object_id('pdtest2')) drop table pdtest2;

create table pdtest
(
work_phone_extension varchar(5),
residential_postal_or_zip_code varchar(30),
residential_street_address_line_2 varchar(30),
residential_street_address_line_1 varchar(30),
work_phone varchar(17),
name_part varchar(30),
residence_phone varchar(17),
mailing_city varchar(20),
mailing_postal_or_zip_code varchar(30),
mailing_street_address_line_2 varchar(30),
mailing_street_address_line_1 varchar(30),
cell_phone varchar(17),
residential_city varchar(20),
residential_country_and_province_or_state varchar(10),
mailing_country_and_province_or_state varchar(10)
);
go

create table pdtest2
(
CustID INT IDENTITY(1,1),
work_phone_extension varchar(5),
residential_postal_or_zip_code varchar(30),
residential_street_address_line_2 varchar(30),
residential_street_address_line_1 varchar(30),
work_phone varchar(17),
name_part varchar(30),
residence_phone varchar(17),
mailing_city varchar(20),
mailing_postal_or_zip_code varchar(30),
mailing_street_address_line_2 varchar(30),
mailing_street_address_line_1 varchar(30),
cell_phone varchar(17),
residential_city varchar(20),
residential_country_and_province_or_state varchar(10),
mailing_country_and_province_or_state varchar(10)
);
go

BULK INSERT pdtest
FROM 'C:\Users\lapettis\Documents\SQLCode\Testdata2.csv'
WITH
(
FIELDTERMINATOR = ',',
ROWTERMINATOR = '\ n', -- Must remove the space between the \ and the n
KEEPNULLS,
firstrow = 2
)
GO
insert into pdtest2(
work_phone_extension,
residential_postal_or_zip_code,
residential_street_address_line_2,
residential_street_address_line_1,
work_phone,
name_part,
residence_phone,
mailing_city,
mailing_postal_or_zip_code,
mailing_street_address_line_2,
mailing_street_address_line_1,
cell_phone,
residential_city,
residential_country_and_province_or_state,
mailing_country_and_province_or_state
)
select
work_phone_extension,
residential_postal_or_zip_code,
residential_street_address_line_2,
residential_street_address_line_1,
work_phone,
name_part,
residence_phone,
mailing_city,
mailing_postal_or_zip_code,
mailing_street_address_line_2,
mailing_street_address_line_1,
cell_phone,
residential_city,
residential_country_and_province_or_state,
mailing_country_and_province_or_state
from
pdtest;
go

select * from pdtest;
select * from pdtest2;
go