Getting Started with Adobe After Effects - Part 6: Motion Blur


Upload Image Close it
Select File

Browse by Tags · View All
sql_server 217
t-sql 211
tsql 116
sqlserver 96
BRH 78
#SQLServer 66
#TSQL 56
SQL Server 34
function 11
SSMS 9

Archive · View All
August 2007 17
August 2010 8
June 2012 7
June 2011 7
November 2007 7
August 2012 6
May 2012 6
November 2011 6
August 2011 6
October 2011 6

Madhivanan's TSQL Blog

Bulk insert - comma as part of data

Dec 15 2010 3:44AM by Madhivanan   

I have already blogged about the usage of BULK INSERT in this post

http://beyondrelational.com/blogs/madhivanan/archive/2010/03/17/bulk-insert-to-table-with-specific-columns.aspx

You can use BULK INSERT command to import data from a file to a table. However if the field seperator is a comma and data has comma as part of it, you may get an error. To avoid this, one method is to make use of OPENROWSET function Consider the following data

Davolio	Nancy,644
"Fuller	Andrew",2354
"Leverling	Janet",4834
"Peacock	Margaret",1056
"Buchanan	Steven",6893
"Suyama	Michael",8195
"King	Robert",75920
"Callahan	Laura",7003
"Dodsworth	,t"	,386

Consider the following table

create table test (names varchar(100),no int)

Now try to import data to the table using BULK INSERT command

BULK INSERT test
   FROM 'F:\testing.txt'
   WITH
     (
        FIELDTERMINATOR =',',
        ROWTERMINATOR = '\n'
      )

The error message is

Msg 4864, Level 16, State 1, Line 1
Bulk load data conversion error (type mismatch or invalid character for the specified codepage) for row 9, column 2 (no).

It is becuase the data "Dodsworth ,t" is treated as two columns which results to an error. If you use OPENROWSET function, data will be imported correctly

insert into test(names, no)
SELECT 
	* 
FROM 
	OPENROWSET('Microsoft.Jet.OLEDB.4.0','text;HDR=NO;FMT=FixedLength;Database=F:\', testing#txt)
	

Tags: t-sql, sql_server, csv, bulk_insert, sqlserver, tsql, BRH, SQL Server, #TSQL, #SQLServer,


Madhivanan
3 · 40% · 12898
1
 
0
Lifesaver
 
0
Refreshed
 
0
Learned
 
0
Incorrect



Submit

1  Comments  

  • What if i am using 64bit operating system?, Please include the series of steps in the same post that would be verymuch helpful to the readers

    commented on Feb 22 2012 1:37AM
    Kalyan
    270 · 1% · 163

Your Comment


Sign Up or Login to post a comment.

"Bulk insert - comma as part of data" rated 5 out of 5 by 1 readers
Bulk insert - comma as part of data , 5.0 out of 5 based on 1 ratings
    Copyright © Rivera Informatic Private Ltd Contact us      Privacy Policy      Terms of use      Report Abuse      Advertising      [ZULU1097]