Convert column with mixed data to string

Question

I have an excel file with 1 column, the column contains mixed data,int & string.

After I read the data from the excel file, I saw that the cell with data AZ-965 is null. this is my string connection

return new OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source="
                                   + excel + ";Extended Properties='Excel 12.0;HDR=YES;IMEX=1;ImportMixedTypes=Text;TypeGuessRows=0;';");

and this how I read data from Excel file

private static List<T> GetImportedData<T>(string sheet, OleDbConnection myConnection,
        List<string> column, ImportDataView<T> view) where T : IImportableData
    {
        var cols = string.Join( ",", column.Select(Cast));
        var formattableString = $"select {cols} from [{sheet}$]";
        using (var MyCommand = new 
            OleDbDataAdapter(formattableString, myConnection))
        {
            using (var DtSet = new DataSet())
            {
                int nbRow = 0;
                MyCommand.Fill(DtSet);
                var dt = DtSet.Tables[0];
                var rows = dt.AsEnumerable();
                var convertedList = rows
                    //.AsParallel()
                    .Select(x => GenerateImport<T>(x, column, ref nbRow, view))
                    .ToList();
                return convertedList;
            }
        }
    }

    private static string Cast(string arg)
    {
      return $"IIf(IsNull([{arg}]), '', CStr([{arg}]))";
     // return $"CStr([{arg}]) as {cleanName(arg)}";
     //return $"[{arg}]";
    }

I check this link, but nothing is work, same isue

score 1 · Accepted Answer · answered Jan 25 '20 at 02:34

TypeGuessRows in your connection string is not a connection string property, at least not that I'm aware. It's a registry key:

https://learn.microsoft.com/en-us/office/client-developer/access/desktop-database-reference/initializing-the-microsoft-excel-driver?tabs=office-2016

So I don't think it's doing what you think it is... or anything for that matter.

If you can't control the registry or the content of the file, my only suggestion would be to change the header property in the connection string (HDR=YES) to "no" so that it reads the first record in as text... then you can effectively trash the first row.

It's not a great solution, but it should do what you seek.

Alternatively, you can try reading the content using a DbDataReader instead of using a data adapter:

using (OleDbCommand cmd = new OleDbCommand(formattableString, conn))
{
    using (OleDbDataReader reader = cmd.ExecuteReader())
    {
        string column = reader.IsDBNull(0) ? null : reader.GetValue(0).ToString();
    }
}

Based on my inferences on what you are trying to do, the data reader may actually offer some other advantages as well.

I think you are right with chnageong the HDR to NO, then delete 1st row, I think this is the only solution that help me. — Med Amin, Jan 27 '20 at 10:10

Convert column with mixed data to string

1 Answers1