Solved

Trapping errors from OpenDataSource

Posted on 2004-08-31
3
417 Views
Last Modified: 2008-02-01
Hello,

I'm importing excel spreadsheets into a Sql Server database.   There are two different types of spreadsheets --lets call them type A and type B -- with spreadsheets of type B having some column names which are different than those of type A.  Unfortunately, I have no way of knowing which type of spreadsheet is being read before actually issuing a SELECT call on the spreadsheet.  

Because of this, when I attempt to select a column which is not in spreadsheet, OpenDataSource errors out and I don't know how to trap the error to issue another SELECT for the other spreadsheet type.

So: 1) is there a way to trap OpenDataSource results and, 2) if not, is there a way to determine column names (say with sp_columns) on a data source opened with OpenDataSource?


Here is a working example of my OpenDataSource call:

set @SQLString = N'SELECT @foo = Due, @boo = Comments
FROM     OpenDataSource( ' + '''' + 'Microsoft.Jet.OLEDB.4.0' + '''' + ',' + '''' + 'Data Source="' + @FileName + '";User ID=Admin;Password=;Extended properties=Excel 8.0'+'''' + ')...importRange'

SET @ParmDefinition = N'@foo varchar(30) OUTPUT, @boo varchar(100) OUTPUT'

EXECUTE sp_executesql
@SQLString,
@ParmDefinition,
@foo=@result OUTPUT,
@boo=@comments OUTPUT
SELECT @comments

which fails when the spreadsheet doesn't have a 'comments' column.

Thanks!
0
Comment
Question by:pennypiper
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 34

Expert Comment

by:arbert
ID: 11948191
I think I would insert the rows into a temp table first and then use the COL_NAME function to determine what colums you have to work with.

Brett
0
 
LVL 12

Accepted Solution

by:
kselvia earned 500 total points
ID: 11959711
--I don't know if I think this is any better than selecting the results into a temp table or not. I just wanted to figure out
--how to do it, so here it is. :)  If you can create a linked server.

EXEC master..sp_addlinkedserver 'MyBook1', 'Excel', 'Microsoft.Jet.OLEDB.4.0', @filename , NULL, 'Excel 8.0', NULL

--Create temp table for spreadsheet meta-data
create table #tmp_columns
      (      TABLE_CAT sysname collate database_default NULL,
            TABLE_SCHEM sysname collate database_default NULL,
            TABLE_NAME sysname      collate database_default NOT NULL,
            COLUMN_NAME sysname collate database_default NULL,
            DATA_TYPE smallint NOT NULL,
            TYPE_NAME sysname  collate database_default NULL,
            COLUMN_SIZE int NULL,
            BUFFER_LENGTH int NULL,
            DECIMAL_DIGITS smallint NULL,
            NUM_PREC_RADIX smallint NULL,
            NULLABLE smallint NOT NULL,
            REMARKS nvarchar(255) collate database_default NULL,
              COLUMN_DEF nvarchar(128) collate database_default NULL,
              SQL_DATA_TYPE smallint null,
              SQL_DATETIME_SUB smallint NULL,
            CHAR_OCTET_LENGTH int NULL,
            ORDINAL_POSITION smallint,
            IS_NULLABLE varchar(254) collate database_default NOT NULL,
            SS_DATA_TYPE tinyint null)

--Extract info.  See sp_tables_ex for other info available
INSERT #tmp_columns
      exec sp_columns_ex  'MyBook1'

IF (select count(1) from #tmp_columns where column_name = 'Comments') = 1
BEGIN      

--Once you have a linked server you can query it like any other datasource.

      SELECT @foo = Due, @boo = Comments from [MyBook1]...[importrange]

END

EXEC master..sp_dropserver 'MyBook1'
0
 
LVL 12

Expert Comment

by:kselvia
ID: 12013131
Thanks! You put me on the Hall of Fame.  At the rate BillAn1 goes though, it will only be for a few hours but at least I was there :)
0

Featured Post

Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

623 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question