November 10, 2016 at 2:57 am
Hello folks,
I have very unusual situation in one column so if you could assist it would be great.
First of all, I have linked server and I've used this command to get linked server:
EXEC sp_addlinkedserver
@server = 'ExcelServer2',
@srvproduct = 'Excel',
@provider = 'Microsoft.ACE.OLEDB.12.0',
@datasrc = 'C:\Test\excel-sql-server.xlsx',
@provstr = 'Excel 12.0;IMEX=1;HDR=YES;'
and it works!
Now, one column has e.g. in first 120 rows number data and after that it has text data.
Every time when I start some query in this column for text data I got "NULL".
I have tried couple things and it seems that only solution is to manually put some letters in front of the numbers in excel file.
But, if I need to put something manually what is the purpose of linked server? 😀
Any idea how to resolve this?
This is how column looks like:
110
110
122
105
105
106
CALL
CALL
DAT
DAT
NAW
and for text data I got "NULL" after any query.
Thank you in advance.
November 10, 2016 at 6:14 am
Could you format this column to text in excel ?
November 10, 2016 at 6:36 am
ESAT ERKEC (11/10/2016)
Could you format this column to text in excel ?
A have already tried format to text, number, custom etc. but still nothing.
I would like to avoid opening these excel files as they are huge (over 150 mb) and they need 2-3 minutes to open .
November 10, 2016 at 6:40 am
Could you send a sample of that excel file?
November 10, 2016 at 6:43 am
tocy1980 (11/10/2016)
ESAT ERKEC (11/10/2016)
Could you format this column to text in excel ?A have already tried format to text, number, custom etc. but still nothing.
I would like to avoid opening these excel files as they are huge (over 150 mb) and they need 2-3 minutes to open .
Unfortunately, the problem is that while Excel can handle the concept of a single column having multiple data types, SQL Server can NOT do that, period. Also, even after you change the data to be of a single type within that column of the spreadsheet, you may have to re-create the Linked Server. The difficulty arises because Excel only looks at the first 8 rows to figure out the data type of the column, so if you're going to have a column where some values include alphabetic characters, you'll need to have the first 8 rows or more contain those values in order for Excel to think of that column as "character" data as opposed to numeric. That's just how it works, so you'll have to accommodate it.
Steve (aka sgmunson) 🙂 🙂 🙂
Rent Servers for Income (picks and shovels strategy)
November 10, 2016 at 8:32 am
Roger that 😉
Thanks guys!
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply