Bulk Insert
-
Hi, Need to insert only specific columns from a .csv file while importing to SQL Server2005 using bulk import. My problem is i have 4 columns a,b,c,d in my .csv file,but i need only two columns a and b only to be imported using bulk import. I was working after seeing this link http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/ Any help is welcome Thanking you
-
Hi, Need to insert only specific columns from a .csv file while importing to SQL Server2005 using bulk import. My problem is i have 4 columns a,b,c,d in my .csv file,but i need only two columns a and b only to be imported using bulk import. I was working after seeing this link http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/ Any help is welcome Thanking you
-
Hi, Thanks for the reply. I have tried it from a similar link from msdn.But i am facing a problem. I followed this link to the tee. But i am getting an error;I worked this way First i created a database by the name "AdventureWorks". Then i creted a table like ; USE AdventureWorks; GO CREATE TABLE myTestSkipCol ( Col1 smallint, Col2 nvarchar(50) NULL, Col3 nvarchar(50) not NULL ); GO I created a .dat file by the name "myTestSkipCol2.dat" 1,DataForColumn3 1,DataForColumn3 1,DataForColumn3 Then i copied the XML file by the name "myTestSkipCol2.Xml" in the C:Drive Then i executed the bulk query like; USE AdventureWorks; GO INSERT INTO myTestSkipCol (Col1,Col3) SELECT Col1,Col3 FROM OPENROWSET(BULK 'C:\myTestSkipCol2.Dat', FORMATFILE='C:\myTestSkipCol2.Xml' ) as t1 ; GO Here is the problem;I am getting an error "Msg 4820, Level 16, State 1, Line 1 Could not bulk insert. Unknown version of format file 'C:\nm.fmt'. " I tried different ways like giving the extension as ".XML" and ".ftm".I tried different permutations and combinations. Need your help Many Thanks
-
Hi, Thanks for the reply. I have tried it from a similar link from msdn.But i am facing a problem. I followed this link to the tee. But i am getting an error;I worked this way First i created a database by the name "AdventureWorks". Then i creted a table like ; USE AdventureWorks; GO CREATE TABLE myTestSkipCol ( Col1 smallint, Col2 nvarchar(50) NULL, Col3 nvarchar(50) not NULL ); GO I created a .dat file by the name "myTestSkipCol2.dat" 1,DataForColumn3 1,DataForColumn3 1,DataForColumn3 Then i copied the XML file by the name "myTestSkipCol2.Xml" in the C:Drive Then i executed the bulk query like; USE AdventureWorks; GO INSERT INTO myTestSkipCol (Col1,Col3) SELECT Col1,Col3 FROM OPENROWSET(BULK 'C:\myTestSkipCol2.Dat', FORMATFILE='C:\myTestSkipCol2.Xml' ) as t1 ; GO Here is the problem;I am getting an error "Msg 4820, Level 16, State 1, Line 1 Could not bulk insert. Unknown version of format file 'C:\nm.fmt'. " I tried different ways like giving the extension as ".XML" and ".ftm".I tried different permutations and combinations. Need your help Many Thanks
How can you get the error:
new2pgrmg wrote:
Could not bulk insert. Unknown version of format file 'C:\nm.fmt'.
if your statement is:
new2pgrmg wrote:
INSERT INTO myTestSkipCol (Col1,Col3) SELECT Col1,Col3 FROM OPENROWSET(BULK 'C:\myTestSkipCol2.Dat', FORMATFILE='C:\myTestSkipCol2.Xml' ) as t1 ;
Your query references a format file called myTestSkipCol2.Xml. This isn't consistent with the error message you reported.
Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
-
How can you get the error:
new2pgrmg wrote:
Could not bulk insert. Unknown version of format file 'C:\nm.fmt'.
if your statement is:
new2pgrmg wrote:
INSERT INTO myTestSkipCol (Col1,Col3) SELECT Col1,Col3 FROM OPENROWSET(BULK 'C:\myTestSkipCol2.Dat', FORMATFILE='C:\myTestSkipCol2.Xml' ) as t1 ;
Your query references a format file called myTestSkipCol2.Xml. This isn't consistent with the error message you reported.
Paul Marfleet "No, his mind is not for rent To any God or government" Tom Sawyer - Rush
Sorry for the mistake but the basic idea is correct.As i told you i was trying different methods and when i found that the posted question had a slight mistake i tried to edit but it was not loading. The error will be Could not bulk insert. Unknown version of format file 'C:\myTestSkipCol2.Xml'. Sorry for the mistake Looking for an answer. Also can you tell me how i can find the BCP version? Many Thanks