csv file import: Bulk insert data conversion error (type mismatch)

Hi,

Iam trying to import data from a csv file into my table in SQL Server 2000. My table is called as temp_table and consists of 3 fields.
column datatype
-------- -----------
program nvarchar(20)
description nvarchar(50)
pId int
pId has been set to primary key with auto_increment.
My csv file has 2 columns of data and it looks like follows:
program, description
"prog1", "this is program1"
"prog2", "this is program2"
"prog3", "this is program3"

Now i use BULK INSERT like this
"BULK INSERT ord_programs FROM 'C:\datafile.csv' WITH (FIELDTERMINATOR=',', ROWTERMINATOR='\n', FIRSTROW=2)"
to import data into my table in SQL server and it gives me this error
"Bulk insert data conversion error (type mismatch) for row 2, column 3 (pId)"
I guess i have to use fileformat or something since i dont have anything for pId field in the csv file to make it work...
Please help me out guys and please post a snippet of code if you have.
Thank You.
0
dn_learner
9/27/2004 4:27:43 AM
asp.net.sql-datasource 29906 articles. 0 followers. Follow

2 Replies
601 Views

Similar Articles

[PageSpeed] 55

BULK INSERT

Copies a data file into a database table or view in a user-specified format.
Syntax
BULK INSERT [ [ 'database_name'.] [ 'owner' ].] { 'table_name' FROM 'data_file' }
[ WITH
(
[ BATCHSIZE [ = batch_size ] ]
[ [ , ] CHECK_CONSTRAINTS ]
[ [ , ] CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ] ]
[ [ , ] DATAFILETYPE [ =
{ 'char' | 'native'| 'widechar' | 'widenative' } ] ]
[ [ , ] FIELDTERMINATOR [ = 'field_terminator' ] ]
[ [ , ] FIRSTROW [ = first_row ] ]
[ [ , ] FIRE_TRIGGERS ]
[ [ , ] FORMATFILE = 'format_file_path' ]
[ [ , ] KEEPIDENTITY ]
[ [ , ] KEEPNULLS ]
[ [ , ] KILOBYTES_PER_BATCH [ = kilobytes_per_batch ] ]
[ [ , ] LASTROW [ = last_row ] ]
[ [ , ] MAXERRORS [ = max_errors ] ]
[ [ , ] ORDER ( { column [ ASC | DESC ] } [ ,...n ] ) ]
[ [ , ] ROWS_PER_BATCH [ = rows_per_batch ] ]
[ [ , ] ROWTERMINATOR [ = 'row_terminator' ] ]
[ [ , ] TABLOCK ]
)
]
Arguments
'database_name'
Is the database name in which the specified table or view resides. If not specified, this is the current database.
'owner'
Is the name of the table or view owner. owner is optional if the user performing the bulk copy operation owns the specified table or view. If owner is not specified and the user performing the bulk copy operation does not own the specified table or view, Microsoft® SQL Serverâ„¢ returns an error message, and the bulk copy operation is canceled.
'table_name'
Is the name of the table or view to bulk copy data into. Only views in which all columns refer to the same base table can be used. For more information about the restrictions for copying data into views, see INSERT.
'data_file'
Is the full path of the data file that contains data to copy into the specified table or view. BULK INSERT can copy data from a disk (including network, floppy disk, hard disk, and so on).
data_file must specify a valid path from the server on which SQL Server is running. If data_file is a remote file, specify the Universal Naming Convention (UNC) name.
BATCHSIZE [ = batch_size ]
Specifies the number of rows in a batch. Each batch is copied to the server as one transaction. SQL Server commits or rolls back, in the case of failure, the transaction for every batch. By default, all data in the specified data file is one batch.
CHECK_CONSTRAINTS
Specifies that any constraints on table_name are checked during the bulk copy operation. By default, constraints are ignored.
CODEPAGE [ = 'ACP' | 'OEM' | 'RAW' | 'code_page' ]
Specifies the code page of the data in the data file. CODEPAGE is relevant only if the data contains char, varchar, or text columns with character values greater than 127 or less than 32.
CODEPAGE value Description
ACP Columns of char, varchar, or text data type are converted from the ANSI/Microsoft Windows® code page (ISO 1252) to the SQL Server code page.
OEM (default) Columns of char, varchar, or text data type are converted from the system OEM code page to the SQL Server code page.
RAW No conversion from one code page to another occurs; this is the fastest option.
code_page Specific code page number, for example, 850.

DATAFILETYPE [ = {'char' | 'native' | 'widechar' | 'widenative' } ]
Specifies that BULK INSERT performs the copy operation using the specified default.
DATAFILETYPE value Description
char (default) Performs the bulk copy operation from a data file containing character data.
native Performs the bulk copy operation using the native (database) data types. The data file to load is created by bulk copying data from SQL Server using the bcp utility.
widechar Performs the bulk copy operation from a data file containing Unicode characters.
widenative Performs the same bulk copy operation as native, except char, varchar, and text columns are stored as Unicode in the data file. The data file to be loaded was created by bulk copying data from SQL Server using the bcp utility. This option offers a higher performance alternative to the widechar option, and is intended for transferring data from one computer running SQL Server to another by using a data file. Use this option when transferring data that contains ANSI extended characters in order to take advantage of native mode performance.

FIELDTERMINATOR [ = 'field_terminator' ]
Specifies the field terminator to be used for char and widechar data files. The default is \t (tab character).
FIRSTROW [ = first_row ]
Specifies the number of the first row to copy. The default is 1, indicating the first row in the specified data file.
FIRE_TRIGGERS
Specifies that any insert triggers defined on the destination table will execute during the bulk copy operation. If FIRE_TRIGGERS is not specified, no insert triggers will execute.
FORMATFILE [ = 'format_file_path' ]
Specifies the full path of a format file. A format file describes the data file that contains stored responses created using the bcp utility on the same table or view. The format file should be used in cases in which:
The data file contains greater or fewer columns than the table or view.

The columns are in a different order.

The column delimiters vary.

There are other changes in the data format. Format files are usually created by using the bcp utility and modified with a text editor as needed. For more information, see bcp Utility.
KEEPIDENTITY
Specifies that the values for an identity column are present in the file imported. If KEEPIDENTITY is not given, the identity values for this column in the data file imported are ignored, and SQL Server automatically assigns unique values based on the seed and increment values specified during table creation. If the data file does not contain values for the identity column in the table or view, use a format file to specify that the identity column in the table or view should be skipped when importing data; SQL Server automatically assigns unique values for the column. For more information, see DBCC CHECKIDENT.
KEEPNULLS
Specifies that empty columns should retain a null value during the bulk copy operation, rather than have any default values for the columns inserted.
KILOBYTES_PER_BATCH [ = kilobytes_per_batch ]
Specifies the approximate number of kilobytes (KB) of data per batch (as kilobytes_per_batch). By default, KILOBYTES_PER_BATCH is unknown.
LASTROW [ = last_row ]
Specifies the number of the last row to copy. The default is 0, indicating the last row in the specified data file.
MAXERRORS [ = max_errors ]
Specifies the maximum number of errors that can occur before the bulk copy operation is canceled. Each row that cannot be imported by the bulk copy operation is ignored and counted as one error. If max_errors is not specified, the default is 10.
ORDER ( { column [ ASC | DESC ] } [ ,...n ] )
Specifies how the data in the data file is sorted. Bulk copy operation performance is improved if the data loaded is sorted according to the clustered index on the table. If the data file is sorted in a different order, or there is no clustered index on the table, the ORDER clause is ignored. The column names supplied must be valid columns in the destination table. By default, the bulk insert operation assumes the data file is unordered.
n
Is a placeholder indicating that multiple columns can be specified.
ROWS_PER_BATCH [ = rows_per_batch ]
Specifies the number of rows of data per batch (as rows_per_batch). Used when BATCHSIZE is not specified, resulting in the entire data file sent to the server as a single transaction. The server optimizes the bulk load according to rows_per_batch. By default, ROWS_PER_BATCH is unknown.
ROWTERMINATOR [ = 'row_terminator' ]
Specifies the row terminator to be used for char and widechar data files. The default is \n (newline character).
TABLOCK
Specifies that a table-level lock is acquired for the duration of the bulk copy operation. A table can be loaded concurrently by multiple clients if the table has no indexes and TABLOCK is specified. By default, locking behavior is determined by the table option table lock on bulk load. Holding a lock only for the duration of the bulk copy operation reduces lock contention on the table, significantly improving performance.
Remarks
The BULK INSERT statement can be executed within a user-defined transaction. Rolling back a user-defined transaction that uses a BULK INSERT statement and BATCHSIZE clause to load data into a table or view using multiple batches rolls back all batches sent to SQL Server.
Permissions
Only members of the sysadmin and bulkadmin fixed server roles can execute BULK INSERT.
Examples
This example imports order detail information from the specified data file using a pipe (|) as the field terminator and |\n as the row terminator.
BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = '|\n'
)
This example specifies the FIRE_TRIGGERS argument.
BULK INSERT Northwind.dbo.[Order Details]
FROM 'f:\orders\lineitem.tbl'
WITH
(
FIELDTERMINATOR = '|',
ROWTERMINATOR = ':\n',
FIRE_TRIGGERS
)

eMale ;)
0
asp
2/11/2005 12:17:41 PM
if you go to Sql Server Books Online

and search for Bulk Insert you will get the complete reference as i pasted in last post.
Further more there is a whole section on Data Transformation Services in Sql Server 2000 Books Online.
I hope that would be a great help for you.

eMale ;)
0
asp
2/11/2005 12:22:15 PM
Reply:

Similar Artilces:

Bulk insert .csv with an identity column in SQL but not in .csv file?
Hi all,I have a table with an identity column, auto increase and the format like below:SqlID (identity/primary key)FirstNameLastNameEmailI want to Bulk Insert a .csv file with the following format:FirstName, LastName, EmailWhat do I need to set so that the column in my table matches the .csv file?Thank you very much.  I've used the Server Import/Export Wizard to import the .csv content and everything seems to work fine.  ...

Insert CSV file data to MS Sql
How to upload file data into ms sql server? thanks You could do an INSERT INTO, BCP(bulk copy) or use DTS, try the link below for sample DTS code.  Hope this helps.http://www.sqldts.comKind regards,Gift Peddie Hi, My problem is i have to download one csv file csv daily(filename with date stamp) from FTP server and import data to Database. if it is one time then DTS is ok. Thanks, Manoj DTS on xp_cmdshell can be scheduled with a Job to run 24/7  but you can just do manual INSERT INTO.  And there is no Time Stamp in SQL Server.  Hope this helps.Kin...

Error : The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
 My website is facing one problem after hosting to the server.When i execute my code from local(india) all working fine.But when i host it to UK,the error coming "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value" . I used Culture=en-GB in web.config.But not solved.This error is coming to all pages where datetime value is using..Please help me ..  Can you post your code or screenshot showing the error on some code line...  Thanks,santosh_maharajaPlease mark as answer if you got expected solution.  this is ...

How to insert data into sql server in bulk using ADO.net
Hi!  I'm building a web application. I need to read data from a text or excel file and process the data and then store the result records into database. The record number is big. I can store the data record into database (SQL Server 2005) one at a time. I think it's slow. Is there any way to insert the data in bulk.   Thanks! ccy Hi, Try DataAdapter's Update() method. Thanks and best regards,Faraz Shah KhanMCP, MCAD.Net, MCSD.Net, MCTS-Win/Web, MCPD-WebBlog HI, If you want to just insert the data then you can use sqlbulkinsert class. If you want to update a...

getting error : : The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value
update tblPact_2008_0307 set student_dob = '30/01/1996' where student_rcnumber = 1830when entering update date in format such as ddmmyyyyi know the sql query date format entered should be in mmddyyyy formatis there any way to change the date format entered to ddmmyyyy in sql query? Before the update execute: SET DATEFORMAT dmyPlease remember to mark the replies as answers if they help and unmark them if they provide no help. everytime i do a query on date format ddmmyyyy, i need to do a set dateformat dmy?is there anyway to configure the sql server to set the dateformat in dmy as...

Error:"The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value."
I still don't know what is happening with my application. I used before in another page the formview control and I didn't have the out of range Date error. It was normally. But now, I'm doing everything manually and I don't know why, but I'm with this problem. I need to safe this problem today... Here go my code! The error occurs here: (It's the Sub that Insert data inside of the database(SQLServer)Protected Sub Gravar_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim SQL As String = "Update RECEBER SET  EMISSAO = '" & CType(tbDataEmissao.Text, Date) & "', VENCIME...

Data Type Mismatch Error on Insert (Please Help ASAP) Thanks
I am running the following code and keep getting a data type mismatch error, the only thing I can think of is the inventorynumber and donornumber are numeric values in the access database. What do I do when I pull it from the text box to make it numeric.??? Thank you from a newbie.... here is the code.. Dim ConnectionString As String = "Provider=Microsoft.Jet.OLEDB.4.0; Ole DB Services=-4; Data Source=c:/websites/phillwebhost/cor/database/corauction.mdb" Dim dbConnection As System.Data.IDbConnection = New System.Data.OleDb.OleDbConnection(connectionString) ...

Is possible to import data from CSV file into database using c# .net
Hi,  I am using visual studio.net 2005 and mysql server (5.0 .net connector).I want to load data from a csv file and set them in database table. The CSV data are not loaded into database table, I am getting error in this line 13: cmd1.ExecuteNonQuery(); Also I have tried to create a stored procedure to load data and send the file path as parameter, but it is not impossible? Please a help..... my code: 1 string strCSVFile = "C:\\Import.csv"; 2 MySqlConnection conn1 = new MySqlConnection(connectionString); 3 4 string strLoadData = "LOAD D...

read data from a csv file and insert it in a sql server databse table
Hai Everbody,            for me in my project i want to read data from a csv file and insert it in a sql server databse table.The csv file may contain n number of columns,but i want only certain columns from that, and insert it in the database table.How to achieve this. Plz help me it is urgent. Thanks in advance.         Thanks and regards                Biju.S.G http://www.dotnetspider.com/kb/Article1082.aspx http://www.devarticles...

Error: Data type mismatch in criteria expression. Dealing with Insert statement
Every time I try enter this into the access database, I get the error    Data type mismatch in criteria expression. And it only seems to happen when I try to add "restore," when I dont' have this it works better. Restore is set to Yes/No in the access database. Do all of the field need to have something inserted even if I  want the field left blank. //you have to put square bracket around reserved words..."size" is a reserved word.     objCmd = new OleDbCommand("INSERT INTO backup (jobNum, [size], urgency, restore) VALUES (@jobNum, @size, @urge...

How to read a remote CSV file into SQL SERVER using bulk insert Command..
Hi, I have load a CSV file into one of the table in sql server 2005 using bulk insert command. But the csv file in remote system. Please help me.....  I guess its not possible. Faced similar problem couple of weeks back. We ended up creating a webservice to transfer the file to sql server and then did the bulkupload. Save our world, its all we have! A must watch video Pale Blue Dot Please use the search feature of the forum before asking a question. ...

SQL 305 Error on SubmitChanges when inserting into table with XML data type with LINQ
I am trying to do simple insert using LINQ to a table that has an XML Data Type column, column allows null, so I do not assign any value to this columnMsg 305, Level 16, State 1, Line 2The xml data type cannot be compared or sorted, except when using the IS NULL operator.here's the code:Dim newReqService As New ReqServicenewReqService.ServiceID = 1newReqService.ReqID = 1db.ReqServices.InsertOnSubmit(newReqService)db.SubmitChanges()here's the sql statement that LINQ producesexec sp_executesql N'INSERT INTO [dbo].[ReqServices]([ReqID], [ServiceID], [DateCreated], [DateLastUpdated],...

LINQ genereates SQL 305 Error on SubmitChanges when inserting into table with XML data type
I am trying to do simple insert using LINQ to a table that has an XML Data Type column, column allows null, so I do not assign any value to this columnMsg 305, Level 16, State 1, Line 2The xml data type cannot be compared or sorted, except when using the IS NULL operator.here's the code:Dim newReqService As New ReqServicenewReqService.ServiceID = 1newReqService.ReqID = 1db.ReqServices.InsertOnSubmit(newReqService)db.SubmitChanges()here's the sql statement that LINQ producesexec sp_executesql N'INSERT INTO [dbo].[ReqServices]([ReqID], [ServiceID], [DateCreated], [DateLastUpdated],...

LINQ BUGG, SQL 305 Error on SubmitChanges when inserting into table with XML data type
I am trying to do simple insert using LINQ to a table that has an XML Data Type column, column allows null, so I do not assign any value to this columnMsg 305, Level 16, State 1, Line 2The xml data type cannot be compared or sorted, except when using the IS NULL operator.here's the code:Dim newReqService As New ReqServicenewReqService.ServiceID = 1newReqService.ReqID = 1db.ReqServices.InsertOnSubmit(newReqService)db.SubmitChanges()here's the sql statement that LINQ producesexec sp_executesql N'INSERT INTO [dbo].[ReqServices]([ReqID], [ServiceID], [DateCreated], [DateLastUpdated],...

Web resources about - csv file import: Bulk insert data conversion error (type mismatch) - asp.net.sql-datasource

Gene conversion - Wikipedia, the free encyclopedia
Gene conversion is the process by which one DNA sequence replaces a homologous sequence such that the sequences become identical after the conversion ...

Family of Enrique Marquez unaware of his 2014 marriage, conversion to Islam
The family members of Enrique Marquez, the man linked to the San Bernardino massacre, said they were unaware he was married or that he had converted ...

Effective Strategies for Increasing E-Commerce Conversions
... tips for improving your ecommerce strategy and related outcomes. Social signals are a powerful tool for engaging users and increasing conversion ...

Christmas Verse: A tale of conversion or, as Christians view it, re-birth
The Journey of the Magi, T. S. Eliot 'A cold coming we had of it, Just the worst time of the year For a journey, and such a long journey: The ...

Revisiting Carlos Martinez's rotation conversion
... thanks to his pitch mix, then it sure sounded like it could be possible. Now, almost a full year later, I wanted to revisit Martinez's conversion. ...

Dow to Force Conversion of Buffett Preferreds If Rally Holds
Bloomberg Dow to Force Conversion of Buffett Preferreds If Rally Holds Bloomberg Dow Chemical Co., which has been paying $255 million annually ...

​Judge orders N.J. "gay conversion" nonprofit to close
Jews Offering New Alternatives for Healing, known as JONAH, ordered to cease operations within 30 days

Malaysian mother loses court battle over conversion of children to Islam
A kindergarten teacher in Malaysia is to appeal to a higher court after she lost a case challenging the conversion of all her three children ...

Council votes to ban gay 'conversion' therapy in Cincinnati
Cincinnati follows four states and the District of Columbia banning the therapy, becoming the first city outside D.C. to do so.

Solar Frontier Pushes CIS (aka CIGS) Thin Film Solar Envelope With New Conversion Record, Factory Model ...
Japan's Solar Frontier comes up with a killer combo, a new low cost thin film solar manufacturing model and a new solar conversion efficiency ...

Resources last updated: 1/18/2016 3:13:23 AM