none
Check if table name exist RRS feed

  • Question

  • Guys I need some help and I just can figure out how to do it though I tried.

    I have 2 workbooks, say wb1.xlsx and wb2.xlsx.

    wb1 contains list of table names.

    wb2 is the data source where tables are explicitly given their respective names. There are three worksheets under wb2, sheet1, sheet2, and sheet3. Now I would like to compare if table names listed in wb1 exists in wb2, sheet3. If it is not there, there's a message box that says, the table name cannot be found on wb2. that's all. thanks to everyone.

    Monday, November 27, 2017 8:18 AM

All replies

  • you can do it in many ways example given below

    private bool ValidateSheet1(string FilePath, string Extension, string isHDR,string SheetName)
    {
        string conStr = "";
        switch (Extension)
        {
            case ".xls": //Excel 97-03
                conStr = ConfigurationManager.ConnectionStrings["Excel03ConString"]
                         .ConnectionString;
                break;
            case ".xlsx": //Excel 07
                conStr = ConfigurationManager.ConnectionStrings["Excel07ConString"]
                         .ConnectionString;
                break;
        }
        conStr = String.Format(conStr, FilePath, isHDR);
        OleDbConnection connExcel = new OleDbConnection(conStr);
        OleDbCommand cmdExcel = new OleDbCommand();
        OleDbDataAdapter oda = new OleDbDataAdapter();
        cmdExcel.Connection = connExcel;
        connExcel.Open();
        DataTable dtSheets = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
        return dtSheets.Select().ToList().Exists(sheet => sheet["TABLE_NAME"].ToString() == SheetName);
     
    }

    you can check it like 

    bool exists = ValidateSheet1(FilePath, Extension, "Yes","Sheet1$");


    have a look at line given below hope it helps.

    https://code.msdn.microsoft.com/Basics-of-using-Excel-4453945d

    Ashish Pandey


    Monday, November 27, 2017 9:43 AM
  • Hello,

    If you need assistance with getting table names then checking them as mentioned look at the following code sample. The code sample will get table names into a list which you can then query one against the other in regards to work books and worksheets.

    In each worksheet the code gets table names and places them into a list where the list type is shown below which should give you all the details needed to do a compare as needed.

    Public Class ExcelReferenceTable
        Public Property Name As String
        Public Property SheetName As String
        Public Property Address As String
        Public ReadOnly Property SelectString As String
            Get
                Return "SELECT * FROM [" & SheetName & "$" & Address & "]"
            End Get
        End Property
        Public Property SourceDataFile As String
        <System.Diagnostics.DebuggerStepThrough()> _
        Public Sub New()
        End Sub
        Public Overrides Function ToString() As String
            Return Name
        End Function
    End Class
    


    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, November 27, 2017 9:47 AM
    Moderator
  • Hello Ashish, please revise your code so it's presented as VB.NET rather than posting C# code in a VB.NET forum.

    Please remember to mark the replies as answers if they help and unmark 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.
    VB Forums - moderator
    profile for Karen Payne on Stack Exchange, a network of free, community-driven Q&A sites

    Monday, November 27, 2017 3:56 PM
    Moderator
  • Hi toljack,

    If I have two Excel, one is wb1.xlsx, another is wb2.xlsx. There are one sheet in the wb1.xlsx, have some table names in the sheet. There are three sheets in the wb2.xlsx, one is Test1, two is Test2, three is Test3. Now, we search if the table names in the wb1.xlsx are also exist in wb2.xlsx.

     ' get wb2.xlsx sheet names
            Dim str As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\test\Wb2.xlsx;
                                 Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
            Dim conn As New OleDbConnection(str)
            conn.Open()
            Dim dtSheets As DataTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing)
            Dim listSheet As New List(Of String)
            Dim drSheet As DataRow
            For Each drSheet In dtSheets.Rows
                listSheet.Add(drSheet("TABLE_NAME").ToString())
            Next
            conn.Close()
            'get wb1.xlsx table names
            Dim str1 As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=D:\test\Wb1.xlsx;
                                 Extended Properties=""Excel 12.0 Xml;HDR=YES"";"
            Dim conn1 As New OleDbConnection(str1)
            conn1.Open()
            Dim sql As String = "select * from [TableName$]"
            Dim cmd As New OleDbCommand(sql, conn1)
            Dim adapter As New OleDbDataAdapter(cmd)
            Dim dt As New DataTable
            adapter.Fill(dt)
            conn1.Close()
            For Each tablename As DataRow In dt.Rows
                Dim match = listSheet.Where(Function(searchstring) searchstring.Contains(tablename(0)))
                If match IsNot Nothing Then
                    Console.WriteLine("The wb2 has the table name is {0}", tablename)
                End If
            Next

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Tuesday, November 28, 2017 2:48 AM
    Moderator
  • TolJack,

    Be aware that the until currently now given solutions are very depending from the OS you use and the Excel version you use. 

    https://support.microsoft.com/en-us/help/2874601/can-t-use-the-access-odbc-driver-or-oledb-provider-outside-office-clic


    Success
    Cor

    Tuesday, November 28, 2017 8:36 AM