/// <summary>
/// Counts the amount of columns based on the delimiters defined by the user
/// </summary>
/// <param name="filePath"></param>
/// <param name="delimiterItem"></param>
/// <returns></returns>
private int GetColumnCount(string filePath, DelimiterItem delimiterItem)
{
int result = 0;
int count = 0;
string[] allLines;
string[] splitLine;
FileInfo file;
try
{
//
// Get the file info
file = new FileInfo(filePath);
if (file.Exists)
{
//
// Do we have a delimiter?
if (!string.IsNullOrEmpty(delimiterItem.GetDelimitingCharacter()))
{
//
// Load all lines
allLines = File.ReadAllLines(filePath);
foreach (string line in allLines)
{
//
// Split the line at the delimiter
splitLine = line.Split(char.Parse(delimiterItem.GetDelimitingCharacter()));
if (result < splitLine.Count())
{
result = splitLine.Count();
}
//
// If the file > 10000 lines and we have analyzed 30% of the file, break out
if (allLines.Count() > 10000 && (count++ / 100) >= 0.30)
{
break;
}
}
}
}
}
catch { }
return(result);
}
/// <summary>
/// Prompts the user to provide delimiter information for the file
/// </summary>
/// <returns></returns>
private DelimiterItem GetDelimiter()
{
DelimiterItem result = default(DelimiterItem);
try
{
//
// Open the delimiter info form
frmDelimiter delimiterForm = new frmDelimiter();
delimiterForm.StartPosition = FormStartPosition.CenterParent;
delimiterForm.ShowDialog();
//
// Capture delimiter result
result = delimiterForm.DelimiterItem;
}
catch { }
return(result);
}
/// <summary>
/// Handles the submission and validation of the delimiter form
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btnDone_Click(object sender, EventArgs e)
{
//
// Validate
if (this.chkDelimiterTab.Checked == false && this.chkDelimiterSemicolon.Checked == false &&
this.chkDelimiterComma.Checked == false && this.chkDelimiterSpace.Checked == false &&
this.chkDelimiterOther.Checked == false)
{
MessageBox.Show("Must provide atleast one delimiter.", Constants.Application.Name + " " + Constants.Application.Version, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
if (this.chkDelimiterOther.Checked == true && string.IsNullOrEmpty(this.txtOtherDelimiter.Text))
{
MessageBox.Show("Must provide the other delimiter.", Constants.Application.Name + " " + Constants.Application.Version, MessageBoxButtons.OK, MessageBoxIcon.Error);
return;
}
//
// Delimiter
this.DelimiterItem = new DelimiterItem();
this.DelimiterItem.IsTab = this.chkDelimiterTab.Checked;
this.DelimiterItem.IsSemicolon = this.chkDelimiterSemicolon.Checked;
this.DelimiterItem.IsComma = this.chkDelimiterComma.Checked;
this.DelimiterItem.IsSpace = this.chkDelimiterSpace.Checked;
this.DelimiterItem.IsOther = this.chkDelimiterOther.Checked;
this.DelimiterItem.OtherChar = this.txtOtherDelimiter.Text;
this.DelimiterItem.TreatConsecutiveDelimitersAsOne = this.chkTreatConsecutiveDelimitersAsOne.Checked;
if (this.cmbTextQualifier.Text != "(None)")
{
this.DelimiterItem.TextQualifier = this.cmbTextQualifier.Text;
}
else
{
this.DelimiterItem.TextQualifier = this.cmbTextQualifier.Text;
}
//
// Close
this.Close();
}
/// <summary>
/// Loads the delimited file using the delimiters defined by the user
/// </summary>
/// <param name="filePath"></param>
/// <param name="delimiterItem"></param>
/// <param name="columnCount"></param>
/// <returns></returns>
private Tuple <bool, string> OpenDelimitedFileAsText(string filePath, DelimiterItem delimiterItem, int columnCount)
{
Tuple <bool, string> result;
object[] fieldInfoArray;
FileInfo file;
QueryTable queryTable;
Worksheet resultWorksheet;
try
{
//
// Load file info
file = new FileInfo(filePath);
if (file.Exists)
{
//
// Build file info array
fieldInfoArray = new object[columnCount];
for (int i = 0; i < columnCount; i++)
{
fieldInfoArray[i] = (int)XlColumnDataType.xlTextFormat;
}
//
// Create a new sheet to work with
resultWorksheet = Globals.ThisAddIn.Application.ActiveWorkbook.Sheets.Add();
resultWorksheet.Name = "Sheet" + Globals.ThisAddIn.Application.ActiveWorkbook.Sheets.Count;
//
// Import file as text
queryTable = resultWorksheet.QueryTables.Add(Connection: $"TEXT;{file.FullName}", Destination: resultWorksheet.Range["$A$1"]);
queryTable.TextFileTabDelimiter = delimiterItem.IsTab;
queryTable.TextFileSemicolonDelimiter = delimiterItem.IsSemicolon;
queryTable.TextFileCommaDelimiter = delimiterItem.IsComma;
queryTable.TextFileSpaceDelimiter = delimiterItem.IsSpace;
queryTable.TextFileConsecutiveDelimiter = delimiterItem.TreatConsecutiveDelimitersAsOne;
queryTable.TextFileParseType = XlTextParsingType.xlDelimited;
queryTable.TextFileColumnDataTypes = fieldInfoArray;
if (delimiterItem.IsOther)
{
queryTable.TextFileOtherDelimiter = delimiterItem.OtherChar;
}
if (delimiterItem.TextQualifier.Equals("\""))
{
queryTable.TextFileTextQualifier = XlTextQualifier.xlTextQualifierDoubleQuote;
}
else if (delimiterItem.TextQualifier.Equals("'"))
{
queryTable.TextFileTextQualifier = XlTextQualifier.xlTextQualifierSingleQuote;
}
else
{
queryTable.TextFileTextQualifier = XlTextQualifier.xlTextQualifierNone;
}
queryTable.Refresh();
queryTable.Delete();
//
// Autofit
Globals.ThisAddIn.Application.Columns.EntireColumn.AutoFit();
//
// Set success
result = Tuple.Create(true, string.Empty);
}
else
{
//
// Set failure
result = Tuple.Create(false, "File could not be found.");
}
}
catch (Exception ex)
{
//
// Set failure
result = Tuple.Create(false, ex.Message);
}
return(result);
}
