November 15, 2019 at 7:09 pm
I ma using this command with openrowset on an excel file:
SELECT F1, F2, F3, F4, F5, F6=cast(f6 as varchar(500)), F7, F8, F9, F10, F11, F12, F13, F14, F15, F16, F17, F18, '\\sasfs-01\sas$\Shared\Audits\UNFI\email\2019\Attachments_FH\00000000905EB7015D2DA14EA24D833EAE09D318C4E22700__DR WOODS SN NWL NOV 2018 8-16-17 SUBMITTED 8-24-17.xlsx' as F19, 'UNFI Promotional Form' as F20 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 xml;HDR=No;Database=\\sasfs-01\sas$\Shared\Audits\UNFI\email\2019\Attachments_FH\00000000905EB7015D2DA14EA24D833EAE09D318C4E22700__DR WOODS SN NWL NOV 2018 8-16-17 SUBMITTED 8-24-17.xlsx','Select * FROM [UNFI Promotional Form$]')
I get the error msg below.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'F17'.
Msg 207, Level 16, State 1, Line 7
Invalid column name 'F18'.
If I set F17 and F18 as NULL like below then it works fine. The thing is that this error does not happen with all my excel files...
SELECT F1, F2, F3, F4, F5, F6=cast(f6 as varchar(500)), F7, F8, F9, F10, F11, F12, F13, F14, F15, F16,Null as F17,Null as F18, '\\sasfs-01\sas$\Shared\Audits\UNFI\email\2019\Attachments_FH\00000000905EB7015D2DA14EA24D833EAE09D318C4E22700__DR WOODS SN NWL NOV 2018 8-16-17 SUBMITTED 8-24-17.xlsx' as F19, 'UNFI Promotional Form' as F20 FROM OPENROWSET('Microsoft.ACE.OLEDB.12.0', 'Excel 12.0 xml;HDR=No;Database=\\sasfs-01\sas$\Shared\Audits\UNFI\email\2019\Attachments_FH\00000000905EB7015D2DA14EA24D833EAE09D318C4E22700__DR WOODS SN NWL NOV 2018 8-16-17 SUBMITTED 8-24-17.xlsx','Select * FROM [UNFI Promotional Form$]')
November 15, 2019 at 7:58 pm
Is this a blog post, or do you have a question?
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
November 15, 2019 at 9:32 pm
My question is...Why am I getting this error msg when i run some excel files??? They don't look different and column 17 and 18 in excel are empty...in the ones that run fine and the ones that give me the error msg.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply