All,

I am looking for a vb or java script that will delete an sql table named
Daily_List, then import an excel spreadsheet from a network share (in a
public folder) into sql server 2000 and name it Daily_List.

a few things to note, the excel spreadsheet name changes daily based on
the date, for instance todays filename is "filename 04242004.xls",
tomorrows will be "filename 04252004.xls" and so on.


Any and all help is greatly apprecitated,

Thanks,

Fred

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

One way to do this would be to

DROP TABLE Daily_List

You can easily predict the name of the Excel File and you can build it up so
something like this

Declare @xlfilename varchar(120)
Declare @statement varchar(255)
set @xlfilename = CONVERT(char(8),getdate(),112)
SET @statement = 'SELECT * INTO Daily_list FROM OpenDataSource(
''Microsoft.Jet.OLEDB.4.0'',''Data Source="c:\' + @xlfilename + '.xls";User
ID=Admin;Password=;Extended properties=Excel 5.0'')...Sheet1'
print @statement
exec(@statement)





--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Fred" <Fred (AT) home (DOT) com> wrote

This work very well with one execption.....My file names date format is
mmddyyyy, and sql either puts dashes between dates or uses the format
yyyyddmm. is there a way to make sql flip flop the date?

as posted before, my filename is "filename date.xls" (filename
04262004.xls) and the date changes daily. sql does not like that.

can anyone help me with a script that can do this?


thanks

Fred

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!

Then you build the statement yourself. You can easily grab the date and
turn it into that format. You then append after casting to a string the new
dateformat to the filename.

--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


"Fred" <Fred (AT) home (DOT) com> wrote