How to get from downloaded entries to email more efficiently   

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:

date: Thu, 3 Jul 2008 13:06:31 -0700 (PDT)   author:   unknown

Re: How to get from downloaded entries to email more efficiently   

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

On Jul 3, 2:06 pm, geneg...@peoplepc.com wrote:
> 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:

date: Thu, 3 Jul 2008 13:33:53 -0700 (PDT)   author:   unknown

Re: How to get from downloaded entries to email more efficiently   

On Jul 3, 3:33 pm, geneg...@peoplepc.com wrote:
> 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
>
> On Jul 3, 2:06 pm, geneg...@peoplepc.com wrote:
>
> > 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:

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.

date: Fri, 4 Jul 2008 11:18:54 -0700 (PDT)   author:   unknown

Re: How to get from downloaded entries to email more efficiently   

On Jul 3, 3:06 pm, geneg...@peoplepc.com wrote:
> 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.

date: Mon, 7 Jul 2008 22:25:39 -0700 (PDT)   author:   unknown

Re: How to get from downloaded entries to email more efficiently   

On Jul 3, 3:06 pm, geneg...@peoplepc.com wrote:
> 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

date: Mon, 7 Jul 2008 22:33:22 -0700 (PDT)   author:   unknown