Validating and uploading excel in database

[jack]

Hi.
My project requires to upload the excel file and populate it into
the database. By doing so it should also check whether the excel file
selected is in the right format.

for example if the file contains the Name , Address and Age fields.

Then the Name , Address column should accept all characters while the
age column should accept only numbers

The columns also should be in correct sequence.
I would be glad if some one provide me some hint how to do so..

Thanks for replying me.

[Marc Gravell]

Would csv (via Excel) suffice? If so there are some great (fast and
free) CSV readers that you can use on an uploaded file (Request.Files)
- e.g. www.codeproject.com/cs/database/CsvReader.asp; you can attach
the csv-reader to the .InputStream (of the file), and read over the
data. Now the clever bit: you can use an IDataReader (which this is)
as the input to a SqlBulkCopy operation (if you are using sql-server),
and essentially BCP the data into the database. Personally I'd bulk-
insert into a staging table, and then execute a stored-procedure to
merge with live data.

For validation; could rely on the databaes parser; could BCP as
varchar, then valdiate (ISNUMERIC) at the database, or could inject a
validating reader so that you check each row as SqlBulkCopy asks for
it (trickier, but still easy).

If you must use Excel, then you need either to save it locally and use
ADODB to open it (slow), else look into Excel parsers. Personally, I'd
go for csv if it is an option.

Marc

[jack]

thats sounds good. but all of sudden the requirement has been changed,
now my boss wants that the form should contain the excel component,
The user will make changes in the excel file .
And then the same excel file will be saved in the database. May be i
will parse the file. or save the object of excel itself.

There is a method through which we create an excel on web page by
flushing the html code below is the sample code which i have tried

Dim dsExport As New DataSet()
Dim tw As New System.IO.StringWriter()
Dim hw As New System.Web.UI.HtmlTextWriter(tw)
Dim dgGrid As New DataGrid()

dgGrid.DataSource = getData()

' Report Header
hw.WriteLine("<b><u><font size='5'Student Marking Report </
font></u></b>")

' Get the HTML for the control.
dgGrid.HeaderStyle.Font.Bold = True
dgGrid.DataBind()
dgGrid.RenderControl(hw)

' Write the HTML back to the browser.
Response.ContentType = "application/vnd.ms-excel"
Me.EnableViewState = False
Response.Write(tw.ToString())
Response.End()
Now i want the changes should be saved in the database .. How will do
so....>?????

[Marc Gravell]

Your boss needs to be realistic; that will be prone to /so/ many
issues; client install, concurrency, write access... perhaps something
like google docs would be a better fit? or alternatively, sharepoint
with office integration?

Marc

[jack]

Tried to do hands dirty with OWC (Office Web Component) and it worked
well. It proved to be the right solution for the purpose. Just gave
the POC on OWC. and waiting for the Global IT approval team for the
suggestions . But still I would like to have few more solutions on the
same pathway.