SQL - Insert from Excel HELP!

[Guest]

I fianlly got the connecton. now i am trying to insert data.

What is wrong with the SQL?

INSERT INTO rpt_NewLoanMonitor SELECT * FROM [DataSource] IN 'C:\Working
Files\SQL_Scheduler.xls' 'Excel 8.0;'

It says there is a problem near the keyword IN ...

Thanks in advance
Candyman

 

[Guest]

This is going onto a SQL Server Db

 

[gimme_this_gimme_that]

Long shot: Add keyword values and swap out spaces in the path with
%20.

Try:

INSERT INTO rpt_NewLoanMonitor VALUES SELECT * FROM [DataSource] IN 'C:
\Working%20Files\SQL_Scheduler.xls' 'Excel 8.0;'

Even if the syntax is OK the statement won't work if the number of
columns in rpt_NewLoanMonitor doesn't equal the number of columns in
SQL_Scheduler.xls.

 

[gimme_this_gimme_that]

So? What happened when you tried it?

 

[Guest]

I'll have to give it a try tomorrow am. Points to ponder
1) the columns are the same as i just built the table and added one record
of data from the excel spreadsheet. (created an insert statement using the
cell values from excel)
2) I was wondering if it had anything to do with the last of the line "...
Scheduler.xls' 'Excel 8.0;' " There is no divider between the file name and
'Excel 8.0'.
3) If a data field is a smalldatetime data type, should the excel cell be
officailly formatted to date at least? Or would this nessitate creating a
record set of some sort. and programatically formatiing anything that
isdate(cell.value) = true to be a date to a date?
4) what would be the fastest way to extract the actual statement being
passed to SQL Servrer?
5) I tried using [DataSource] refering to a range name. the name was built
by setting the currentregion.name to "DataSource". should the reference in
the Sql statement be...["DataSource"] instead?

I really appresiate your input on this.
thanks again
candyman