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?
Who is Participating?
Mark WillsConnect With a Mentor Topic 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'
EXEC sp_addlinkedsrvlogin MyCSV, FALSE, NULL,NULL,NULL
EXEC sp_tables_ex MyCSV  -- note it does pick up all TXT and CSV files

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  

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

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

sp_dropserver 'MyCSV', 'droplogins';

-- 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'')') 
   select 'nope'

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

Open in new window

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
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.

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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,
steamngnAuthor Commented:
Ok Mark,
Went with the TRY-CATCH, and it works beautifully. Thanks for the thoughts and guidance; answer accepted and points awarded!
Mark WillsTopic AdvisorCommented:
Great to hear, and always, very happy to have been of help...
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.