| Author |
 Topic  |
|
|
salmab
Starting Member
3 Posts |
 Posted - 02/10/2013 : 20:13:22
|
Hi,
I'm trying to do a bulk insert.
Here is my sql script
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 = '\n',
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? |
|
|
James K
Flowing Fount of Yak Knowledge
1518 Posts |
Posted - 02/10/2013 : 21:44:42
|
quote:
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.
Try putting a comma at the beginning of each row,,91234,,,,WOLFIE HOWLETT,416-,Toronto,,,,,Toronto,CA_ON,CA_ON If that is not possible, or if that does not work, you would need to use a format file. There are examples of format files and description about how to create them here: http://msdn.microsoft.com/en-us/library/ms178129.aspx
Another alternative that you might want to consider is to use SSIS or the simple Import/Export Wizard. That will let you map the columns easily via a helpful dialog. |
 |
|
|
visakh16
Very Important crosS Applying yaK Herder
India
47173 Posts |
Posted - 02/11/2013 : 00:28:53
|
in any case your metedata ie number of columns and types have to be fixed. you can have an intermidiate , missing and expect it to pick up next set of columns correctly
------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
|
|
|
| |
Topic |
|
|
|
Bulk Insert field mapping
