OpenRowset not reading data right

[PWise]

I am inserting date into a Sql table from a excell Spread sheet That I receive from The BOE with a OpenRowSet Query


This Code Works

insert INTO Tablename ( [Year], School, OSIS, .....,Voucher)
SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0; Database=FolderName\FileName.XLS',
'SELECT [Year], School, OSIS, .....,Voucher 
FROM [''WorkSheetName $'']') VoucherS

On Certain Spread Sheets I get This Error

Disallowed implicit conversion from data type money to data type varchar, table 'DbName.dbo.TableName', column 'Voucher'. Use the CONVERT function to run this query.

when I run the Select Statement in QA

SELECT *
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0; Database=FolderName\FileName.XLS',
'SELECT [Year], School, OSIS, .....,Voucher
 FROM [''WorkSheetName $'']') VoucherS

It returns field Voucher as a null

If I run this code

insert INTO Tablename ( [Year], School, OSIS, .....,Voucher)
SELECT [Year], School, OSIS, ....., Convert(varchar(50),Voucher) Voucher
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0; Database=FolderName\FileName.XLS',
'SELECT [Year], School, OSIS, .....,Voucher 
FROM [''WorkSheetName $'']') VoucherS

It inserts the data but the first row for field voucher it leaves as null.

I know what is causing the problem on the last line of the Spread Sheet in the voucher column there is a total field that is formatted as Money.

Any help will be appreciated

PWise

 

[PWise]

found a solution

insert INTO Tablename ( [Year], School, OSIS, .....,Voucher)
SELECT [Year], School, OSIS, ....., Voucher 
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 
'Excel 8.0;[COLOR=red]IMEX=1;[/color] Database=FolderName\FileName.XLS',
'SELECT [Year], School, OSIS, .....,Voucher 
FROM [''WorkSheetName $'']') VoucherS

Via this link

http://support.microsoft.com/kb/194124/EN-US

I thanked Skip for his help in this post

http://www.tek-tips.com/viewthread.cfm?qid=1385064