Using Excel via ODBC

[LondonManc]

Both. Provide an engine with which the user can drive Excel via ODBC

Have a read through this; it may help:
Microsoft SQL Server Integration Services: Mixed data types in Excel column

[MyUserName]

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!

The problem is that if there is no data in the column Excel does not know the datatype it was created as so it assumes it is a varchar. This buggers things up for us as we need to know the column type that it was created as :-(

[MyUserName]

My condolences

Is this to push something to Excel or for Excel to pull from your product?

Both. Provide an engine with which the user can drive Excel via ODBC

[LondonManc]

If you've got access to SSIS, you should be able to do this straight out of the box.

[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!

[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?

[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]

What database are you using?

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

[mudskipper]

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

[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]

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.

[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?

[LondonManc]

What do you want to do with the data?

[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?