importing SQL data

[Guest]

I teach a very basic Excel class for a large healthcare facility. I received
a question from a student that I can not answer. She imports SQL data into
Excel each day. When she imports, the results include periodically throughout
the spreadsheet info that looks to be header information - see below. She
wanted to know if there was away to remove all occurances of this info
through out the spreadsheet without having to manually remove each one. I am
not familiar with SQL. Thanks!

May 14, 2007 Pa ge 5

(orderabl _I_act) TESTS WHICH CAN BE ORDER ED IN DIVIDUALLY
(active)
========= ============================ ========= ===== =========== ====== =====
TEST ID TEST NAME TYPE LOC DEPT WRKST SPE
========= ============================ ========= ===== =========== ====== =====

 

[Guest]

With all due respect to the user, that output did not likely come from the
results of a SQL query. It probably comes from a report that was exported to
a text file.

There are tools which specialize in removing page headings and parsing the
data into fields. Your company may already have one.

Otherwise, If the report is structured such that each page is a set number
of rows with the top x-rows as headings and the bottom y-rows are footers,
you could run a repeating series of numbers down a column and remove those
values.
Example:
Remove rows with values 1 through 8 and rows with values 39 through 42

For partial pages, there might be a specific value that flags the end of the
page. For example, a formula could restart the count in the row below the
row containing "Printed on:". The key is to determine appropriate patterns.

Is that something you can work with?

Post back with more questions.
***********
Regards,
Ron

XL2002, WinXP

 

[Guest]

Thanks Ron. I spoke with the staff member and you are correct - the data came
from a report that was exported to a text file. I am not for sure how to set
up the removal of the rows. If rows 1-8 are the heading and the repeat of the
heading starts again at rows 61-68 with 53 rows inbetween, do we use a
formula or a macro, or what? Thanks for all of your help!

 

[Guest]

with
the report beginning in A1

Try something like this:
Insert a row above the report
Insert 2 columns before the report
A1: Delete
B1: Line
C1: Report

In Col_B, starting in B2, create the repeating sequences of line numbers 1
through 60

A2: =B2<9
Copy that formula down as far as you need
(That formula will return TRUE for each row to be deleted, otherwise: FALSE)

Then.....Select from A1 down as far as you need

From the Excel main menu:
<data><filter><Autofilter>
Click the dropdown in A1 and select TRUE
(Now, only the rows to be deleted are displayed)

Select from the 1st TRUE value down through the last TRUE value
<edit><delete>......(in filter mode, you can only delete entire rows)

<data><filter><autofilter>....to remove the filter
(now only the valid data should remain)

Is that something you can work with?

***********
Regards,
Ron

XL2002, WinXP

 

[MH]

Sue,

Before making work for yourself, you should enquire if it is possible to
extract the rowset that the report is based on. Your user should have a
word with the DBA responsible for the database with regard to setting up an
ODBC connection (which can be read-only) so that this data can be imported
without the header information.

MH