DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Page 1 of 2 12 LastLast
Results 1 to 15 of 18

Thread: Excel OLEDB Create Table Issue

  1. #1
    Join Date
    Aug 2008
    Posts
    7

    Excel OLEDB Create Table Issue

    Good Afternoon,

    I've searched Google, Yahoo, MSN and groups galore and can't find information on this issue i'm having anywhere - so i'm hoping that someone here knows a good trick or two that can get me back on my feet.

    Goal: Use OLEDB to populate an excel spreadsheet with specific required column names using data from our SQL Server database.

    Issue: Including a number sign (#) in the column name when issuing a CREATE TABLE statement ends up being converted to a period (.) in Excel.

    Here is the connection string (*This works fine*):
    Code:
    string sConnectionString =
                    "Provider=Microsoft.Jet.OLEDB.4.0;" +
                    "Data Source=" + fileloc + ";" +
                    "Extended Properties=\"Excel 8.0;HDR=YES\"";
    First, I execute a "DROP TABLE" to remove Sheet1 because i'm going to re-populate it with new data (*This works fine*):
    Code:
    DROP TABLE [Sheet1$]

    Second, I issue a "CREATE TABLE" statement to recreate the Sheet1 with column names that I need (*The "Name Account #" field ends up reading "Name Account ." in Excel):
    Code:
    CREATE TABLE [Sheet1$]([Full Name] char(255),[Type] char(255),[Date] char(255),[Num] char(255),[Name City] char(255),[Name State] char(255),[Name Zip] char(255),[Memo] char(255),[Name Account #] char(255),[Name] char(255),[UPC Code] char(255),[Item Description] char(255),[Rep] char(255),[Qty] char(255),[Sales Price] char(255),[Amount] char(255))
    Lastly, I insert the data into the table - but it fails because the insert references "Name Account #" but the excel spreadsheet column name was created as "Name Account ."

    I've tried everything I can think of to try getting this "CREATE TABLE" statement to put the "#" character in the column name instead of converting it to "." when the sheet is created, but i'm at a loss

    Can anyone lend a helping hand?

    GS

  2. #2
    Join Date
    Dec 2003
    Posts
    2,750
    Is there any reason why you need to use a special character in your column name? This usually isn't a good idea since they're often reserved for another purpose by the software or database system.
    Paul
    ~~~~
    Microsoft MVP (Visual Basic)

  3. #3
    Join Date
    Aug 2008
    Posts
    7
    Yes unfortunately, because the excel spreadsheet is being fed to a legacy application that expects that column name. I realize there wouldn't be any issue if we could change it, and would never use the character in my own work.

    Accourding to MS, the '#' character is a valid character for column names, it just so happens that something in between the OLEDB -> Excel is messing with this, and I can't seem to find any special escape character sequence or syntax to use an ASCII value for the '#' character that applies to create table statements.

    Any ideas?

    GS

  4. #4
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    The Ascii value for the # sign is 35
    Code:
    Private Sub Command1_Click()
    MsgBox Chr(35)
    End Sub
    If you want to test it.

  5. #5
    Join Date
    Aug 2008
    Posts
    7
    Thanks,

    Any idea what the correct syntax would be to incorporate ASCII into a CREATE TABLE statement?

    That's where I got stuck

    GS

  6. #6
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    This worked just fine for me.
    Code:
    Option Explicit
    
    Private ADOCn As ADODB.Connection
    Private ConnString As String
    Private sSQL As String
    
    Private Sub Form_Load()
    ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
            "Data Source=d:\db1.mdb;" & _
            "Persist Security Info=False"
    
    Set ADOCn = New ADODB.Connection
    ADOCn.ConnectionString = ConnString
    ADOCn.Open ConnString
    
    End Sub
    
    Private Sub Command1_Click()
    sSQL = "CREATE TABLE [Name Account #] "
    sSQL = sSQL & "(First_Name char(50), "
    sSQL = sSQL & "Last_Name char(50), "
    sSQL = sSQL & "Address char(50), "
    sSQL = sSQL & "City char(50), "
    sSQL = sSQL & "Country char(25), "
    sSQL = sSQL & "Birth_Date date) "
    ADOCn.Execute sSQL
    
    End Sub

  7. #7
    Join Date
    Aug 2008
    Posts
    7
    Weird that it lets you make the table name that includes '#' with no problem, but does it let you specify the '#' in a column name?

    Here is my create table statement (my table name is Sheet1 and the # is in a column name, not the table name)

    Code:
    CREATE TABLE [Sheet1$]
    ([Full Name] char(255),[Type] char(255),[Date] char(255),
    [Num] char(255),[Name City] char(255),[Name State] char(255),
    [Name Zip] char(255),[Memo] char(255),[Name Account #] char(255),
    [Name] char(255),[UPC Code] char(255),[Item Description] char(255),
    [Rep] char(255),[Qty] char(255),[Sales Price] char(255),[Amount] char(255))
    Thanks in advance for the aid,
    GS

  8. #8
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    I didn't do it as a column name, only a table name.

    So, I went back and redid it, and still with no problems.
    Code:
    Private Sub Command1_Click()
    sSQL = "CREATE TABLE [Name Account #] "
    sSQL = sSQL & "(First_Name char(50), "
    sSQL = sSQL & "Last_Name char(50), "
    sSQL = sSQL & "[Address #]char(50), "
    sSQL = sSQL & "City char(50), "
    sSQL = sSQL & "Country char(25), "
    sSQL = sSQL & "Birth_Date date) "
    ADOCn.Execute sSQL
    End Sub

  9. #9
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Why are all your field lengths 255 characters?

    I don't know of any state in the union with a name that long, and darn few cities, and even fewer first and last names.

  10. #10
    Join Date
    Aug 2008
    Posts
    7
    At second glance, it looks like you're using an Access Database in your code example. I'm using OLEDB to send to an Excel Spreadsheet (see title). That's when the issue is encountered.

    Here are the exact steps to reproduce my problem:
    1. Assuming you have Excel installed, create a new worksheet in Excel and save it to your C: drive as "work.xls"
    2. Execute the code posted below (I just modified your code to reproduce my result)
    3. Open the work.xls and notice that the "Name Account #" column header reads "Name Account ."

    If you modify your code to this:
    Code:
    Option Explicit
    
        Private OLEDBCn As System.Data.OleDb.OleDbConnection
        Private OLEDBCom As System.Data.OleDb.OleDbCommand
        Private ConnString As String
        Private sSQL As String
    
        Private Sub Command1_Click()
            ConnString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                    "Data Source=c:\work.xls;" & _
                    "Extended Properties=""Excel 8.0;HDR=YES"""
    
            OLEDBCn = New System.Data.OleDb.OleDbConnection(ConnString)
            sSQL = "CREATE TABLE [Sheet1$] "
            sSQL = sSQL & "([First_Name] char(50), "
            sSQL = sSQL & "[Last_Name] char(50), "
            sSQL = sSQL & "[Address] char(50), "
            sSQL = sSQL & "[City] char(50), "
            sSQL = sSQL & "[Country] char(25), "
            sSQL = sSQL & "[New Account #] char(25)) "
            OLEDBCom = New System.Data.OleDb.OleDbCommand(sSQL, OLEDBCn)
            OLEDBCn.Open()
            OLEDBCom.ExecuteNonQuery()
            OLEDBCn.Close()
        End Sub
    I just tested this and it produced the exact results i'm describing. Please let me know how this works out for you. The incorrect result I described is realized when using Excel with this code above - and the trick is to get the column name to "New Account #" instead of the produced "New Account ."

    GS

  11. #11
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    It would have been useful to know that you were using VB.NET....I wouldn't have been messing about with VB6.

    Let me switch over to a .NET project, and see what I can come up with.

  12. #12
    Join Date
    Aug 2008
    Posts
    7
    My fault - I should have included that I was using .NET OLEDB to Excel in my title or original description of my issue.

    Hope i've given enough information to adequately describe my problem,
    GS

  13. #13
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Here is what I don't understand, and what initially confused me.

    Excel is a spreadsheet. It is not a database. Why are you running CREATE TABLE queries against a spreadsheet and using a database provider to connect to, or create, one?

    If you just use VB.NET code in a project with a reference set to the Micrsoft Excel Objects Library, then you should be able to do whatever you want. I just ran this and it worked like champ. All you would need to do now is fill in the data blanks.
    Code:
    Imports Excel
    Public Class Form1
        Private objExcel As Excel.Application = DirectCast(CreateObject("Excel.Application"), Excel.Application)
        Private bkWorkBook As Excel.Workbook = DirectCast(objExcel.Workbooks.Add(), Excel.Workbook)
        Private shWorkSheet As Excel.Worksheet = DirectCast(bkWorkBook.Sheets("Sheet1"), Excel.Worksheet)
        Private rngRowStart As Excel.Range = DirectCast(shWorkSheet.Cells(1, 1), Excel.Range)
        Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            shWorkSheet.Name = "Sheet1"
            shWorkSheet.Range("A1").Value = "First Name"
            shWorkSheet.Range("B1").Value = "Last Name"
            shWorkSheet.Range("C1").Value = "Address "
            shWorkSheet.Range("D1").Value = "City"
            shWorkSheet.Range("E1").Value = "Country"
            shWorkSheet.Range("F1").Value = "New Account #"
            'start populating the data here
            rngRowStart = shWorkSheet.Range("A3")
            objExcel.Visible = True
        End Sub
    End Class

  14. #14
    Join Date
    Aug 2008
    Posts
    7
    Thanks a lot for the code example - I may end up having to use the method you proposed if I can't find a workable OLEDB solution.

    The reason for using OLEDB (which is the other MS recommended approach with .NET) is that you don't actually need Excel installed on the workstation since it doesnt require a reference to the COM Object. As long as .NET is installed you can read/write/create Excel spreadsheets with or without Excel.

    Since OLEDB -> Excel knows how to interpret this, the "CREATE TABLE" creates the sheet and the columns you specify become the headers. "INSERTS" put data into the rows and you can "DELETE TABLE" to remove a sheet, etc. I'm guessing at this point that this is probably a bug in OLEDB's interpretation to Excel.

    I appreciate all the time & effort you've put into this,
    Thanks again!

    GS

  15. #15
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,663
    Quote Originally Posted by gsmmc
    The reason for using OLEDB (which is the other MS recommended approach with .NET) is that you don't actually need Excel installed on the workstation since it doesnt require a reference to the COM Object. As long as .NET is installed you can read/write/create Excel spreadsheets with or without Excel.
    That makes sense.

    I am going to continue to play around with your original code. It is puzzeling why it does not like that # sign.

Similar Threads

  1. Replies: 8
    Last Post: 01-23-2006, 04:27 PM
  2. Replies: 0
    Last Post: 09-07-2005, 04:40 PM
  3. Can't create a PARADOX file and insert records using ODBC
    By Steve Oliver in forum VB Classic
    Replies: 0
    Last Post: 07-26-2002, 12:30 PM
  4. Multi-row calculations
    By Bob Hines in forum Database
    Replies: 7
    Last Post: 04-27-2000, 11:14 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center