dealing with extra columns in Excel files - C#

dealing with extra columns in Excel files - C#

In my application I need to read an excel file and display the
headers(title) in a tabular format. This works fine so far. But for some
excel files it shows(excel file has 20 columns) some extra
columns(column21, column22 etc). Not sure why its showing these extra
columns when I checked the excel file it has only 20 columns and 21 or 22
columns are completely empty. Not sure why my its displaying these extra
columns. When I tried to debug the code "myReader.FieldCount" was showing
22 columns. I tried to programmatically remove those columns which are
empty. But it raised someother issues with the row data. For some rows its
shows only 18 or 15 columns as there is missing data for some columns. Is
there a better way of dealing with excel. Here is my code
@@@@@@@@@@@@@
if (sourceFile.ToUpper().IndexOf(".XLSX") >= 0) // excel 2007 or
later file
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
+ sourceFile + ";Extended Properties=\"Excel
12.0;HDR=No;\"";
else // previous excel versions
strConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="
+ sourceFile + ";Extended Properties=\"Excel
8.0;HDR=No;\"";
OleDbConnection conn = null;
StreamWriter wrtr = null;
OleDbCommand cmd = null;
OleDbDataReader myReader = null;
try
{
conn = new OleDbConnection(strConn);
conn.Open();
cmd = new OleDbCommand("SELECT * FROM [" + worksheetName +
"]", conn);
cmd.CommandType = CommandType.Text;
myReader = cmd.ExecuteReader();
wrtr = new StreamWriter(targetFile);
while (myReader.Read())
{
List<string> builder = new List<string>();
for (int y = 0; y < myReader.FieldCount; y++)
{
if(!string.IsNullOrEmpty(myReader[y].ToString()))
builder.Add("\"" + myReader[y].ToString() +
"\"");
}
wrtr.WriteLine(string.Join(",", builder));
}