Skip to content

Instantly share code, notes, and snippets.

@growse
Created November 10, 2011 14:50
Show Gist options
  • Star 0 You must be signed in to star a gist
  • Fork 0 You must be signed in to fork a gist
  • Save growse/1355020 to your computer and use it in GitHub Desktop.
Save growse/1355020 to your computer and use it in GitHub Desktop.
Bulk copy SQL from GNU splitted Postgres CSV output into MSSQL with IDENTITY_INSERT
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
namespace ARGHGHGHGHG
{
internal class Program
{
static int progress = 0;
private static int total = 0;
private const int batchsize = 1000;
private static void Main()
{
const string filename = "c:\\stuff\\x{0}.csv";
Console.WriteLine("hi");
using (var dbconn = new SqlConnection("string"))
{
dbconn.Open();
var bulk = new SqlBulkCopy(dbconn, SqlBulkCopyOptions.KeepIdentity, null) { DestinationTableName = "perflog", NotifyAfter = batchsize, BatchSize = batchsize };
bulk.SqlRowsCopied += bulk_SqlRowsCopied;
for (var x = 0; x <= 9; x++)
{
var currfilename = string.Format(filename, x.ToString("00"));
Console.WriteLine("Reading Data from {0}", currfilename);
var datatable = CsvReader.GetDataTable(currfilename);
total = datatable.Rows.Count;
Console.WriteLine("Done. {0} rows", total);
bulk.WriteToServer(datatable);
}
}
}
static void bulk_SqlRowsCopied(object sender, SqlRowsCopiedEventArgs e)
{
progress += batchsize;
Console.WriteLine("Done {0} out of {1}", progress, total);
}
}
class CsvReader
{
public static DataTable GetDataTable(string filename)
{
var conn = new System.Data.OleDb.OleDbConnection(string.Concat("Provider=Microsoft.Jet.OleDb.4.0; Data Source = ", Path.GetDirectoryName(filename), "; Extended Properties = \"Text;HDR=YES;FMT=Delimited\""));
conn.Open();
var strQuery = string.Concat("SELECT * FROM [", Path.GetFileName(filename), "]");
var adapter = new System.Data.OleDb.OleDbDataAdapter(strQuery, conn);
var dataset = new DataSet("CSV File");
adapter.Fill(dataset);
return dataset.Tables[0];
}
}
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment