now I finally got to write the rows of my csv (somewhat) into the table. It does add the 7 rows of the csv, but without data. The only thing it sets is the id, but that's autoincremented anyways. The data out of the csv doesn't arrive in the table.
static DataTable GetDataTableFromCsv(string path, bool isFirstRowHeader, string csvSelection)
{
string header= isFirstRowHeader ? "Yes" : "No";
string pathOnly = Path.GetDirectoryName(path);
string fileName = Path.GetFileName(path);
List<string> headerList = getHeader(fileName);
string sql = @"SELECT " + "*" + " FROM [" + fileName + "]";
//string sql = @"SELECT * FROM [" + fileName + "];";
using (OleDbConnection connection = new OleDbConnection(@"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + pathOnly + ";Extended Properties=\"Text;HDR=" + headerList + ";FMT=Delimited(;)\""))
using (OleDbCommand command = new OleDbCommand(sql, connection))
using (OleDbDataAdapter adapter = new OleDbDataAdapter(command))
{
DataTable dataTable = new DataTable();
dataTable.Locale = CultureInfo.CurrentCulture;
foreach (var item in headerList)
{
dataTable.Columns.Add(item);
}
adapter.Fill(dataTable);
writeSchema(dataTable.Columns, pathOnly, fileName);
return dataTable;
}
}
public static Boolean BatchBulkCopy(DataTable dt, string destTable, List<string> columnMapping, IBM.Data.DB2.DB2Connection connection, int batchSize)
{
DataTable dtInsertRows = dt;
using (IBM.Data.DB2.DB2BulkCopy sbc = new IBM.Data.DB2.DB2BulkCopy(connection, IBM.Data.DB2.DB2BulkCopyOptions.TableLock))
{
sbc.DestinationTableName = "\"" + destTable + "\"";
foreach (var mapping in columnMapping)
{
var split = mapping.Split(new[] { ',' });
sbc.ColumnMappings.Add("\"" + split.First() + "\"", "\"" + split.Last() + "\"");
}
try{
sbc.WriteToServer(dtInsertRows);
sbc.Close();
}
catch(Exception e){
System.Console.WriteLine(e.ToString());
}
}
connection.Close();
return false;
}
private static void writeSchema(DataColumnCollection columns, string csvDir, string csvFileName)
{
FileStream fsOutput =
new FileStream(csvDir + "\\schema.ini",
FileMode.Create, FileAccess.Write);
StreamWriter srOutput = new StreamWriter(fsOutput);
srOutput.WriteLine("[" + csvFileName + "]");
int i = 1;
foreach (DataColumn item in columns)
{
srOutput.WriteLine("Col" + i + "=\"" + item.ToString() + "\" Text");
i++;
}
srOutput.Close();
fsOutput.Close();
}
private static List<string> getHeader(string fileName)
{
string fullpath = @ImportPath + "\\" + fileName;
List<string> header = new List<string>();
var reader = new StreamReader(File.OpenRead(fullpath));
while (!reader.EndOfStream)
{
var line = reader.ReadLine();
var values = line.Split(';');
foreach (var spalte in values)
{
header.Add(spalte);
}
reader.Close();
return header;
}
return null;
}
}
