Author |
Topic |
salmab
Starting Member
3 Posts |
Posted - 2013-02-10 : 20:13:22
|
Hi, I'm trying to do a bulk insert.Here is my sql scriptGOUse testDROP TABLE PDTESTcreate 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 pdtestFROM 'C:\Python27\Scripts\pd.csv'WITH(FIELDTERMINATOR = ',',ROWTERMINATOR = '\n', KEEPNULLS,FIRSTROW = 2)GOHere is my textfile row,91234,,,,WOLFIE HOWLETT,416-,Toronto,,,,,Toronto,CA_ON,CA_ONAll 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
Master Smack Fu Yak Hacker
3873 Posts |
Posted - 2013-02-10 : 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.aspxAnother 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
52326 Posts |
Posted - 2013-02-11 : 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 MVPhttp://visakhm.blogspot.com/ |
|
|
|
|
|