Problem
what is the best way to parse / retrieve / extract excel file data stored as binary data in a SQL 2005 field?
(therefore, all data can ultimately be stored in other fields of other tables.)
background
Basically, our client needs a large amount of detailed data from their users. Unfortunately, our client cannot require any db export from its user. therefore, our client must provide some kind of user interface so that the user can enter data. the user interface, which our client decided to be acceptable to all of its users, exceeded it, because it has a fairly reliable interface. therefore, given all this, and our client needs this data to be analyzed and stored in their db automatically.
we tried to convince our client that users would do it exactly once, and then insisted on exporting db! but the client cannot demand the db export of its users.
- our client requires us to analyze the excel file
- user users use excel as the "best" user interface to enter all the necessary data.
- users are provided with empty excel templates that they must fill out
- these templates have a fixed number of uniquely named tabs
- These templates have several fixed areas (cells) that need to be filled
- these templates also have areas in which the user will insert up to a thousand lines with the same formatting.
- Upon completion, the excel file is sent from the user by the standard download of the html file.
- our client stores this file in a SQL database
given
- standard excel file (.xls) (native format, not a comma or tab)
- file is stored in a
varbinary(max)SQL 2005 field - excel "" , .. , - (, , , , "",...)
- SQL 2005 ( , SSIS?)
- ()
- ( )
- "" , .. , (, , , , , "",...)