Parsing Excel Spreadsheet

[Hugh McLaughlin]

Hello Everyone and thanks for your help in advance. I am
working on an application that requires the parsing of an
Excel spreadsheet that will be loaded into a SQL Server
table. An example of the spreadsheet is located at:

http://ratesheets.sollen.com/script/...ts/interfirst/
ratesheets/IFRS1_20030722_094227.xls

At the heart of the problem is the sometimes varying
layout of the spreadsheet. For example, the application
requires that the date between cells H13 and D23 be
loaded into the table. However, the same data may be
located between H12 and D22 tomorrow. So the application
needs to be able to determine the starting point of the
data. Fortunately, the headings do not change, so one of
the obvious ways to handle this is to move through each
cell (i.e. A1, A2, B1, B2) until the known header value
is achieved. However, this seems to be a very
inefficient way to handle this. Is there another way to
identify the starting point to begin processing, i.e. in
English "Find the cell that equals 30 YEAR FIXED -
Program 100". Any ehlp would be greatly appreciated.
Thanks.

[Colin Young]

Excel has a find function. You can use that to set the active cell to the
cell containing the desired text.

Colin

"Hugh McLaughlin" <hu**@kmcnetwork.com> wrote in message
news:05****************************@phx.gbl...

inefficient way to handle this. Is there another way to
identify the starting point to begin processing, i.e. in
English "Find the cell that equals 30 YEAR FIXED -
Program 100". Any ehlp would be greatly appreciated.
Thanks.

[Tian Min Huang]

Hello Hugh,

I don't think there is any easy to achieve this. If you know exactly where
the data is when you automate Excel, you could input it as a parameter in
your function to get the data. If you don't know, you have to search the
data by yourself. Or is it possible for you to create a special pane in
Excel file to contain the beginning area of valid data?

Regards,

HuangTM
Microsoft Online Partner Support
MCSE/MCSD

Get Secure! ¨C www.microsoft.com/security
This posting is provided ¡°as is¡± with no warranties and confers no rights.

[Hugh McLaughlin]

Thanks for your response Tian. The Excel file is
generated by a thrid party, so I am unclear as to how I
could automate a pane. Does Colin's Find fucntion idea
make sense. How do you access it from an Asp.Net page?

-----Original Message-----
Hello Hugh,

I don't think there is any easy to achieve this. If you know exactly where the data is when you automate Excel, you could input it as a parameter in your function to get the data. If you don't know, you have to search the data by yourself. Or is it possible for you to create a special pane in Excel file to contain the beginning area of valid data?

Regards,

HuangTM
Microsoft Online Partner Support
MCSE/MCSD

Get Secure! ¨C www.microsoft.com/security
This posting is provided ¡°as is¡± with no warranties and confers no rights.

.

[Guillaume Hanique]

I never use A1, A2, B1 and B2 when programming. I always use the row
and columns. If you use the Find method to find the header, then you
can find the row and the column with Cel.Row and Cel.Column. Column
would be the starting column. So the first column of your table would
be StartColumn + 0, the second column of your table would be
StartColumn + 1.

You can find a cell by ExcelApp.Workbooks(1).Sheets(1).Cells(Row,
Column)

Hope this helps.

Guillaume Hanique

On Tue, 22 Jul 2003 15:29:08 -0700, "Hugh McLaughlin"
<hu**@kmcnetwork.com> wrote:

Hello Everyone and thanks for your help in advance. I am
working on an application that requires the parsing of an
Excel spreadsheet that will be loaded into a SQL Server
table. An example of the spreadsheet is located at:

http://ratesheets.sollen.com/script/...ts/interfirst/
ratesheets/IFRS1_20030722_094227.xls

At the heart of the problem is the sometimes varying
layout of the spreadsheet. For example, the application
requires that the date between cells H13 and D23 be
loaded into the table. However, the same data may be
located between H12 and D22 tomorrow. So the application
needs to be able to determine the starting point of the
data. Fortunately, the headings do not change, so one of
the obvious ways to handle this is to move through each
cell (i.e. A1, A2, B1, B2) until the known header value
is achieved. However, this seems to be a very
inefficient way to handle this. Is there another way to
identify the starting point to begin processing, i.e. in
English "Find the cell that equals 30 YEAR FIXED -
Program 100". Any ehlp would be greatly appreciated.
Thanks.

[onedaywhen]

Hugh,
Have you considered querying the Excel worksheet using ADO.NET? You
talk of headings so there's are chance your data is similar enough to
a database table i.e. rows of columns. With SQL you supply the name of
the column, not the Excel range/name. The 'English' phrase you used
sounds similar to a SQL query to me...

"Hugh McLaughlin" <hu**@kmcnetwork.com> wrote in message news:<05****************************@phx.gbl>...

Hello Everyone and thanks for your help in advance. I am
working on an application that requires the parsing of an
Excel spreadsheet that will be loaded into a SQL Server
table. An example of the spreadsheet is located at:

http://ratesheets.sollen.com/script/...ts/interfirst/
ratesheets/IFRS1_20030722_094227.xls

At the heart of the problem is the sometimes varying
layout of the spreadsheet. For example, the application
requires that the date between cells H13 and D23 be
loaded into the table. However, the same data may be
located between H12 and D22 tomorrow. So the application
needs to be able to determine the starting point of the
data. Fortunately, the headings do not change, so one of
the obvious ways to handle this is to move through each
cell (i.e. A1, A2, B1, B2) until the known header value
is achieved. However, this seems to be a very
inefficient way to handle this. Is there another way to
identify the starting point to begin processing, i.e. in
English "Find the cell that equals 30 YEAR FIXED -
Program 100". Any ehlp would be greatly appreciated.
Thanks.

[Hugh McLaughlin]

Thanks for your response. Actually, that is what I am
doing. However, the problem, unless I am missing
something, is that I need to define a range of cells.
Unfortunately, the range changes occasionally, thus a
static reference won't work. At that point, I need to
locate the appropraite starting cell for the range based
on a heading value. Any ideas?

-----Original Message-----
Hugh,
Have you considered querying the Excel worksheet using ADO.NET? Youtalk of headings so there's are chance your data is similar enough toa database table i.e. rows of columns. With SQL you supply the name ofthe column, not the Excel range/name. The 'English' phrase you usedsounds similar to a SQL query to me...

"Hugh McLaughlin" <hu**@kmcnetwork.com> wrote in message

news:<05****************************@phx.gbl>...

Hello Everyone and thanks for your help in advance. I am working on an application that requires the parsing of an Excel spreadsheet that will be loaded into a SQL Server
table. An example of the spreadsheet is located at:

http://ratesheets.sollen.com/script/...ts/interfirst/ ratesheets/IFRS1_20030722_094227.xls

At the heart of the problem is the sometimes varying
layout of the spreadsheet. For example, the application requires that the date between cells H13 and D23 be
loaded into the table. However, the same data may be
located between H12 and D22 tomorrow. So the application needs to be able to determine the starting point of the
data. Fortunately, the headings do not change, so one of the obvious ways to handle this is to move through each
cell (i.e. A1, A2, B1, B2) until the known header value
is achieved. However, this seems to be a very
inefficient way to handle this. Is there another way to identify the starting point to begin processing, i.e. in English "Find the cell that equals 30 YEAR FIXED -
Program 100". Any ehlp would be greatly appreciated.
Thanks.

.

[onedaywhen]

Short answer: automate Excel and use its object model to call the Find
method to find the range, then plug the range into your SQL e.g.

SELECT * FROM [InterFirst Rates$D12:H23]

With ASP.NET there are many ways of automating Excel. I have no way of
telling which is the best approach for your cicumstances. Take a look
at the following KB article for a discussion of the various
approaches:

http://support.microsoft.com/default...b;en-us;311452

"Hugh McLaughlin" <hu**@kmcnetwork.com> wrote in message news:<02****************************@phx.gbl>...

Thanks for your response. Actually, that is what I am
doing. However, the problem, unless I am missing
something, is that I need to define a range of cells.
Unfortunately, the range changes occasionally, thus a
static reference won't work. At that point, I need to
locate the appropraite starting cell for the range based
on a heading value. Any ideas?

-----Original Message-----
Hugh,
Have you considered querying the Excel worksheet using

ADO.NET? You

talk of headings so there's are chance your data is

similar enough to

a database table i.e. rows of columns. With SQL you

supply the name of

the column, not the Excel range/name. The 'English'

phrase you used

sounds similar to a SQL query to me...

"Hugh McLaughlin" <hu**@kmcnetwork.com> wrote in message

news:<05****************************@phx.gbl>...

Hello Everyone and thanks for your help in advance. I am working on an application that requires the parsing of an Excel spreadsheet that will be loaded into a SQL Server
table. An example of the spreadsheet is located at:

http://ratesheets.sollen.com/script/...ts/interfirst/ ratesheets/IFRS1_20030722_094227.xls

At the heart of the problem is the sometimes varying
layout of the spreadsheet. For example, the application requires that the date between cells H13 and D23 be
loaded into the table. However, the same data may be
located between H12 and D22 tomorrow. So the application needs to be able to determine the starting point of the
data. Fortunately, the headings do not change, so one of the obvious ways to handle this is to move through each
cell (i.e. A1, A2, B1, B2) until the known header value
is achieved. However, this seems to be a very
inefficient way to handle this. Is there another way to identify the starting point to begin processing, i.e. in English "Find the cell that equals 30 YEAR FIXED -
Program 100". Any ehlp would be greatly appreciated.
Thanks.

.