Best way of importing Excel to SQL Server

Hello,

I nee to write something that will transfer excel data into an SQL Server
table.

I have for another database application I wrote have it importing Excel
spreadsheet data using cell by cell, row by row method. This is fully
automated so the user can choose whatever spreadsheet they want to import
and press a button which sits on a VB6 frontend.
This has been good for that situsation but it can be very slow when there
are large amounts of data to process.

I am just wondering are there any faster, better alternatives that would
still enable a user to select which excel spreadsheet to import
as the application I am writing now will sit on a website frontend, using
ASP, and I'd really like to try and speed things up if I could.

any advice would be much appreciated.

Thankyou,

Oh, and hello, this is my first post here!

JayneLittle PussyCat wrote:
> Hello,
> I nee to write something that will transfer excel data into an SQL Server
> table.
> I have for another database application I wrote have it importing Excel
> spreadsheet data using cell by cell, row by row method. This is fully
> automated so the user can choose whatever spreadsheet they want to import
> and press a button which sits on a VB6 frontend.
> This has been good for that situsation but it can be very slow when there
> are large amounts of data to process.
> I am just wondering are there any faster, better alternatives that would
> still enable a user to select which excel spreadsheet to import
> as the application I am writing now will sit on a website frontend, using
> ASP, and I'd really like to try and speed things up if I could.
> any advice would be much appreciated.
> Thankyou,
> Oh, and hello, this is my first post here!
> Jayne

DTS Packages are design just for this sort of thing.

Zach|||nib <individual_news@.nibsworld.com> wrote in message news:<2tqnibF21lmapU1@.uni-berlin.de>...
> Little PussyCat wrote:
> > Hello,
> > I nee to write something that will transfer excel data into an SQL Server
> > table.
> > I have for another database application I wrote have it importing Excel
> > spreadsheet data using cell by cell, row by row method. This is fully
> > automated so the user can choose whatever spreadsheet they want to import
> > and press a button which sits on a VB6 frontend.
> > This has been good for that situsation but it can be very slow when there
> > are large amounts of data to process.
> > I am just wondering are there any faster, better alternatives that would
> > still enable a user to select which excel spreadsheet to import
> > as the application I am writing now will sit on a website frontend, using
> > ASP, and I'd really like to try and speed things up if I could.
> > any advice would be much appreciated.
> > Thankyou,
> > Oh, and hello, this is my first post here!
> > Jayne
> DTS Packages are design just for this sort of thing.
> Zach

I second that notion -- however, there are some problems with
importing Excel data into SQL Server.

I've noticed that if you have a column whose first value is numeric,
and then there are other values in that column that are not numeric,
those values get dropped. Kind of a pain.|||>>
>> DTS Packages are design just for this sort of thing.
>>
>> Zach
>
> I second that notion -- however, there are some problems with
> importing Excel data into SQL Server.

Will a DTX package still be as flexible as what I am doing now,
i.e. would a user still be able to browse through a VB6 / webfrontend to the
spreadsheet they want to import,
i.e. can it be coded in VB6 / ASP?
> I've noticed that if you have a column whose first value is numeric,
> and then there are other values in that column that are not numeric,
> those values get dropped. Kind of a pain.

There are loads of problems with importing excel into sql server, I've found
if the spreadsheet is not set up as a text format data then strange things
will happen.
Similar when you export from sql server to excel, leading zeros of numeric
data gets dropped etc...
Anyway,
Enough of that, thanks

Jayne|||Little PussyCat wrote:
>>>DTS Packages are design just for this sort of thing.
>>>
>>>Zach
>>
>>
>>I second that notion -- however, there are some problems with
>>importing Excel data into SQL Server.
>
> Will a DTX package still be as flexible as what I am doing now,
> i.e. would a user still be able to browse through a VB6 / webfrontend to the
> spreadsheet they want to import,
> i.e. can it be coded in VB6 / ASP?

You'd have to investigate exactly how much you can do with it. Even if
DTS requires a hard-coded file name, you could always have your front
end copy the chosen spreadsheet to said name and then run the dts package.

Zach