February 10, 2013 at 6:39 pm
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?
February 10, 2013 at 7:29 pm
salma.pinky (2/10/2013)
GOUse 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?
Looking at the file, how BULK INSERT works, and running a test myself, it happens to be working correctly. What column is the 416- supposed to be in?
February 10, 2013 at 7:32 pm
Playing a little bit, I think your data may be a bit messed up. In you BULK INSERT you tell it to start at row 2, this tells me that you may have column headers in the first row. It would help to see those as well.
February 10, 2013 at 7:35 pm
One other thing, since you are not using a format file, your test file needs to account for the CustID (IDENTITY) column as well.
February 10, 2013 at 9:10 pm
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
,,,,,PATIENT TEST,416-,Toronto,,,,,Toronto,CA_ON,CA_ON
,,,,,WOLFIE HOWLETT,416-,Toronto,,,,,Toronto,CA_ON,CA_ON
,m4g2k2,,18 Yonge St,,Steve TEST,416-555-5555,Toronto,,,18 Yonge St,,Toronto,CA_ON,CA_ON
,,,,,BUTTERS STOTCH,416-,Toronto,,,,,Toronto,CA_ON,CA_ON
,91044,,1 Manhattan Ave.,,SUE STORM,416-555-5556,Star City,91041,,1 Manhattan Ave.,,Star City,CA_ON,CA_ON
,,,1 Rural Rd E,,CLARK KENT,416-606-0001,Smallville,,,1 Rural Rd E,,Smallville,CA_ON,CA_ON
February 10, 2013 at 9:53 pm
Several problems you need to deal with.
One, the columns you originally defined do not match the columns as listed in your source file. Please look at the new table definition I have below.
Two, to import your data as is, you can not have an identity column defined on the table. If you need or desire this column, you need to do one of two things. One, account for the column in your import file; or 2, use a format file.
Three, I changed the row delimiter from '' to '\ n' -- no space between the \ and n.
As you will notice, I have dropped the identity column as well as changed the order of the columns defined.
The following works with the data as provided in your last post.
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
February 10, 2013 at 10:20 pm
Another alternative is to use a staging table for the data being imported. Using what you have posted, pdtest (without the identity column) serves as a staging table for the bulk import. This table is then used to populate the target table, in this case pdtest2.
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
I am sure there are other alternatives as well, I just don't have time to research them at the moment. I am sure someone else may be able to provide such alternatives.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply