I am trying to import a CSV file into an SQL Server table with the OleDbDataReader and SqlBulkCopy objects, like this:
using (OleDbConnection dconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\mystuff\\;Extended Properties=\"text;HDR=No;FMT=Delimited\""))
{
using (OleDbCommand dcmd = new OleDbCommand("select * from mytable.csv", dconn))
{
try
{
dconn.Open();
using (OleDbDataReader dreader = dcmd.ExecuteReader())
{
try
{
using (SqlConnection dconn2 = new SqlConnection(@"data source=MyDBServer;initial catalog=MyDB;user id=mydbid;password=mydbpwd"))
{
using (SqlBulkCopy bc = new SqlBulkCopy(dconn2))
{
try
{
dconn2.Open();
bc.DestinationTableName = "dbo.mytable";
bc.WriteToServer(dreader);
}
finally
{
dconn2.Close();
}
}
}
}
finally
{
dreader.Close();
}
}
}
finally
{
dconn.Close();
}
}
}
A couple of the columns for the destination table use a
bit datatype. The CSV files uses the strings "1" and "0" to represent these.
When I run this code, it throws this exception:
Unhandled Exception: System.InvalidOperationException: The given value of type S
tring from the data source cannot be converted to type bit of the specified targ
et column. ---> System.FormatException: Failed to convert parameter value from a
String to a Boolean. ---> System.FormatException: String was not recognized as
a valid Boolean.
at System.Boolean.Parse(String value)
at System.String.System.IConvertible.ToBoolean(IFormatProvider provider)
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvid
er provider)
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType dest
inationType)
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType dest
inationType)
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData
metadata)
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData
metadata)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount
)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
at MyClass.Main()
It appears not to accept "1" and "0" as valid strings to convert to booleans. The System.Convert.ToBoolean method appears to work the same way.
Is there any way to change this behavior? I discovered if you change the "1" to "true" and "0" to "false" in the CSV file it will accept them.
I believe this is due to the way that Microsoft Access uses numbers to represent bits. I think 0 is Yes and -1 is No. Try it with 0 and -1 instead of 1 and 0.
//WriteLog("LoadError", "Error while delete data from satging table", TraceEventType.Error);
//return "Error";
throw
new Exception("Error while delete data from satging table");
dmory
0 Points
3 Posts
Importing CSV files with SqlBulkCopy
May 02, 2007 02:02 PM|LINK
I am trying to import a CSV file into an SQL Server table with the OleDbDataReader and SqlBulkCopy objects, like this:
using (OleDbConnection dconn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:\\mystuff\\;Extended Properties=\"text;HDR=No;FMT=Delimited\"")) { using (OleDbCommand dcmd = new OleDbCommand("select * from mytable.csv", dconn)) { try { dconn.Open(); using (OleDbDataReader dreader = dcmd.ExecuteReader()) { try { using (SqlConnection dconn2 = new SqlConnection(@"data source=MyDBServer;initial catalog=MyDB;user id=mydbid;password=mydbpwd")) { using (SqlBulkCopy bc = new SqlBulkCopy(dconn2)) { try { dconn2.Open(); bc.DestinationTableName = "dbo.mytable"; bc.WriteToServer(dreader); } finally { dconn2.Close(); } } } } finally { dreader.Close(); } } } finally { dconn.Close(); } } }A couple of the columns for the destination table use a bit datatype. The CSV files uses the strings "1" and "0" to represent these.
When I run this code, it throws this exception:
Unhandled Exception: System.InvalidOperationException: The given value of type String from the data source cannot be converted to type bit of the specified targ
et column. ---> System.FormatException: Failed to convert parameter value from a
String to a Boolean. ---> System.FormatException: String was not recognized as
a valid Boolean.
at System.Boolean.Parse(String value)
at System.String.System.IConvertible.ToBoolean(IFormatProvider provider)
at System.Convert.ChangeType(Object value, Type conversionType, IFormatProvid
er provider)
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType dest
inationType)
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlParameter.CoerceValue(Object value, MetaType dest
inationType)
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData
metadata)
--- End of inner exception stack trace ---
at System.Data.SqlClient.SqlBulkCopy.ConvertValue(Object value, _SqlMetaData
metadata)
at System.Data.SqlClient.SqlBulkCopy.WriteToServerInternal()
at System.Data.SqlClient.SqlBulkCopy.WriteRowSourceToServer(Int32 columnCount
)
at System.Data.SqlClient.SqlBulkCopy.WriteToServer(IDataReader reader)
at MyClass.Main()
It appears not to accept "1" and "0" as valid strings to convert to booleans. The System.Convert.ToBoolean method appears to work the same way.
Is there any way to change this behavior? I discovered if you change the "1" to "true" and "0" to "false" in the CSV file it will accept them.
ask_Scotty
Contributor
4156 Points
707 Posts
Re: Importing CSV files with SqlBulkCopy
May 05, 2007 08:08 PM|LINK
Hello my friend,
I believe this is due to the way that Microsoft Access uses numbers to represent bits. I think 0 is Yes and -1 is No. Try it with 0 and -1 instead of 1 and 0.
Kind regards
Scotty
dmory
0 Points
3 Posts
Re: Importing CSV files with SqlBulkCopy
May 09, 2007 04:52 PM|LINK
rsadalkar
Member
4 Points
2 Posts
Re: Importing CSV files with SqlBulkCopy
Nov 13, 2008 11:36 AM|LINK
I use same method to move data from CSV file SQL tables. Data with single quote ( ' ) not transferd. How should i handle this please suggest.
try
{
int filelen = FileName.Length; string DBObjectName = string.Empty; if (string.Equals(TableName, "ccar")){
DBObjectName = "dbo.CCAR_Staging";}
else if (string.Equals(TableName, "chbalances")){
DBObjectName = "dbo.chbalances_Staging";}
string SQL = "select * from " + FileName;using (OleDbConnection dconn = new OleDbConnection(ConfigurationSettings.AppSettings["UploadProvider"].ToString() + "Extended Properties=\"text;HDR=No;FMT=Delimited\"")){
using (OleDbCommand dcmd = new OleDbCommand(SQL, dconn)){
try{
dconn.Open();
using (OleDbDataReader dreader = dcmd.ExecuteReader()){
try{
using (SqlConnection dconn2 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings[Constants.DATABASE].ConnectionString)){
using (SqlBulkCopy bc = new SqlBulkCopy(dconn2)){
try{
string ErrDesc = DeleteStagingData(DBObjectName);if (ErrDesc != null){
//WriteLog("LoadError", "Error while delete data from satging table", TraceEventType.Error); //return "Error"; throw new Exception("Error while delete data from satging table");}
else{
dconn2.Open();
bc.DestinationTableName = DBObjectName;
bc.WriteToServer(dreader);
}
}
catch (Exception ex){
// WriteLog(ex.Message, "UploadData-BulkCopy", TraceEventType.Error); return GetErrorMsg("EC102", ex.Message);}
finally{
dconn2.Close();
}
}
}
}
catch (Exception ex){
// WriteLog(ex.Message, "UploadData-Reading file", TraceEventType.Error); return GetErrorMsg("EC102", ex.Message);}
finally{
dreader.Close();
}
}
}
catch (Exception ex){
// WriteLog(ex.Message, "UploadData-Reading file", TraceEventType.Error); return GetErrorMsg("EC102", ex.Message);}
finally{
dconn.Close();
}
}
}
}
catch (Exception ex){
// WriteLog(ex.Message, "UploadData method", TraceEventType.Error); return GetErrorMsg("EC102", ex.Message);}
return null;}
public string ValidateSchema(String FileName){
try{
using (FileStream fileStream = File.Open(FileName, FileMode.Open)){
using (StreamReader fileReader = new StreamReader(fileStream)){
string line = fileReader.ReadLine(); string[] fields = line.Split(','); if (fields.Length > 1){
string xml = BuildXmlString("Category", fields);DataSet ds=new DataSet();ds=GetColumnMapping(xml);
if (ds.Tables[0].Rows.Count > 0){
string Count = ds.Tables[0].Rows[0][0].ToString(); if (int.Parse(Count)<18){
throw new Exception("Invalid Format");}
}
else{
throw new Exception("Invalid Format");}
}
else{
throw new Exception("Invalid Format");}
}
}
}
catch (Exception ex){
return GetErrorMsg("EC101", ex.Message);}
return null;