Excel Import to sqlserver database

Hi all,

this is thiru from India, hope i shall get answers here for my questions.

1. I need to import an Excel spread sheet to a remote sql server database through ASP.Net web application. I brief the process im following now please go through it.

Import Process:

a. select a fiile(.xls) and upload it to server.
b. using M/S Odbc Excel driver, and the uploaded excel file as datasource,
c. query the excel sheet to populate a dataset.
d. iterate through the rows of the dataset(I could not bulk copy the excel data, because
have to check the database, if record exists then update, else insert) to import to the
SQL Database

Performance issues:
1. I have to import spreadsheets having upto 60,000 records or even more at a time.
2. Is this a good option to use a webapplication for this task (I use this approach because
my boss wants to do so).
3. some times the excel file size grows up to 7 mb(Though i shall adjust config settings,
uploading and then querying a 7 mb file shall be an ovverhead i think.)
4. is there any possibility to get the datasource with out uploading the file to the server (Like
modifying the connection string as "datasource=HtmlFileControl.PostedFile" instead,)(I
tried this but it gives me "unspecified error").

please analyse my problem and suggest me a possible solution.
I thank all, for your efforts, of any kind.
have a nice time,
........thiru

hi,

you can directly import excel

to sql server import table

do the processing from the import table

to the staging table.

then from the staging table load it directly to the destination table.

i suggest you use SSIS or DTS or you can linked server

to excell and do the processing with sql server.

the problem with your approach is that you might encounter a dataset limitation.

there is no restartability feature. if your application breaks in the middle of the load process

where half of the data has been loaded it would be a great nightmare.

the first above mentioned approach is used widely in BI and datawarehousing.

here's some explanation on how it works:

1. you import the data to an import table. the import table shall be used

for processing the data .this liberates the datasource from heavy processing

and migrates the proceessing to SQL. if the import fails you can truncate the import table

and re import the data. this can done easily without harming the production.

the idea here is to first load everything into sql server.

2. stage the data to the staging table from the imports. here you do data cleansing

the burden nows reside on the sql server which is not a burden at all but a big problem to asp

from your previous design. Resatrtability. truncate the stage table and do the processing from either

step 1 or 2.

3. bulk insert from the stage to the production table. here your very sure that you are inserting a very clean data

with great speed.

|||

Hello,
Thanls for your efforts, I have proceeded a long way through the application and my most recent challenge is this:

please let me find some help.

For

a couple of weeks i am struggling with an excel import and export

application to sqlserver as i have to do it through an external

application and not through the Export Import Utility in sql server.
My most recent problem is:
The understanding of field values by sql server and excel.
1. I need to import into a table whose fields are varchar type.
2. I have to use as datasource- an Excel sheet(.xls file).
3. Some fields in excel contain numbers (Length up to 10 or
more digits).
4. For this I first format the cells'(in Excel file) datatype
into "Text".
5. Even then after importing into a table(say tblTemp) whose
schema/structure, is

CREATE TABLE [tblTemp] (
[rowId] int IDENTITY(1,1),
[Account Number] varchar(30) not null ,
[Mobile Number] varchar(30) null,
[Name] varchar(100) null
)
, and to import into this table im using select into query
with datasource as the excel file(Data Source=ExcelFile.xls),
the table fields show exponential values(may be float
datatype).
Shall

any one suggest me how shall i get values from excel sheet with

datatype varchar and import into the above said table as varchar values

(with out any exponential types).|||

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

|||

David Hayden wrote:

I recommend just uploading the sheet to a staging table in SQL Server as mentioned by the previous person. You can use SqlBulkCopy to pull this off as mentioned in the following tutorial:

Import / Export Excel Spreadsheet Data into SQL Server Database Table Using SqlBulkCopy

Here are some other tutorials that may be of interest:

Reading and Writing Excel Spreadsheets / Worksheets Using ADO.NET C# DbProviderFactory

Reading Excel Worksheet and Column Schema Information Using ADO.NET 2.0 and GetSchema

The spreadsheet has a schema and you want to make sure it is formatted properly.

Regards,

Dave

I noticed that the codings are in C#.... is there any codings examples doing the same process using VB?

|||

hi thiru

how r u ?

iam facing same problem if have solution please suggest me

thanking u

with regards

purushotham.T

|||The easiest way I found of importing data from an excel sheet to sql server is saving the sheet as a tab delimited text file and then importing it to sql server. View this microsof issue :http://support.microsoft.com/kb/236605
Hope this helps