Results 1 to 11 of 11
  1. #1
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103

    Post Batch loading of attachments (jpeg files)

    Is there a way to batch load attachments (specifically jpeg files) instead of the manual one-by-one "add" process?



    I am not a programmer (I am looking for one to do this) and I want to find out if this is doable.

    At present we receive tens and tens of PDF attachments each day (attached to Outlook e-mails from mobile apps). These include text, numbers and jpeg graphics. My goal is to reprocess them in Access (which we use as our database) to verify/validate the data more easily (right now we scan visually each PDF and the room for error is enormous). Also, by "reprocessing" in Access, I can include basic account information which many times is missed in the original PDF (we enter this missed data manually, very laborious and error prone process).

    I tried importing (Excel spreadsheet link and Access Table creation) the hyperlinks to my jpeg files and realized that in Access I have to do this again - one-by-one! I need to have the file batch loaded, instead. Otherwise, it is just as bad as our current manual process.

    So, first, I need to strip out the jpeg files and create their own directory (I have a unique account number and date stamp), and then create an Attachment Table which I can link via the unique account number. Aha, and the number of jpeg files per each PDF can be anywhere from 1 to 8. So, these can be named according to their sequence in the original PDF and I can assign their location in my "reprocessed PDF", accordingly.

    So, can all of this be done? and if so, how?

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You use a lot of terms that could be taken more ways than one. Batch load from where to where? What is your definition of batch load?
    What is reprocessing by Access? You want Access to verify the content of a pdf? I'd be surprised if you can do this since even what looks a pdf text document could actually be an image of a document.
    If you're looking to have the file path loaded into a table from network folders, that's doable. To make a connection between that and something else in the database (e.g. customer) you'd need an identifier that could come from the path (such as the folder name or the last n characters of the file name for example). If you're looking for someone to write code that will grab the attachments from an email and dump them in the network via Access code, I suppose that's possible but not by me.

    When reading your post I thought the last paragraph was going to sum up what needs to happen, but I'm afraid I don't understand the situation and goal any more so. That's because you state what you need to do in a way that portrays you understand the task but ask if it's possible. When you put it that way, I have no idea of what you need from us.
    Sorry.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    Micron,

    I appreciate your comments about precision and clarity, I will try to summarizewhat is needed. And, what I believe to be the solution.

    The current process:
    1. PDF’s are received daily from the field viaapps as attachments to e-mail
    2. Each PDF is saved in a weekly folderdedicated to a specific Town (project), (say project called Westfield)
    3. Each PDF is converted to a Word documentand any information (and many sometimes a lot) of missing information ismanually edited using Access database information. (Note: each Access databaseis dedicated to a specific Town project (say in this case called Westfield)). Theedited Word documents are saved as Final PDFs.
    4. Each PDF has a field identifying theTown where the work was done (for example: Westfield)
    a. Each PDF has many images (jpeg files), from 1 to 8
    b. The sequence of appearance of the images (they provide an image of various stages of work done by field Techs), isimportant.

    5. Weekly, a CSV summary is received andedited to fill in the same missing information which was processed in Item 3.
    6. The edited Final PDFs and the edited CSV file(saved as an .xlsx Excel format) are submitted to the customer as finished “product”on a weekly basis.
    The problem:
    1. The tediousness of editing manually theindividual PDFs is simply overwhelming
    2. The data verification process isoverwhelming and simply cannot be sustained to guarantee an acceptable level ofaccuracy.
    The proposed solution:
    1. Edit the weekly CSV/Excel file using currentpractice (missed Account#s, for example).
    2. Create a new dedicated folder of all theimage attachments (Attachment Folder), and save the image attachments using thefollowing naming system: Town, Account# and the sequence of its occurrence ineach individual PDF (say, A1 through A8). For example: Westfield – 993458 - A2
    3. Append the Attachment Folder periodically(say daily) as new PDFs are received
    4. “Recreate” the Final PDFs in Access. Thisway, the missing information is processed only once using the weekly CSV summaryfile as the starting point.

    Process

    1. Create a new dedicated folder of all images(Attachment Folder), which would be appended with new images as time progresses(as stated in Item of 2 of the proposed solution)
    2. Using the Edited (final) version of theweekly CSV/Excel file, create a directoryof: Town, Account#.
    3. From the current PDF folder name allimages (as Attachments) as follows: Town, Account#, (position)A1, A2, A3, ...A8
    4. Save the newly named images in theAttachment Folder

    Process 2

    1. From within an Access Database (eachdatabase is dedicated to a particular Town, say in this case - Westfield)create an Attachment Table
    2. Create the fields Town and Account#
    3. Create the Fields A1 through A8
    4. Populate the Attachment Table with all ofthe Account#s created in Process 1, Step 2
    5. Populate the Fields A1 through A8 with thenamed images which correspond to Town and Account# in each field correspondingto the Attachment name suffix (A1 through A8)

    Having the Attachment Table in place and populated as in Process 2, I can thencreate PDF forms with all the information which appeared in the original PDFs,or was intended to appear in the original PDFs (such as originally missingAccount#s), without the tediousness and inherent inaccuracy of doing it allmanually.

  4. #4
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    ?? Somewhat confused with your post??
    What information do you store and use? Seems a lot of PDFs.

    What is the rationale for
    Note: each Access databaseis dedicated to a specific Town project
    How and why exactly does missing/incomplete data come to you? This seems to be a basic issue.
    The tediousness of editing manually theindividual PDFs is simply overwhelming
    I don't work with the details of PDFs, but you seem to have a number of software products involved --Excel, Word, Access, PDFs.

    In 5 lines or less of plain English (no jargon) --what is the business involved? And what are you trying to accomplish?

  5. #5
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    Orange,

    We are in the business of replacing water meters for various Towns. The meters include transmitters which make it easy for Towns to simply read any water meter at will. (No more human water meters).

    To do this, each Town must have accurate references to: old meter final reading (for billing purposes) and the new meter serial number and new meter transmitter numbers (all barcodes read by the mobile devices carried by the Techs doing this work.

    Each Town also wants proof of the work in the form of PDFs (work orders) for each installation which also includes images of the pre- and post- installation (that's where the images come in play)

    If the work is schedule by our office, all basic account information (including the Account#, Name, Address etc) is returned with each work order/PDF as it is sent by the Tech using their mobile apps.

    However, many times, the Techs can schedule their own appointment as they are in the immediate area (the next door neighbor asks them to do the work while they are right there). in those instances, the basic information (Account #) is not available to the Tech as he uses an available template form. He fills it out as best he can. It includes the images and serial numbers but not the Account#. At best, it includes the street address which shows up on the PDF/completed Work Order. The staff in the office then need to manipulate (manually) the partially completed PDF to fill in the missing Account#, and other fields, as needed. This is all time consuming and prone to error.

    We also receive a summary CSV file which has all the same information (including the missing fields) and no images.

    My idea is to do the edits only once in the CSV (Excel file), associate the images with each work order record (via the edited/complete Account# fields), and then process the whole CSV/Excel spreadsheet as a link to the Access database (one exits for each Town/project).

    The processing would include another verification of the spreadsheet for any (still) missing fields and then re-formatting the original PDFs (QUERY/FORM/REPORT/PDF) with all the information using the database.

    Weekly, each Town would get the exported (verified) Excel Spreadsheet and all the PDFs that are associated with those records.

  6. #6
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Consider either reading the pdf file directly into Access (search on ms access parse pdf or ms access read pdf), then filling in the missing data in your db, OR read the Word document into Access after doing the edits in Word. From what I read, the only reason you're using Word is to complete the missing data, so maybe do that in Adobe (not Acrobat Reader) if you cannot get it directly from the pdf into Access. You don't seem to be using the document afterwards, so it appears to be a step that adds nothing but work.

    As for the csv file, it also makes no sense to me to edit or amend data only to have a different version come at you (the csv file) which does not reflect the changes and have to edit the csv file as well; a great opportunity to have two edits that should be identical but are not, never mind the fact that it is duplicating work.

    For business purposes, you should NEVER create any folder/file/object names with spaces or special characters, save for the underscore:
    Westfield_993458_A2 NOT Westfield – 993458 - A2 and NOT Westfield 993458 A2
    Even 99.99% of the files/folders I create at home for personal use follow this principle.
    Last edited by Micron; 10-16-2016 at 07:05 PM. Reason: clarification

  7. #7
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    Micron,

    Thank you for very good suggestions. Yes, I don't want to do things twice. So,working with the original pdfs in Access, and then completing the edits (aspart of a Query), without going through MS Word is a great suggestion. In fact,I want to bring everything into the db directly, if I can. So, I will try thatroute. Then, there would be no need for working with the CSV file and another uselessedit exercise.


    What I still need, is a way to create automatically the Attachment Folder andcreating (automatically) the Attachment Table in Access. Right now (I don't knowof any other way), the Attachment Table contents must be created one-by-one bybringing each Attachment separately into the corresponding field/record.

    The Account# (and also the date of the creation of the pdf) are the 2 singleunique identifiers that can be used to name each attachment (with the suffix A1through A8) to bring those attachments into the Attachment Table. That appears to be the biggest hurdle.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I agree with your attempts to streamline the processing and remove duplication in a practical manner. It seems there are too many 'softwares' in current set up.

    Is there any way to give the field agent the Account# or other info that they currently lack when servicing customers and neighbours? That seems to be a current irritant. Could the agent ask the neighbour for recent bill/invoice to get the Account#?
    You might consider using the file system rather than attachment type field, but the applicability is really based on the details of your info.

    Good luck.

  9. #9
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Using vba and the MkDir function, it is possible to create a file folder and sub folders. It is also possible to create tables on the fly with make table queries (simplest approach) or with the Create Table (sql) statement in vba. If the order of A1 through A8 is important, I cannot envision any reliable means to discern the order since you cannot even rely on the time stamp of an image, assuming the Exif data is even part of an image embedded in a pdf document. You would likely have to provide a sort order field in the table that holds the picture path and create a form that displays them and provides a sort order field (maybe a continuous form).

    Sounds like a rather difficult task that requires much more expertise than what a novice would typically possess. Since by your first post, second line, you don't see yourself as the one to do this, I recommend you document your current procedure and the field procedures in a way that clearly states the current practices and limitations/objectives (the first phase). If you're looking for an application that is Access based, try to find someone who is experienced with the requirements (e.g. if importing data from Word forms is a requirement, the ideal candidate should have this in their repertoire). There should be consult and investigative phases with the requirements and objectives well defined. Only then can a designer realistically propose solutions and a scope of work. Stick to the agreed upon design solution as much as possible lest you get into disagreements about scope creep and who's going to pay for them and delay the project. Only make significant deviations from the original scope if a major requirement was overlooked, which means the first phase was not adequately defined. Adding enhancements after users have had a chance to work with the result is more expedient and efficient.

  10. #10
    zkrucz is offline Competent Performer
    Windows 10 Access 2016
    Join Date
    Oct 2016
    Posts
    103
    Micron,

    Micron, all excellent suggestions. Thank you.

    I googled ms access parse pdf as you suggested and came up with inkscape as software to change the pdfs into Access-readable files. However, after playing with it, it turns out that incscape (unfortunately, it only reads one page of pdf at a time) and then does not save into .xml or .xds format (which Access allows).

    Can you suggest a similar software that creates either .xml or .xds format files?

  11. #11
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Sorry, that's not an area in which I have experience. Suggest you try the searches I already posted if you have not done so.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Batch Script to Move Files
    By littleheart_Sony in forum Programming
    Replies: 5
    Last Post: 06-20-2015, 12:06 AM
  2. Replies: 9
    Last Post: 01-26-2015, 01:58 PM
  3. View jpeg without IE
    By chuck555 in forum Programming
    Replies: 4
    Last Post: 11-08-2014, 11:18 PM
  4. Replies: 10
    Last Post: 06-16-2014, 08:37 AM
  5. Access Question regarding JPEG files
    By amosstarz in forum Security
    Replies: 1
    Last Post: 09-01-2006, 04:17 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums