Impot data from EXCEL file

Question

Post reply

Impot data from EXCEL file

SQLRO

SSCrazy

Points: 2923
More actions
January 26, 2006 at 8:44 am

#67290

Hi,
I want to Import the data from an EXCEL file which is being uploaded by a web application. Name of the file will be different every time. I have to use Stored procedure for acheiving this. I need to import using a column list because few columns are auto generated in the back end. I am too new to DTS and do not know whether it will work here. Is there any better way to do it?

Thanks in advance
Rohit

Viewing 4 posts - 1 through 3 (of 3 total)

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

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 1

If:

a)The excel spreadsheet always has same number of columns

b)You want to import the same columns

c)The excel tabname is always the same

...You can use a dynamic properties task to change the name of the source spreadsheet dynamically.

Effectively this repoints the package to a different source WITHOUT losing the transformation mappings.

If any of the above answer false, then dts is not the way to go unless you bring in the entire spreadsheet into a table then use an sp to write to the target.

This effectively means creating a table with a matching column for the maximum number of columns in a spreadsheet, naming them F1, F2...etc

------------------------------
The Users are always right - when I'm not wrong!

Todd Painter-134230 SSC Veteran Points: 207 More actions · Answer 2

If you must do it from a stored procedure you can use the following syntax to select from the excel spreadsheet:

insert into table

select * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',

'Excel 8.0;DATABASE=d:\excelfile.xls', 'Select * from [sheet1$]')

It requires that the worksheet name (sheet1$) is always the same. If the name of the file itself is always changing and it's impossible to push back on the user community to follow a standard then you have at least two options.

1. rename the spreadsheets in your directory one at a time to a common file name as you load them using xp_cmdshell and the ren command

2. use xp_cmdshell and the dir command to get a list of excel files

ex. insert into table1 --table1 should have one column 256 characters wide

exec master..xp_cmdshell 'dir d:\*.xls'

You can then use string functions like substring and charindex to get the name of any excel files in the directory

You would then build a query string and replace "d:\excelfile.xls" in the query above with your result. Then run the query using sp_executesql

I'd probably use option number 1 or push back on your users to name their files a common name.

SQLRO SSCrazy Points: 2923 More actions · Answer 3

Thanks a ton for your reply. The option of using OPENROWSET is best suited fot my application.

Thanks a ton

Regards

Rohit