Using Excel via ODBC

[MyUserName]

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?

[LondonManc]

What do you want to do with the data?

[NickFitz]

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?

[mudskipper]

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.

[MyUserName]

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?

[mudskipper]

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

[LondonManc]

What database are you using?

All sounds a bit rinky dink if you're using Excel as some sort of ETL device.

[MyUserName]

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.

[LondonManc]

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?

[vetran]

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!