SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Bulk Insert field mapping
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

salmab
Starting Member

3 Posts

Posted - 02/10/2013 :  20:13:22  Show Profile  Reply with Quote
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

3724 Posts

Posted - 02/10/2013 :  21:44:42  Show Profile  Reply with Quote
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.
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52325 Posts

Posted - 02/11/2013 :  00:28:53  Show Profile  Reply with Quote
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/

Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.03 seconds. Powered By: Snitz Forums 2000