OPENROWSET(INSERT) ERROR COLUMNS NAMES

Question

OPENROWSET(INSERT) ERROR COLUMNS NAMES

basit-387423

Old Hand

Points: 310
More actions
March 24, 2008 at 7:04 pm

#70144

When I remove columns from my excel spread sheet and my code is as follows, it gives me an error.
I donot want to hard code my columns names in the excel file , and would like them to be generated from teh dynamic sql that I am passing the openrowset...
set @provider = 'Microsoft.Jet.OLEDB.4.0'
set @ExcelString = 'Excel 8.0;HDR=yes;Database=' + @fn
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *
FROM [Sheet1$]'')
'+ @sqlRIDBID + '')
exec('insert into OPENrowset(''' + @provider + ''',''' + @ExcelString + ''',''SELECT *
FROM [Sheet2$]'')
'+ @sqlRELID + ' ')
sqlRIDBID ='select * from table1'
sqlRELID ='select * from table2'
the above gives me an error....
Server: Msg 213, Level 16, State 5, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Server: Msg 213, Level 16, State 1, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
even when I change the above two sql statments with the column names, then it also gives me an error, ??? the same one as above...
It seems that the only way it works is that as long as column names are there in the template file??? I donot want to hard code the headers in the template file as want to generate them dynamically from the sql that passed to the open rowset.

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Lester Policarpio SSCertifiable Points: 7109 More actions · Answer 1

Are you sure the numbers of columns in the excel file is equal to the number of columns in your '*'? select * from table1? please check...

"-=Still Learning=-"

Lester Policarpio

basit-387423 Old Hand Points: 310 More actions · Answer 2

Thanks for your reply. The thing is this, I donot want to hard code the column names in my excel, so there are NO column names mentioned in the excel. I want teh column names to be passed from the dynamic sql that is being generated by me in the openrowset. As you can see my openrowset command code, i.e. I am doing select * from table and select * from sheet1$, so that I want the columns names to be also automatically appended to the sheet once shjeet is created. I guess what I want to know is it possible to create an excel file without hardcoding the column names in the excel template file.

basit-387423 Old Hand Points: 310 More actions · Answer 3

Even this simple query also doesnot work, the issue is still the same... I want the column names to be taken from the select * statement and donot want to hard code the column names in the excel file...

INSERT INTO OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'Excel 8.0;HDR=YES;Database=C:\pubsTest.xls', 'SELECT * FROM [Sheet1$]')

SELECT * FROM pubs.dbo.authors

Server: Msg 213, Level 16, State 5, Line 1

Insert Error: Column name or number of supplied values does not match table definition.

david.donativo SSC Rookie Points: 31 More actions · Answer 4

I am having the same issue using SQL OPENROWSET, in that I do not want to specify the column names within the .xls file.

The data has already been formatted (including header row as part of my SQL Dataset to be inserted into the .xls file.

Has anybody been able to resolve?

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 5

Here's an excellent article which, even if it doesn't directly answer your question, will ensure that the method you are using for writing to Excel from SQL Server is appropriate for the task.

http://www.simple-talk.com/sql/t-sql-programming/sql-server-excel-workbench/

Cheers

ChrisM

^{“Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw}

For fast, accurate and documented assistance in answering your questions, please read this article.
Understanding and using APPLY, (I) and (II) Paul White
Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

greg.bull SSCrazy Points: 2093 More actions · Answer 6

Probably several years too late, but I've just been working in the same problem...

Assuming your Excel is set up with some report header information, then when you try to select it via openrowset, you'll see this header info returned. It looks unstructured. It doesn't mean there's a problem there.

What I do is to select a large bunch of columns to the right of my last data column, and delete them. They're probably blank already, but this seems to help.

Then, I place the Excel cursor in a cell below my column headers, and change the types of the cells to suit (ie. Short Date for a date column) the data. I save and close the Excel with the cell with focus in the under the left most data item. Not sure if this latter part makes any difference, as yet.

If I don't do the above, I sometimes see the error you mentioned. But if I do, all seems to work magically. Seems a bit of black art to me, especially when trying to change the format of the data from within Excel...

Regards, Greg.