0

I'd like to insert CSV data into a SQL Server database at one time. I know about BULK INSERT but I need to select some fields only. So I try INSERT INTO like below -

try
{
   OleDbConnection myOleDbConnection = new OleDbConnection("Provider=SQLOLEDB;Data Source=ServerName;Integrated Security=SSPI;Initial Catalog=DBName;User ID=sa;Password=password");

   myOleDbConnection.Open();

   string strSQL = null;
   strSQL = "INSERT INTO " + strTable + "(" + M_ItemCode + "," + M_ItemDesc + "," + M_UOM + ") " + "SELECT " + M_ItemCode + "," + M_ItemDesc + "," + M_UOM + " FROM [Text;DATABASE=E:\temp\\Item.csv]" ;

   OleDbCommand cmd = new OleDbCommand(strSQL, myOleDbConnection);
   return (cmd.ExecuteNonQuery() == 1);
}
catch (Exception ex)
{
    Common.ShowMSGBox(ex.Message, Common.gCompanyTitle, Common.iconError);
    return false;
}

I got the error

Invalid object name 'Text;DATABASE=E:\temp\Item.csv'.

Is my syntax wrong?

Community
  • 1
  • 1
soclose
  • 2,773
  • 12
  • 51
  • 60
  • 2
    why are you doing it through code? sql server has built in feature to do this stuff for you. See this thread for details http://stackoverflow.com/questions/3446706/import-selected-columns-from-a-csv-files-to-sql-server-table – Ehsan Jul 12 '13 at 07:33
  • also look into format files for bulk import. here is some info: http://msdn.microsoft.com/en-us/library/ms178129.aspx – John Faulkner Jul 12 '13 at 07:56
  • i use SqlBulkCopy ColumnMappings. – soclose Jul 16 '13 at 00:48

0 Answers0