Search code examples
c#exceldbf

Parse file to DataTable

I have to parse file with .xls extention into DataTable object. Seems like file is text table with fixed columns length in cp866 encoding, but I couldn't read it with appropriate connection string.

I can successfully open such files with MS Excel, and it writting Opening DBF 4 while it opening. Below example of reading attempt such file as simple text, and you may notice that some characters are not recognized.

Can somebody help to solve this problem?

enter image description here

Solution

  • Suppose that it's corrupt dbf4 file. You could try to read it using Microsoft.Office.Interop.Excel package:

    Application app = new Application { Visible = false };
Workbook wb = app.Workbooks.Open(fileFullName, UpdateLinks: 0, ReadOnly: true, CorruptLoad: 1);
Worksheet ws = wb.Worksheets.Item[1];
Range r = ws.UsedRange;

object[,] va = (object[,])r.Value2;
int rowLen = va.GetLength(0);
int colLen = va.GetLength(1);

StringBuilder sb = new StringBuilder();

for (int i = 1; i <= rowLen; i++)
{
    object[] row = new object[colLen];

    for (int j = 1; j <= colLen; j++)
    {
        row[j - 1] = va[i, j];
    }

    sb.AppendLine(string.Join(";", row));
}

string result = sb.ToString();

wb.Close(false, fileFullName, null);
Marshal.ReleaseComObject(wb);
app.Quit();