BULK INSERT in MS SQL SERVER

In IT environment, it is always a necessary to import data from data files into database.

And recently, I have to deal with import a huge load of data (around 200 MB for 1 CSV input file) from CSV files into MS SQL SERVER. So what I had done to achieve this is by using BULK INSERT.

Firstly, prepare a CSV data file with the following content and save as test.csv.
July,Singapore,25
James,Australia,50
May,China,29

And then run scripts as following to load all the data from the CSV file into database.

--Temp table to store the data
CREATE TABLE CSVTest
(
   Name VARCHAR(500),
   Country VARCHAR(500),
   Age INT
)
GO

--Bulk insert into CSVTest
BULK INSERT CSVTest
FROM 'C:\test.csv'
WITH
(
   FIELDTERMINATOR=',',
   ROWTERMINATOR = '\n'
)
GO

--Get all data from CSVTest
SELECT *
FROM CSVTest
GO

--Drop the temp table
DROP TABLE CSVTest
GO

Kill Excel.exe Process in C# .NET

I am trying to get my C# .NET codes to generate reports into excel files. In the Export function, it creates the excel object, adds in the data and then saves the file.

All of this works fine. But for some reason, the excel.exe process is still running when I check from task manager.

I had tried to quit the excel application. I had tried to use InteropServices.Marshal.ReleaseComObject to get rid of them in .NET. But unfortunately, the excel.exe is still sitting in memory.

Finally, I found a way to kill the process in C# .NET. Take a look on the codes below:


using System.Diagnostics;
using System.Collections;
…
…
…

Hashtable myHashtable;
private void btnExport_Click(object sender, EventArgs e)
{
  // get process ids before running the excel codes
  CheckExcellProcesses();

  // export to excel
  ExportDataToExcel();

  // kill the right process after export completed
  KillExcel();
}

private void ExportDataToExcel()
{
  // your export process is here...
}

private void CheckExcellProcesses()
{
  Process[] AllProcesses = Process.GetProcessesByName("excel");
  myHashtable = new Hashtable();
  int iCount = 0;

  foreach ( Process ExcelProcess in AllProcesses) {
    myHashtable.Add(ExcelProcess.Id, iCount);
    iCount = iCount + 1;
  }
}

private void KillExcel()
{
  Process[] AllProcesses = Process.GetProcessesByName("excel");

   // check to kill the right process
  foreach ( Process ExcelProcess in AllProcesses) {
    if (myHashtable.ContainsKey(ExcelProcess.Id) == false)
       ExcelProcess.Kill();
  }

  AllProcesses = null;
}


Hope this helps.