Bulk upload with tagging

Question

We are trying to reform our accounting site, by taking all of the documents we use in dropbox, and pushing them to sharepoint. The difficulty is that I need to keep certain information about each file.

Certificates of Insurance are the documents in question. Our accounting employee has the files saved in a special format that makes sense to her. The naming convention she uses for the files is: VendorName COI exp mm.dd.yy (for example, a file could be named: CompanyX COI exp 9.1.16).

Now, I need to take out the Vendor name and use that as the title field in a document library, and the expiration should be placed in a separate column.

We have several hundred of these documents, so extracting the expiration date of each would take an unreasonable amount of time. So, my fellow programmers, what can I do to speed this process up?

Edit: Using Sharepoint Online

Edit2: Here is an example of the files I was given, I crossed out our vendor's names for privacy reasons.

Edit3: To make it more clear: the way the COIs work, is that we get one every year from each of our vendors. We have about 200 or so vendors right now, each with 2-3 COIs I have to upload (each COI having a different expiration, with a possibility of having 2 COIs with the same exp, but different type of COI)

Answer 1

If you have use of SharePoint Designer with your site, you could also set up a simple workflow to copy the appropriate parts of the file names into their target fields in the document library.

As Lucky Lindy pointed out, the whole process is much easier if the file names are standardized beforehand, but once you have standard file names, you could do something like the following:

1. Assign the file name to a string variable.
2. Find the index of "COI" in that variable.
3. Extract the characters up to that index as the company name.
4. Find the index of "exp" in the string.
5. Get the characters from that index to the first "." and assign it to a "month" variable.
6. Get the characters from the first "." to the second "." and assign it to a "day" variable.
7. Get the characters from the second "." to the end of the file name and assign it to a "year" variable
8. Assemble the month, day, and year variables into a date string, and then assign that string to your expiration date column as a date/time value.

Answer 2

What I typically do in this case would:

1. Standardize the filename so that they are all in the same format. I see at least 1 file with a - after the date and the dates are not in the same formats.
2. to create 2 different calculated columns. One to hold the Title and another to hold the expiration date (let me know if you need help with the formulas)
3. Create a date column to hold the expiration date.
4. upload the documents, but don't check them in.
5. Create a view with the 3 columns created above and the title field
6. edit in grid view and copy the calculated fields to the static fields.
7. Delete the calculated fields

I know this is kind of brute force, but it should take less than an hour. No programming (except formulas).

Two other suggestions:

Have the accountant standardize their file names for you. If you don't want to standardize the names before upload, you can fix them manually in the grid view.