Read data into datatable RRS feed

  • Question

  • I feel that my question is more about C#, although it deals with Excel, but it is not VBA question.

    I don't know why some of my threads had been moved to Excel forum (I feel there are much fewer viewers there), maybe I should remove keyword Excel from thread title.

    Here is my original thread, which was posted here then moved to Excel forum. How should I incorporate below logic into the sample code. It is just the logic I want to go with.

    Maybe add messagebox for error message.

    Thank you very much!

    int C1SK = 0; //Store_key
    int C2MN = 0; //region_key
    int C3TC = 0; //store_name
    int C4VT = 0; //store_type
    int C5TL = 0; //street_address
    int C6VN = 0; //city
    int C7VA = 0; //state_province
    for (int i=1; i <= colCount; i++)
        if ((string)(xlWorksheet.Cells[1, i]) == "Store_key")
            C1SK = i;
        else if ((string)(xlWorksheet.Cells[1, i]) == "region_key")
            C2MN = i;
        else if ((string)(xlWorksheet.Cells[1, i]) == "store_name")
            C3TC = i;
        else if ((string)(xlWorksheet.Cells[1, i]) == "store_type")
            C4VT = i;
        else if ((string)(xlWorksheet.Cells[1, i]) == "street_address")
            C5TL = i;
        else if ((string)(xlWorksheet.Cells[1, i]) == "city")
            C6VN = i;
        else if ((string)(xlWorksheet.Cells[1, i]) == "state_province")
            C7VA = i;
    table.Columns.Add("Store_key", typeof(int));
    table.Columns.Add("region_key", typeof(int));
    table.Columns.Add("store_name", typeof(string));
    table.Columns.Add("store_type", typeof(string));
    table.Columns.Add("street_address", typeof(string));
    table.Columns.Add("city", typeof(string));
    table.Columns.Add("state_province", typeof(string));
    //table.Columns.Add("ID", typeof(int));
    for (int i = 2; i <= rowCount; i++)
    table.Rows.Add(xlRange.Cells[i, C1SK].Value, xlRange.Cells[i, C2MN].Value, xlRange.Cells[i, C3TC].Value, xlRange.Cells[i, C4VT].Value, xlRange.Cells[i, C5TL].Value, xlRange.Cells[i, C6VN]Value, xlRange.Cells[i, C7VA].Value);

    • Edited by VA_er Friday, March 22, 2019 8:44 PM
    Friday, March 22, 2019 2:27 PM

All replies

  • Hello,

    An easy way to read sheet data such as shown is via OleDb. In this case given

    VS2017 source for below.

    We can get the above into a DataTable

    Note the ListBox shows column names and data types that is not required but can be useful if you do want to traverse columns for perhaps a mapping operation that is out of scope of this question which I've done in enterprise solutions years ago.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    profile for Karen Payne on Stack Exchange

    Friday, March 22, 2019 5:41 PM