Help please? - parsing multiple data items from one field to others

[ccotti]

Hello..

First off, please know up front that I am NOT a programmer and know enough about VBA to be rather dangerous. So, I truly hope you may be able to help.

So, I have an Access database that I am using to combine business processes used with the client plans that use them. The catch is that, at this time, the relationship has to be made manually. So, a SharePoint form was created that would allow individuals to select the process and the multiple number of plans that use that process. Once the relationship has been made, SharePoint creates a list that can be exported to either Excel or Access. Either way, the information is put into a dump table in Access.

The table below shows what some of the fields look like when exported to Access. The semi colon “;” , the pound sign “#”, and the numbers between the “#” signs are added by SharePoint when exporting. And, we have not found a way to keep them from attaching. The colon (":") is being added by us as a delimiter between the plan number and name.

tblSharePointExport

Process
1001:Vesting
Plan Number and Name
100001rofit Sharing/401(k) Savings Plan ;#183;#100002:Compliance Team Plan ;#66;#100003etroleum Engineers Profit Sharing Plan ;#439;#100004rofit Sharing Plan for Tobacco Company;#355
(imagine this being two fields in a table)

​

Below is what I really need. A one to many relationship between the process used and the plan with each plan having their own record.

tblSharePointExport​

Num Process PNum Plan Name
1001 Vesting 100001 Profit Sharing/401(k) Savings Plan
1001 Vesting 100002 Compliance Team Plan

1001 Vesting 100003 Petroleum Engineers Profit Sharing Plan
1001 Vesting 100004 Profit Sharing Plan for Tobacco Company
​

(imagine this being four fields in a table)

After searching the internet and threads, I did find out how to parse the data based on a character (like a comma, semi colon, or even the # sign) Here is the link to the site that contained the code I used, http://www.techonthenet.com/access/questions/parse.php. After incorporating these function and method of parsing, I found out that it will only parse once. Also, it requires fields be created for each time a character type needs parsed. The only issue with that is that the number of plans that could be in the field vary greatly.

So, what I am hoping to learn is how to parse the data based on the various delimiters, then scrub the data of any unwanted info, and then combine it into the appropriate field with the corresponding record. (if all that makes sense). In addition, I am hoping to create this so that it runs as a function off a button on the switchboard form. Not asking for much, huh? ;-) Once all the data has been parsed and scrubbed, it will then be appended to a table that will be used for BI and reporting.

We have considered having the individuals select one process and one plan at a time.. however, there are several hundred processes as well as plans. So, the combinations could go into the thousands. In addition, management is asking us to find a way to allow individuals to select one process and all the plans that match and to be able to do that through the use of SharePoint.

So, any advice and/or coding options you could pass my way would be so appreciated.

Thanks
Chris

 

[Brianwarnock]

You can use the Split function to create an array of your parsed fields. Although help says the delimiter is a character it can be a string. You actually need 4 simple functions to give you the 4 fields like
firstfield
100001rofit Sharing/401(k) Savings Plan
then you can break these down

The split will create a 7 cell array (0-6) but 1 3 5 contain junk numbers you don't want 183 66 etc
so four functions

Public Function parse1(oldfield As String)
myarray = Split(oldfield, ";#")
parse1 = myarray(0)
End Function

Public Function parse2(oldfield As String)
myarray = Split(oldfield, ";#")
parse2 = myarray(2)
End Function

Public Function parse3(oldfield As String)
myarray = Split(oldfield, ";#")
parse3 = myarray(4)
End Function

Public Function parse4(oldfield As String)
myarray = Split(oldfield, ";#")
parse4 = myarray(6)
End Function

Now you run 4 queries to create the fields, using 1 function in each, which you are going to Union together to create the table. Each query will have the first to fields also this can be achieved with the simple parsing you have already got.

Hmm I feel that you may need to come back on this , and I may have ago at the whole thing over the weekend but gotta go now.

Brian

 

[ccotti]

Thanks Brian!

I will play around with this and see what I can come up with as well.

 

[Brianwarnock]

The talk of Union queries is a nonsense just 1 create table and 3 append queries, or create an empty table and 4 append queries..

Also it struck me that if the number parts of the data where fixed length as i suspect then the parsing is simple.

num:left(table1.process,4)
process:mid(table1.process,6)
pnum:left(parse1(Plan_Number_and_Name),6)
pname:mid(parse1(Plan_Number_and_Name),8)

and so on.

Of course if good at VBA you could do all of the reading and writing in the code and thus the Split would be done only once per input record.
4 years into retirement it would take me some head scratching and time.
but it might be an exercise that you could consider.

Brian

 

[ccotti]

Thanks again Brian.. these are all good suggestions and I will keep working at it. At the moment, I'm thinking about attempting the code option. I'm thinking that a count/loop function on that one field would be good since the amount of plans to one process will vary so much. Now I just have to figure out how to write it

Anyway, thanks again for all of your time and support! I really appreciate it and have fun while on retirement!

Chris

 

[Brianwarnock]

the amount of plans to one process will vary so much

For some reason I thought that it was fixed at 4, you will have to do more wprk on the Split function making use of Ubound i think, anyway it is in help so I'm sure that you will get there and you can always post back with specifcs , probably in a new thread depending on what.

Brian

 

[Poppa Smurf]

Attached is a database that will perform your requirements.

There are two tables tbl_input is your sample data, tbl_output is the result of the parsing.

I have assumed the following
1. There are two fields in the input
2. The first field is in the format Process number:Name e.g. 10001:Vesting
3. The second field is as per your example.
4. The second field is a text field. Will the field be a memo data type?

The Code is in the module mod_parse_input. The limit of the array are small just for testing. For testing purposes I have changed some of your data.

 

[Brianwarnock]

As I said in an earlier post I would need to be a lot of head scratching to remember how to do the whole exercise in code, however thanks to Allan I have been able to cheat.

I have done the exercise using the Split function, I did this mainly for my own amusement and was shocked to discover that my output differed from Allan's , but I discovered that Allan's misses the last plan fron the process 1001 ie plan 120004. I added a 3rd input record to aid debug and Allan's went completely haywire, I haven't had a chance to have a look yet I'm sure that it will be something simple.
I attach my version of the database.

Brian

Edit I found that the rowcount = rowcount+1 before the .Movenext was commented out, maybe I had somehow inadvertently done that. Now it still omits Plan 120004 and all of the 3rd input record.
The missing plan 12004 was due to there being no ;#123 at the end of the data. But don't know why no 3rd record.
My solution does not need the final ;# but will allow it.

 

[ccotti]

First off.. Brian and Alan.. you both ROCK!

This is great and you saved me a ton of time and headaches! Like you Brian, I have been scratching my head trying to figure this out. Only difference is you know what you are doing and I don't.

So.. I've tried the module and it is working. Now, I have one more question if I may be so bold.

How do I add an additional column of data? I thought that if I could see how to parse out the process name and number, I could duplicate that to do the alternate process column as well since they are basically the same (number:name). However, I am getting errors when compiling. So, I think it goes without saying that my logic is flawed.

Any suggestions? I attached the code with the changes I made if you wanted to see what I was thinking..

Again.. thank you SO very much for taking the time and having the compassion to help me out.

Chris

 

[Poppa Smurf]

Brian

I was not aware that the Split could be used as per your code.

Is this correct for the Split function? It splits the string into individual array cells of a single dimenional array based on the delimiter. When you split the plan details you step to every second arraycell because you have data that is not required in the next cell e.g. 185 that was added by sharepoint.

The missing plan 12004 was due to there being no ;#123 at the end of the data. But don't know why no 3rd record.

I ran your code and the third record of the input gave me the two records in the output for the third record.

Chris

How do I add an additional column of data?

You may need another array.

Do you have a copy with three columns and plenty of records?

Edit

How do I add an additional column of data? I thought that if I could see how to parse out the process name and number, I could duplicate that to do the alternate process column as well since they are basically the same (number:name). However, I am getting errors when compiling. So, I think it goes without saying that my logic is flawed.

Any suggestions? I attached the code with the changes I made if you wanted to see what I was thinking..

Upload your database and I will have a look at it.

 

[Poppa Smurf]

Chris and Brian

Here is an updated version using Brian's Split coding. I added a third field to the input data and processed in a simialr manner to the second field.

Briefly, this is the process
1. Read the input table into an array called array_input
2. Manipulated the array_input using the Split function
3. Wrote each result of the split function to an array called array_output
4. Finally, after the manipulation wrote the array_output to the table tbl_output.

I used the above method of using the arrays because in an earlier project at work where I was doing the transposing of each input record and writing the result to a table the database was bloating and time consuming. Using the arrays reduced the bloat and time taken.

 

[vbaInet]

Hey Allan, just getting some clarification here. What did you mean by point 3? Reading from the already split array into another multi-dimensional array would be costly.

I think the process could be:

1. Read first line and Split()
2. Check the Ubound() of the split array and IF it's 2, create a new record (i.e. I'm talking recordset here so the session remains "live") and add the two items. Save the Vesting ID into a variable.
3. Move to next line, Split(using ";"), Split(using ":") the first item and add the 2 items to the table, skip next item of first Split(i.e. the one using ";")
4. Continue adding using the saved Vesting ID in step 2 until all the split items (taking as a couple) have been used up. You would obviously have gotten rid of the hash (#) character in this step.

Just my idea folks.

It would be useful if the OP could provide a sample xls file for you guys to work with.

If it's not an xls doc and you're reading from a table, then the process would be much easier.

 

[Poppa Smurf]

I did this process as I am worried about the database may bloat by the reading each input record and writing many records after manipulating the input record.

I copied the Split code from Brian's example in an earlier message on this thread as I have not used the Split function before.

 

[vbaInet]

It wouldn't really bloat because you're using local functions to manipulate the data and the real work is copying from one table to the other. But I'm not clear if the records are in a table or in a Spreadsheet?

Ah I see. It's very useful right?

 

[Brianwarnock]

vbaInet The input is a table consisting of 2 fields like so

process_number plan_details
1001:Vesting 100001rofit Sharing/401(k) Savings Plan ;#183;#100002:Compliance Team Plan ;#66;#100003etroleum Engineers Profit Sharing Plan ;#439;#100004rofit Sharing Plan for Tobacco Company;#355;#120004rofit Sharing Plan for Tobacco Company;#123

well I guess its 3 now.

Forgive my ignorance but why would my approach of read input
write output
loop to next input

cause table bloat, whatever that means.
Please note this is a serious question.

Brian

 

[vbaInet]

I see Brian, I thought it was an Excel file. You've got it under wraps.

 

[ccotti]

Hi Brain and Alan,

I am working on getting a sample DB with some relatively realistic data for you. And, I apologize for not being able to give you a sample of the real data but, it's a confidentiality thing.

In the meantime and to clarify, I am getting the data handed to me in an Excel spreadsheet (which has been exported from SharePoint). And, I am importing that data to a table in Access which is where I am hoping to do the parsing.

Anyway, I will get busy and put together and upload a sample DB. Would 25 to 30 records be enough?

Also, I apologize for any confusion. I was trying to do this and be as little of a problem as possible.. and, truth be known, this is my first attempt at using a forum to get this kind of a thing figured out. While I have done some coding.. it has not been to this extent and more with using web languages to do some rather simple things (html, css, javascript, actionscript, etc).

So, again, please allow me to say thank you both so much for your continued support and help!

Thanks,
Chris

 

[vbaInet]

If the file is getting to you in spreadsheet format I don't see any reason for importing it into your db then copying from that table. The whole process can be handled from the xls file. I think you should provide Brian and Allan the spreadsheet.

 

[Brianwarnock]

Without your Db and with no comment on what and where the error the only thing I can point at is this

Dim intaltsub_number As String

should be

Dim lmgaltsub_number As Long

but in principle your approach to adding the extra field is correct.

Brian

 

[Brianwarnock]

If the file is getting to you in spreadsheet format I don't see any reason for importing it into your db then copying from that table. The whole process can be handled from the xls file. I think you should provide Brian and Allan the spreadsheet.

Erm, thanks!, I love Excel but this is one exercise I'm happy doing in Access.

Sure you can use text to columns and then clean up but it requires manual effort and no coding, each to his own I suppose.

Brian