• Visitors can check out the Forum FAQ by clicking this link. You have to register before you can post: click the REGISTER link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. View our Forum Privacy Policy.
  • Want to receive the latest contracting news and advice straight to your inbox? Sign up to the ContractorUK newsletter here. Every sign up will also be entered into a draw to WIN £100 Amazon vouchers!

Using Excel via ODBC

Collapse
X
  •  
  • Filter
  • Time
  • Show
Clear All
new posts

    Using Excel via ODBC

    I create a table with numeric columns but do not put data in it via ODBC.
    When I read it back the comes back as a varchar because it is unable to tell the data type without data.

    Is there any way around this, can you create the columns with some kind of tag etc?
    "He's actually ripped" - Jared Padalecki

    https://youtu.be/l-PUnsCL590?list=PL...dNeCyi9a&t=615

    #2
    What do you want to do with the data?
    The greatest trick the devil ever pulled was convincing the world that he didn't exist

    Comment


      #3
      According to https://docs.microsoft.com/en-us/sql...ql-server-2017 it should only become varchar when the column contains data of mixed types; number should become double. Maybe there's a column header that's text which is screwing things up?

      Dunno as I've never done it, but if my hypothesis is correct, can you select the data from the spreadsheet excluding the row with the column headers?

      Comment


        #4
        https://docs.microsoft.com/en-us/sql...ql-server-2017

        I'm sure I used an ini file in the dim and distant past - see if above link helps.

        Failing that, you could create your own config with the column definitions.

        Comment


          #5
          I need to create the table in one statement and write to it later, closing the connection inbetween.

          My problem is that our system reads the database metadata and because the column is empty Excel returns the metadata as a varchar rather than as a numeric (which is how it was created). Our date is numeric, the metadata is varchar and we get an error.

          I am not able to change the code base which does that check as it is very deep inside legacy code.

          I was hoping there was some kind of tag I could create the column with so it knows for later that it is numeric?
          "He's actually ripped" - Jared Padalecki

          https://youtu.be/l-PUnsCL590?list=PL...dNeCyi9a&t=615

          Comment


            #6
            IIRC the ini file didn't require code change - an ini with the same name as the xls and it processes automagically

            Comment


              #7
              What database are you using?

              All sounds a bit rinky dink if you're using Excel as some sort of ETL device.
              The greatest trick the devil ever pulled was convincing the world that he didn't exist

              Comment


                #8
                Originally posted by LondonManc View Post
                What database are you using?

                All sounds a bit rinky dink if you're using Excel as some sort of ETL device.
                I am writing an ODBC driver for our product and someone wants to use Excel.
                "He's actually ripped" - Jared Padalecki

                https://youtu.be/l-PUnsCL590?list=PL...dNeCyi9a&t=615

                Comment


                  #9
                  Originally posted by MyUserName View Post
                  I am writing an ODBC driver for our product and someone wants to use Excel.
                  My condolences

                  Is this to push something to Excel or for Excel to pull from your product?
                  The greatest trick the devil ever pulled was convincing the world that he didn't exist

                  Comment


                    #10
                    Connecting to an ODBC source Excel should get the data type from the ODBC driver of the DB its connecting to (tabledefs/catalogs) not via its text import crystal ball malarky.


                    https://docs.microsoft.com/en-us/sql...ql-server-2017

                    https://docs.microsoft.com/en-us/sql...ql-server-2017


                    You never ever want Excel to try and guess what data type it is, its wrong more often than a poster in the B***** forum!
                    Always forgive your enemies; nothing annoys them so much.

                    Comment

                    Working...
                    X