Check if columns exist in Excel file for import to MS SQL

Hi All,
Interesting issue:
Running MS SQL 2008 we have a stored procedure to dynamically fetch data from Excel files and import into SQL Tables through linked server. This has been working fine, but yesterday we received a file where the author had created a sheet and then DELETED all the columns. The Sheet exists though, and a query to that sheet name returns an error through jet 4.0 ...
So the question is, Is there a way to check if an Excel sheet has columns or not?
Andy
steamngnAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Mark WillsTopic AdvisorCommented:
Yeah it can be a real trap, worse, if you try to access it directly, then it normally fails, even if just being referenced it will check and fail.

So, normally have to try to somehow "encapsulate" the test either with an exec() or a begin try ot something that can catch an error.

Have a look at the few examples below and we can discuss further if needed....


-- method 1 - use linked server check the columns

EXEC sp_addlinkedserver MyCSV, 'Jet 4.0', 'Microsoft.Jet.OLEDB.4.0','c:\ee\', NULL,'Text'
GO
EXEC sp_addlinkedsrvlogin MyCSV, FALSE, NULL,NULL,NULL
GO
EXEC sp_tables_ex MyCSV  -- note it does pick up all TXT and CSV files
GO

if object_id('tempdb..#tbl','U') is not NULL drop table #tbl
create table #tbl(TABLE_CAT  varchar(255) ,
              TABLE_SCHEMA varchar(255) ,
              TABLE_NAME  varchar(255) ,
              COLUMN_NAME  varchar(255) ,
              DATA_TYPE  smallint ,
              TYPE_NAME  varchar(13),
              COLUMN_SIZE  int ,
              BUFFER_LENGTH  int ,
              DECIMAL_DIGITS  smallint ,
              NUM_PREC_RADIX  smallint ,
              NULLABLE  smallint ,
              REMARKS  varchar(254),
              COLUMN_DEF  varchar(254),
              SQL_DATA_TYPE  smallint ,
              SQL_DATETIME_SUB  smallint ,
              CHAR_OCTET_LENGTH  int ,
              ORDINAL_POSITION  int ,
              IS_NULLABLE  varchar(254),
              SS_DATA_TYPE  tinyint )
insert #tbl
EXEC sp_columns_ex MyCSV,empty#csv  
GO

if (select count(*) from #tbl) > 0 
   exec('select * from MyCSV...empty#csv')
else
   select 'Nope nuttin'
GO

if object_id('tempdb..#tbl','U') is not NULL drop table #tbl
GO

sp_dropserver 'MyCSV', 'droplogins';
GO


-- method 2 use xp_cmdshell to examine the physical files (turn it on in a SP and then turn it off again if worried about security)

declare @tmp table (results varchar(255),filename varchar(255),filesize int)
insert @tmp (results)
exec master..xp_cmdshell 'for %i in (c:\ee\*.csv) do @echo %~fi""%~zi'

update @tmp set filename = substring(results,1,charindex('""',results)-1), filesize = substring(results,charindex('""',results)+2,10)

if (select filesize from @tmp where filename = 'c:\ee\empty.csv') > 0
   exec('select * from OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\EE\; '',''select * from empty.csv'')') 
else
   select 'nope'
GO

-- method 3 use try/catch block and an ad hoc linked server

begin try
   exec('select * from OpenRowset(''MSDASQL'', ''Driver={Microsoft Text Driver (*.txt; *.csv)}; DefaultDir=C:\EE\; '',''select * from empty.csv'')') 
end try
begin catch
   select 'Nuttin'
end catch
GO

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
ylarvorCommented:
you probably could be do that with the .net procedure in sql server 2005. you can take two or three day to look in this direction if you want. good luck
0
leonstrykerCommented:
Runing a simple VBS (or VBA) script on the Excel file to check the data before import would probably be easiest.

Let me know if you would like to pursue this solution.

Leon
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

steamngnAuthor Commented:
Hi guys,
Thanks for the input. I will be away the next couple days, but will look into these idaes first thing Monday and post back my findings.
Have a great weekend all,
Andy
0
steamngnAuthor Commented:
Ok Mark,
Went with the TRY-CATCH, and it works beautifully. Thanks for the thoughts and guidance; answer accepted and points awarded!
Andy
0
Mark WillsTopic AdvisorCommented:
Great to hear, and always, very happy to have been of help...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.