public static bool HandleFile(string file, string delimiter)
{
string FileNameExtension = Path.GetExtension(file);
string FileName = Path.GetFileNameWithoutExtension(file);
string FullPath = Path.GetDirectoryName(file);
Console.WriteLine("FileName: {0}", FileName);
Console.WriteLine("File-type: {0}", FileNameExtension);
Console.WriteLine("Full path: {0}", FullPath);
if (FileNameExtension == ".xls" || FileNameExtension == ".xlsx")
{
Console.WriteLine("File is Excel-format - converting Excel to CSV");
string csvoutput = Path.ChangeExtension(file, ".csv");
delimiter = "|";
ExcelFileHelper.SaveAsCsv(file, csvoutput, delimiter);
Console.WriteLine("Deletes Excel file");
File.Delete(file);
}
Console.WriteLine("Processing CSV-file and generating XML-formatfile");
var doc = GenerateXML.GenerateXMLFile(FullPath, "\\" + FileName + ".csv", delimiter);
StreamWriter xmlfile = File.CreateText(FullPath + "\\formatfile_" + FileName + ".xml");
doc.Save(xmlfile);
xmlfile.Close();
if (GenerateFormatFile.Properties.Settings.Default.loadToSQL)
{
Console.WriteLine("LoadToSQL is activated - loading file to SQL Server");
LoadToSQL.CSVFile(
GenerateFormatFile.Properties.Settings.Default.servername
, GenerateFormatFile.Properties.Settings.Default.databasename
, GenerateFormatFile.Properties.Settings.Default.username
, GenerateFormatFile.Properties.Settings.Default.password
, FullPath + "\\" + FileName + ".csv");
}
Console.WriteLine("Delete CSV file");
File.Delete(FullPath + "\\" + FileName + ".csv");
Console.WriteLine("Delete Format-file");
File.Delete(FullPath + "\\formatfile_" + FileName + ".xml");
return(true);
}
public static bool HandleFile(string file, string delimiter, bool normalize)
{
string FileNameExtension = Path.GetExtension(file);
string FileName = Path.GetFileNameWithoutExtension(file);
string FullPath = Path.GetDirectoryName(file);
Console.WriteLine("FileName: {0}", FileName);
Console.WriteLine("File-type: {0}", FileNameExtension);
Console.WriteLine("Full path: {0}", FullPath);
if (FileNameExtension == ".xls" || FileNameExtension == ".xlsx")
{
Console.WriteLine("File is Excel-format - converting Excel to CSV");
string csvoutput = Path.ChangeExtension(file, ".csv");
delimiter = "|";
ExcelFileHelper.SaveAsCsv(file, csvoutput, delimiter);
Console.WriteLine("Deletes Excel file");
File.Delete(file);
}
if (delimiter == "tab")
{
Console.WriteLine("Delimter set to TAB - converting TAB to pipe '|'");
string text = "";
using (StreamReader sr = new StreamReader(file))
{
int i = 0;
do
{
i++;
string line = sr.ReadLine();
if (line != "")
{
line = line.Replace("\t", "|");
text = text + line + Environment.NewLine;
}
} while (sr.EndOfStream == false);
}
File.WriteAllText(FullPath + "\\" + FileName + "Converted.csv", text);
//change delimiter and filename
delimiter = "|";
FileName += "Converted";
Console.WriteLine("New filename: {0}", FileName);
}
if (normalize)
{
int r = 0;
string headerline = File.ReadLines(FullPath + "\\" + FileName + ".csv").First(); // gets the first line from file.
int delimitercount = (headerline.Length - headerline.Replace(delimiter, "").Length);
Console.WriteLine("Normalizing file");
string text = "";
using (StreamReader sr = new StreamReader(FullPath + "\\" + FileName + ".csv"))
{
int i = 0;
do
{
i++;
string line = sr.ReadLine();
if (line != "")
{
int linedelimiters = line.Length - line.Replace(delimiter, "").Length;
if (linedelimiters < delimitercount)
{
r++;
int missingdelimiters = delimitercount - linedelimiters;
line += string.Concat(Enumerable.Repeat(delimiter, missingdelimiters));
}
text = text + line + Environment.NewLine;
}
} while (sr.EndOfStream == false);
}
if (r > 0)
{
Console.WriteLine("Found {0} rows to be normalized", r);
File.WriteAllText(FullPath + "\\" + FileName + ".csv", text);
}
}
Console.WriteLine("Processing CSV-file and generating XML-formatfile");
var doc = GenerateXML.GenerateXMLFile(FullPath, "\\" + FileName + ".csv", delimiter);
StreamWriter xmlfile = File.CreateText(FullPath + "\\formatfile_" + FileName + ".xml");
doc.Save(xmlfile);
xmlfile.Close();
if (GenerateFormatFile.Properties.Settings.Default.loadToSQL)
{
Console.WriteLine("LoadToSQL is activated - loading file to SQL Server");
LoadToSQL.CSVFile(
GenerateFormatFile.Properties.Settings.Default.servername
, GenerateFormatFile.Properties.Settings.Default.databasename
, GenerateFormatFile.Properties.Settings.Default.username
, GenerateFormatFile.Properties.Settings.Default.password
, FullPath + "\\" + FileName + ".csv");
}
//Console.WriteLine("Delete CSV file");
//File.Delete(FullPath + "\\" + FileName + ".csv");
//Console.WriteLine("Delete Format-file");
//File.Delete(FullPath + "\\formatfile_" + FileName + ".xml");
return(true);
}