Import Excel file into SQL Server

Could someone provide me with a URL that explains how to set up DTS to import an Excel file into SQL Server?

I plan to run this infrequently so there is no need to schedule a recurring run.

Thanks,
CraigHere is sample how to get rows from Excel file in SQL - use OPENDATASOURCE:

select * from OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source="\\comp\share name\filename.xls";Extended Properties=Excel 8.0')...['2005$']

where ['2005$'] is Sheet name in Excel (2005).
Format ['name$'] uses only for sheets started with digit or contained spaces.
overwise:
Sheet1$ for sheet with name Sheet1|||Thanks for the reply. I will try that.

If I can get it imported, can I include it as a temp table in queries and would I need a linked server for that?

Craig

Import CSV file data from URL/HTTP

Hi,

I'm truly a newbie to this, so please ignore any stupidity :-)

I am trying to (in SQL2005 - integration services)

1) Import a CSV file over http (using an URL)
2) schedule the job (so it is done regularly)
3) *bonus* edit the URL dynamically

I have a web link, e.g.

http://dummy.com/cgi-bin/run.cgi?from=050101&to=050102

when accessing this "page" the result is sort of a CSV file. It looks something like this:

Date;cust;id;...
20050101;CLIENTA;0121210310; ..
20050101;CLIENTB;238241268;...
...

Step 1) How can I import this to be used in a Data Flow task (or similar)?I have managed to set up a HTTP Connection Manager, but I can't seem to use this as a "Data flow source"!? The "Web service task" seem to be the only task accepting a http conn. manager as input, but that requries a wsdl file and does not seem to be the right solution for me.

Step 2) When succeeding with step 1), how can I automate the solution? so the web link is accessed every day.

Step 3) Ideally, I would like to alter the weblink daily (by changing the dates in the link). How can this be done?

Hope someone can help me!

Cheers,

Carl

One more thing that might help:

What I am trying to do is really imitate the "web query" functionality in Excel.

|||

You can download a file using the webclient in a script task

http://www.sqljunkies.com/HowTo/A77EA078-79A6-48D4-B55C-14D103820413.scuk

Then use a data flow as normal.

Its good splitting the download and flow so that you can run the flow without having the to download the file, and also means you don't have to write a custom source for the data flow.

|||

Thanks a lot

This helped me get started. After figuring out how to add variables to the project I managed to do what I want!

Import CSV file data from URL/HTTP

Hi,

I'm truly a newbie to this, so please ignore any stupidity :-)

I am trying to (in SQL2005 - integration services)

1) Import a CSV file over http (using an URL)
2) schedule the job (so it is done regularly)
3) *bonus* edit the URL dynamically

I have a web link, e.g.

http://dummy.com/cgi-bin/run.cgi?from=050101&to=050102

when accessing this "page" the result is sort of a CSV file. It looks something like this:

Date;cust;id;...
20050101;CLIENTA;0121210310; ..
20050101;CLIENTB;238241268;...
...

Step 1) How can I import this to be used in a Data Flow task (or similar)?I have managed to set up a HTTP Connection Manager, but I can't seem to use this as a "Data flow source"!? The "Web service task" seem to be the only task accepting a http conn. manager as input, but that requries a wsdl file and does not seem to be the right solution for me.

Step 2) When succeeding with step 1), how can I automate the solution? so the web link is accessed every day.

Step 3) Ideally, I would like to alter the weblink daily (by changing the dates in the link). How can this be done?

Hope someone can help me!

Cheers,

Carl

One more thing that might help:

What I am trying to do is really imitate the "web query" functionality in Excel.

|||

You can download a file using the webclient in a script task

http://www.sqljunkies.com/HowTo/A77EA078-79A6-48D4-B55C-14D103820413.scuk

Then use a data flow as normal.

Its good splitting the download and flow so that you can run the flow without having the to download the file, and also means you don't have to write a custom source for the data flow.

|||

Thanks a lot

This helped me get started. After figuring out how to add variables to the project I managed to do what I want!