Import Spec for excel?

[SirPoonga]

Is there a way to have an import spec like access does for importing
files? I have a text file with fixed width columns. I can't import
into access because the file is generated from screen dumps of a
mainframe system. So there are footer and headers in the middle of the
data.

Currently to get the info into Access someone imports into excel,
deletes header lines, exports to tab delimited. I created a function
in access to automate the rest of their process. However since there
isn't a spec that I know of for excel the person that imports the file
has to set field width on import and take out the ones excel
auto-generates. However sometimes that person misses some field
breaks.

If I could import into access I just make an Import Spec which
specifies field widths.

Is there a programatical way of doing this if there is no import spec?
Actually, yeah, there is, can read in file line by line and use Mid$()
for each field. Is there a better way?

 

[Harlan Grove]

SirPoonga wrote...

Is there a way to have an import spec like access does for importing
files? I have a text file with fixed width columns. I can't import
into access because the file is generated from screen dumps of a
mainframe system. So there are footer and headers in the middle of the
....
Is there a programatical way of doing this if there is no import spec?
Actually, yeah, there is, can read in file line by line and use Mid$()
for each field. Is there a better way?

This brings back bad memories. Screen dumps from a CICS system app to
fetch data your IT department can't or won't provide a cleaner way to
access?

If so, any scripting language would be better than Excel to strip out
headers and footers. If the first 4 and last 2 lines of every screen
dump were unwanted headers and footers, with no blank lines between
screen dumps, gawk could strip out headers and footers using the
command line

gawk "(NR % 25) > 4" screendump.txt > strippedscreendump.txt

That's what I used back when I had to do this sort of thing.

On the other hand, if you want to use Excel to strip off headers and
footers, and the screen dump file were formatted I assumed above, just
load it into Excel with no field parsing, then insert a column to the
left of the data and a row above it so the imported data begins in cell
B2. Enter x in A1, y in B1 and the following formula in A2.

=MOD(ROW()-1,25)>4

Fill A2 down into as many rows as the data in col B. Then select the
entire col A and B range from row 1 down, run the menu command Data >
Filter > AutoFilter, select A1, choose TRUE from the drop-down list.
This should filter the nonheader rows. Select the resulting col B range
beginning in cell B2, copy it, and paste it into a text editor like
Notepad, then save it as a text file. That text file you should be able
to import into Access.

 

[Harlan Grove]

Harlan Grove wrote...
....

If so, any scripting language would be better than Excel to strip out
headers and footers. If the first 4 and last 2 lines of every screen
dump were unwanted headers and footers, with no blank lines between
screen dumps, gawk could strip out headers and footers using the
command line

gawk "(NR % 25) > 4" screendump.txt > strippedscreendump.txt

....

Screwed up! That only strips off first 4 and last 1 of 25 lines/screen.
To strip off the last 2 rather than just the last one would require

gawk "4 < (x = NR % 25) && x < 24" screendump.txt >
strippedscreendump.txt

 

[SirPoonga]

Ok, that's cool, gives me ideas to work with.

Unfortunately this is the only way the data is coming. I'm a temp and
they are moving form one mainframe software to another (due to a
merger). They want to keep me around until that is complete and I have
finished my original project until the switch over. So we are coming
up with ideas. One of them is to automate this process they go through
daily. Someone cleans up that info BY HAND! Yikes!

After some thinking, I think I might be able to do this in access which
is where the data is going reside eventually.
I can import using an Access Import Spec. The first two fields will be
date and order number. I will first run an update query with Trim() to
take off unneeded spaces. Then one to check if the date field is not
null and not like "*/*/*", this will delete all the page headers and
footers. Then delete all the records where order number is null. That
will delete any other bad and/or unused lines. As long as I have an
auto-increment field the data will stay in order I can then make a
function to go through and fill in the date fields properly (date shows
up only once on the report for all the orders for that date).
Sorry, was thinking out loud...
I just ran a quick test of that. It looks like it will work. I can
easily program an Access form to do all that with one button click.
Should be easy for the user to use.

Thanks for the info, it helped me look at the situation in a different
way.

 

[Harlan Grove]

After some thinking, I think I might be able to do this in access which
is where the data is going reside eventually.

....

Actually, Access uses VBA, so you could use VBA to read the screen dump file
a line at a time using the old BASIC sequential file I/O statements.

 

[SirPoonga]

Yep, figured it out. I dump the file to a table using fixed width. I
then delete records that are blank (in this case no date, no order
number). Then delete where the date doesn't match */*/*, this will
delete records with header and footer screen dump info. And since I
import with an autoincrement ID they list in order with a query. The
report shows the date once for each order for that day. So I then loop
through the table in order and keep track of the last date I came
across. If the date field is filled change the last date used. If it
is null fill it with the last date used.

Works great. Just saved 20 minutes of manual work to a couple of
seconds