Does your code only make errors when processing a subset of files or does it all fail?
I'm speculating on a possible cause of the error, when the number of fields in a row of the TXT file is not the same as the first line:
The length of 'line. Replace(FileDelimiter, "','")' and 'ColumnList' is not equal.
eample: ColumnList = "[A, B, C]"; line. Replace(FileDelimiter, "','") = "1, 2, 3, 4" or "1, 2"
I don't know how you would handle such TXT content, but if you assume that the header order for each row is the same, It can be handled like this:
When the data is short, an empty string is added.
When the data is long, remove the redundant strings
(This is a code reference, you may need to rewrite the appropriate code for your language version)
string dataStr = line.Replace(FileDelimiter, "','");
string[] columnNames = ColumnList.Split(new string[] { "],[", "[", "]" }, StringSplitOptions.RemoveEmptyEntries);
string[] dataValues = dataStr.Split(',');
if (columnNames.Length != dataValues.Length)
{
if (dataValues.Length < columnNames.Length)
{
Array.Resize(ref dataValues, columnNames.Length);
for (int i = dataValues.Length - columnNames.Length; i < columnNames.Length; i++)
{
dataValues[i] = "";
}
}
else if (dataValues.Length > columnNames.Length)
{
Array.Resize(ref dataValues, columnNames.Length);
}
}
string query = "Insert into [" + filenameonly + "] (" + ColumnList + ") VALUES('" + string.Join("','", dataValues) + "')";
Excel_OLE_Cmd.CommandText = query;
Excel_OLE_Cmd.ExecuteNonQuery();
You may need to check with your users to see if you can handle potentially non-compliant TXT data files in this way (especially if we have removed redundant data).