Solved

Trapping errors from OpenDataSource

Posted on 2004-08-31
3
404 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
  • 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

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

760 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

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now