How to get from downloaded entries to email more efficiently

[genegold]

Each week our university library department gets a shipment of new
books from a distributor and we email ourselves (from their site) the
"short" form of their shipping list, then we manually edit out
numerous extraneous fields and empty space and email the resulting
list to reviewers. With usually a 100 or more books per week, the
editing step is obviously tedious and time consuming. We would like
to do this transformation from download to email a lot more
efficiently, but staff has felt hamstrung by a lack of database format
cooperation from the distributor and by its own failed attempts in
trying to work it out. The email from the distributor's site includes
all fields appropriate to that entry, meaning some fields we need will
show in some entries but not others (e.g, Series Title). I could
probably do this in SAS, were it available, by reading in fields and
then exporting, but am not sure how to accomplish it in Access. Any
advice about how to do this and/or where to get help via Access (or
Excel?), online or published, would be greatly appreciated.

Below is an example of a complete downloaded entry for one title,
containing the maximum of what we need. What we want to end up with
for the email in this case is Title, ISBN, Author, Editor, Publisher,
Pub Year, Binding, LC Class, Series Title, Series Title, Series Volume
(in most cases the last three entries won't be present or needed). We
eliminate all other fields for the email.

Shipped Item YBP Order Key: 99819790827
Title: WOMEN AND THE CHURCH IN MEDIEVAL IRELAND, C.1140-1540. ISBN:
9781846821455
Author: HALL, DIANNE
Publisher: FOUR COURTS PRESS Pub Year: 2008 Binding: Paper
LC Class: BR
US List: 35.00 USD US Status: Orders accepted
UK List: Not Known UK Status: Import Only
Est. US Net: 35.00 USD
Order Date: 3/25/2008
Invoice Date: 6/18/2008 Invoice Number: 443520
Library Note: Add...
shipped to library (6/18/2008) alternate editions GobiTween (1 Book)


Subaccount: 2002-09 Quantity: 1 Fund Code: 713 PO Number:
M0813826
Batch PO: AP-CLAIMS/SLIPS
Initials: KRL Other Local ID: Location: No Substitutions:
No
Local Data 1: Local Data 2:
Local Data 3: Local Data 4:
Order Notes 1:

 

[genegold]

To clarify that last sentence, it's the fields, not just the entries,
that are or are not present, depending on the volume. That's what
makes it more difficult from our perspective.

Gene

 

[pietlinden]

To clarify that last sentence, it's the fields, not just the entries,
that are or are not present, depending on the volume. That's what
makes it more difficult from our perspective.

Gene

The last time I worked on anything like this, we read the file a line
at a time and parsed it. If you have colons delimiting the "field
name" and the value. Then you could use a recordset to add the values
to a table. Not the fastest thing in the world, but fairly flexible.

 

[pietlinden]

Each week our university library department gets a shipment of new
books from a distributor and we email ourselves (from their site) the
"short" form of their shipping list, then we manually edit out
numerous extraneous fields and empty space and email the resulting
list to reviewers.  With usually a 100 or more books per week, the
editing step is obviously tedious and time consuming.   We would like
to do this transformation from download to email a lot more
efficiently, but staff has felt hamstrung by a lack of database format
cooperation from the distributor and by its own failed attempts in
trying to work it out.  The email from the distributor's site includes
all fields appropriate to that entry, meaning some fields we need will
show in some entries but not others (e.g, Series Title).  I could
probably do this in SAS, were it available, by reading in fields and
then exporting, but am not sure how to accomplish it in Access.  Any
advice about how to do this and/or where to get help via Access (or
Excel?), online or published, would be greatly appreciated.

Below is an example of a complete downloaded entry for one title,
containing the maximum of what we need.  What we want to end up with
for the email in this case is Title, ISBN, Author, Editor, Publisher,
Pub Year, Binding, LC Class, Series Title, Series Title, Series Volume
(in most cases the last three entries won't be present or needed).  We
eliminate all other fields for the email.

Shipped Item                    YBP Order Key: 99819790827
Title: WOMEN AND THE CHURCH IN MEDIEVAL IRELAND, C.1140-1540.    ISBN:
9781846821455
Author: HALL, DIANNE
Publisher: FOUR COURTS PRESS     Pub Year: 2008     Binding: Paper
LC Class: BR
US List: 35.00 USD    US Status: Orders accepted
UK List: Not Known    UK Status: Import Only
Est. US Net: 35.00 USD
Order Date: 3/25/2008
Invoice Date: 6/18/2008     Invoice Number: 443520
Library Note: Add...
shipped to library (6/18/2008)  alternate editions GobiTween (1 Book)

Subaccount: 2002-09    Quantity: 1    Fund Code: 713 PO Number:
M0813826
  Batch PO: AP-CLAIMS/SLIPS
Initials: KRL    Other Local ID:      Location: No     Substitutions:
No
Local Data 1: Local Data 2:
Local Data 3: Local Data 4:
Order Notes 1:

Any chance you could post 2 more examples? It's just that it's
impossible to find a pattern from one example. The way we did this
when we were parsing SciSearch data (yes, I know it's not really
legal...) was to read the data a line at a time and use SPLIT and Mid/
Left/Right and the other string manipulation functions. Tedious, but
if the data does not have a standard structure, then you do not really
have a choice. You can use Chuck Grimsby's code from Access Web as a
starting point ... it will read through a text file and keep
PreviousLine, ThisLine, NextLine and things like that which make
dealing with monsters like this easier... then you just need to add
the gory details about the parsing yourself.

Yes, easier said than done, but I might do it for mental exercise....
before my brain atrophies from lack of use.

 

[pietlinden]

Each week our university library department gets a shipment of new
books from a distributor and we email ourselves (from their site) the
"short" form of their shipping list, then we manually edit out
numerous extraneous fields and empty space and email the resulting
list to reviewers.  With usually a 100 or more books per week, the
editing step is obviously tedious and time consuming.   We would like
to do this transformation from download to email a lot more
efficiently, but staff has felt hamstrung by a lack of database format
cooperation from the distributor and by its own failed attempts in
trying to work it out.  The email from the distributor's site includes
all fields appropriate to that entry, meaning some fields we need will
show in some entries but not others (e.g, Series Title).  I could
probably do this in SAS, were it available, by reading in fields and
then exporting, but am not sure how to accomplish it in Access.  Any
advice about how to do this and/or where to get help via Access (or
Excel?), online or published, would be greatly appreciated.

Below is an example of a complete downloaded entry for one title,
containing the maximum of what we need.  What we want to end up with
for the email in this case is Title, ISBN, Author, Editor, Publisher,
Pub Year, Binding, LC Class, Series Title, Series Title, Series Volume
(in most cases the last three entries won't be present or needed).  We
eliminate all other fields for the email.

Shipped Item                    YBP Order Key: 99819790827
Title: WOMEN AND THE CHURCH IN MEDIEVAL IRELAND, C.1140-1540.    ISBN:
9781846821455
Author: HALL, DIANNE
Publisher: FOUR COURTS PRESS     Pub Year: 2008     Binding: Paper
LC Class: BR
US List: 35.00 USD    US Status: Orders accepted
UK List: Not Known    UK Status: Import Only
Est. US Net: 35.00 USD
Order Date: 3/25/2008
Invoice Date: 6/18/2008     Invoice Number: 443520
Library Note: Add...
shipped to library (6/18/2008)  alternate editions GobiTween (1 Book)

Subaccount: 2002-09    Quantity: 1    Fund Code: 713 PO Number:
M0813826
  Batch PO: AP-CLAIMS/SLIPS
Initials: KRL    Other Local ID:      Location: No     Substitutions:
No
Local Data 1: Local Data 2:
Local Data 3: Local Data 4:
Order Notes 1:

Is the structure of the information always:

<Field Name>:<Value><carriage return>?

If so, this is not that hard....
read each line, parse using SPLIT and specify full-colon as the
delimiter. Then you have the Variable/Value pairs in the array and
you can write varData(0) to one column in your table and varData(1) to
another, depending on what line you're on. Of course, that's a
grossly simplistic overview, but hopefully you get the idea.

Pieter

 

[genegold]

Any chance you could post 2 more examples?  It's just that it's
impossible to find a pattern from one example.  The way we did this
when we were parsing SciSearch data (yes, I know it's not really
legal...) was to read the data a line at a time and use SPLIT and Mid/
Left/Right and the other string manipulation functions.  Tedious, but
if the data does not have a standard structure, then you do not really
have a choice.  You can use Chuck Grimsby's code from Access Web as a
starting point ... it will read through a text file and keep
PreviousLine, ThisLine, NextLine and things like that which make
dealing with monsters like this easier... then you just need to add
the gory details about the parsing yourself.

Yes, easier said than done, but I might do it for mental exercise....
before my brain atrophies from lack of use.- Hide quoted text -

- Show quoted text -

Here's a few more examples. They come down laid out as HTML, which is
why the lateral layout in text. In Outlook we convert them to text.
There appear to be tabs between each field (show up in the forwarded
version). Most entries will end up with Title through LC Class, with
Series Title and Series Volume and Edition sometimes included, as
indicated in the examples. Our goal is to get it as efficiently as
possible from the downloaded format (email) back to an email with only
the fields we want. Thanks.

Gene

Shipped Item YBP Order Key: 40015563581
Title: UNDERCURRENTS: QUEER CULTURE AND POSTCOLONIAL HONG KONG. ISBN:
9780774814690
Author: LEUNG, HELEN HOK-SZE, 1967-
Publisher: UNIV OF BRIT COLUMBIA PR Pub Year: 2008 Binding: Cloth
LC Class: NX180.H6L48 2008 Content Level: ADV-AC YBP Select: Research-
Recommended
Series Title: SEXUALITY STUDIES SERIES.
US List: 98.00 USD
UK List: Not Known UK Status: Import Only
Est. US Net: 82.32 USD
Order Date: 7/2/2008
Invoice Date: 6/26/2008 Invoice Number: 452399
Library Note: Add...
shipped to library (6/26/2008) GobiTween (1 Book/1 Slip)



Subaccount: 2002-15 Quantity: 1 Fund Code: PO Number:
Batch PO: AP-PROFILED BKS
Initials: Other Local ID: Location:
Local Data 1: Local Data 2:
Local Data 3: Local Data 4:
Order Notes 1:



--------------------------------------------------------------------------------


Shipped Item YBP Order Key: 40015563582
Title: RHIZOMATIC WEST: REPRESENTING THE AMERICAN WEST IN A
TRANSNATIONAL, GLOBAL, MEDIA AGE. ISBN: 9780803215399
Author: CAMPBELL, NEIL, 1957-
Publisher: UNIV OF NEBRASKA PRESS Pub Year: 2008 Binding: Cloth
LC Class: NX653.W47C36 2008 Content Level: ADV-AC YBP Select: Research-
Recommended
Series Title: POSTWESTERN HORIZONS.
US List: 50.00 USD
UK List: Not Known UK Status: Import Only
Est. US Net: 42.00 USD
Order Date: 7/2/2008
Invoice Date: 6/26/2008 Invoice Number: 452399
Library Note: Add...
shipped to library (6/26/2008) alternate editions GobiTween (1 Book/1
Slip)



Subaccount: 2002-15 Quantity: 1 Fund Code: PO Number:
Batch PO: AP-PROFILED BKS
Initials: Other Local ID: Location:
Local Data 1: Local Data 2:
Local Data 3: Local Data 4:
Order Notes 1:



--------------------------------------------------------------------------------
Shipped Item YBP Order Key: 99820587895
Title: LANDMARK DECISIONS OF THE UNITED STATES SUPREME COURT. ISBN:
9780872894099
Author: FINKELMAN, PAUL, 1949-
Publisher: CQ PRESS Pub Year: 2008 Binding: Cloth
LC Class: KF4549.F56 2008 Content Level: GEN-AC YBP Select: Basic-
Recommended Edition: 2ND ED.
US List: 245.00 USD US Status: Orders accepted
UK List: Not Known UK Status: Import Only
Est. US Net: 205.80 USD
Order Date: 6/2/2008
Invoice Date: 7/2/2008 Invoice Number: 454875
Library Note: Add...
shipped to library (7/2/2008) GobiTween (1 Book)
Booklist



Subaccount: 2002-09 Quantity: 1 Fund Code: 762 PO Number: M0817892
Batch PO: AP-CLAIMS/SLIPS
Initials: JLK Other Local ID: Location: No Substitutions: No
Local Data 1: Local Data 2:
Local Data 3: Local Data 4:
Order Notes 1:

----------------------------

Shipped Item YBP Order Key: 99820720358
Title: HOUSE NOT MEANT TO STAND: A GOTHIC COMEDY ISBN: 9780811217095
Author: WILLIAMS, TENNESSEE, 1911-1983
Editor: THOMAS KEITH
Publisher: NEW DIRECTIONS Pub Year: 2008 Binding: Paper
LC Class: PS3545.I5365H68 2008 Content Level: GEN-AC YBP Select: Basic-
Essential
Series Title: NEW DIRECTIONS PAPERBOOK. Series Volume: 1105
US List: 14.95 USD
UK List: Not Known UK Status: Import Only
Est. US Net: 14.20 USD
Order Date: 6/18/2008
Invoice Date: 7/2/2008 Invoice Number: 454876
Library Note: Add...
shipped to library (7/2/2008) GobiTween (2 Books/2 Slips)
Booklist



Subaccount: 2002-09 Quantity: 1 Fund Code: 789 PO Number: M0818127
Batch PO: AP-CLAIMS/SLIPS
Initials: cc Other Local ID: Location: No Substitutions: No
Local Data 1: Local Data 2:
Local Data 3: Local Data 4:
Order Notes 1:



--------------------------------------------------------------------------------