Assign a single record to a string

[NauticalGent]

Good day AWF!

My lovely wife came to me with ugliest excel spreadsheet I have ever seen. Due to a number of factors, I cannot import it and even when I did manage to do so, the data is all over the place. For example, in one record, the info she was looking for is in Field(column) F7 and in the next record, it is in Field(column) F56 and it just gets worse...

I was able to write a sub that looped though each field and search for a particular string and it is able to process about 12k records in less than 2 minutes. However, I was wondering if there was a way to search the entire record for a string without having to concatenate the fields OR loop through them.

I am certain there isn't, but I don't mind being wrong...

Thanks in advance!

 

[Minty]

I don't think you can easily, however, if you chuck all the columns into an Array (which you can do in one go), then search the array, it will be light-years quicker.

 

[MajP]

Power queries in Excel make doing a Union like query really easy. That may simplify the problem

 

[NauticalGent]

Now WHY didnt I think of that?!? What is funny, I was already assigning the Field contents into an Array and evaluating each Array element. I amaze myself at times...for all the WRONG reasons!

 

[NauticalGent]

Power queries in Excel make doing a Union like query really easy. That may simplify the problem

Thanks MajP, since she is the excel expert in the house, I will present that to her...

 

[Pat Hartman]

Have you considered sending her to a reeducation camp

 

[NauticalGent]

Have you considered sending her to a reeducation camp

Actually, she does recognize the supremacy of Access to Excel when it comes to storing data. The issue is she come to ME instead of learning it herself.

She is pretty proficient at basic queries and gets real proud of herself when she "makes it happen". But again, when it comes to the neat stuff, she has me and I have AWF!

 

[The_Doc_Man]

This may be sacrilegious, but if she wanted to do something in Access, you COULD introduce her to the forum. We rarely bite and hardly ever try to run off with someone else's main squeeze.

 

[Edgar_]

I was wondering if there was a way to search the entire record for a string without having to concatenate the fields OR loop through them.

The built-in Find function is the fastest way to accomplish that, built-in stuff is usually already optimized, so it's best to use it most of the times. But if loops are required, then converting the cell values into an array is indeed the way to go because each cell range comes with a massive set of extra objects and going through all of them is painfully slow.

 

[NauticalGent]

This may be sacrilegious, but if she wanted to do something in Access, you COULD introduce her to the forum. We rarely bite and hardly ever try to run off with someone else's main squeeze.

Back 2017 I believe, she and I were working on a project for her job. I had to travel to Bahrain for about a month and would not have access to our project or even AWF. We made her an account and, true to her lack of artistic creativity, her name was NauticalLass (I think). That was before Jon changed the web service so I think she got lost in the sauce on the change-over since her only post was the introduction.

She is an extreme introvert and although she knew most of you by name (She has endured my ramblings about the members here and has even met 2 members), she was a little intimidated and doesn't not like to ask questions...so there ya have it. She dabbles a little because, as I said, she knows Access is a better tool for what she wants to do, but the learning curve is steep at first and she can always hand it off to me...

...AND...if she WERE to become self-sufficient, what would she need me for?!?!?

 

[CJ_London]

what would she need me for?

keep the coffee coming?

 

[The_Doc_Man]

...AND...if she WERE to become self-sufficient, what would she need me for?!?!?

I would hope she would stay because of love. That's what my main squeeze says. That and also that she has invested 29 years into breaking me in and doesn't want to waste the effort now.

 

[jdraw]

I can identify as a fellow dabbler and can confirm a long, steep and continuing learning curve and an increasing forgetful curve with age.

 

[NauticalGent]

Since this generated more interest than I thought it would, attached is the quick, down-and-dirty db did to get her the results and zip file that holds the hideous spreadsheet she gave me. It is an export she gets from her company's website on request so "it is what it is". I link to it and name the link excelData.

The spreadsheet is so ugly I needed a shower after playing with it - don't say I didn't warn you.

The info she needs are many, but in this case, she needed 3 dates which we were able to determine came over as something like: 2023-07-21 15:07:25;;;2023-07-24 20:04:32;2023-07-31 13:50:26

If you looked at the code I searched for "*####-##-## ##:##:##;;;####-##-## ##:##:##;####-##-## ##:##:##*" and if found did something with it. Again, this is something I threw together as a proof-of-concept for her and there is much work to do.

Have a look but wear a mask and gloves...any feedback is welcome.

 

[Edgar_]

The excel file has a structure, however, it was not configured properly. Fixing it is relatively easy:
1. Convert it to CSV
2. Open Excel
3. Import from text
4. Choose semicolon ( ; ) as separator
5. Choose none as text qualifier
Done

There are columns with what appears to be JSON data, so you can use the web browser control to parse that information.

The attached document should simplify the process of adapting it for Access and performing the necessary tasks to continue the development.

 

[NauticalGent]

Thanks Edgar, that really cleaned up a lot of records and got us closer to an accurate account of here data.

As you mentioned, it is configured poorly but I may be able to loop through the fields and extract data that matches a pattern.

here are columns with what appears to be JSON data, so you can use the web browser control to parse that information.

Exactly. I am no expert on JSON, hell you cant even call me a novice! Would you indulge me and explain how I would use a web browser control to parse the info?

 

[Edgar_]

Sure, I'll prepare some examples when I get to the office. What do you plan to do with all that data? Is that all the data?

 

[NauticalGent]

Sure, I'll prepare some examples when I get to the office. What do you plan to do with all that data? Is that all the data?

You sir, are a prince among men! The data is HR Cases for my wife's company. She wants to make reports based on metrics: Avg time to process cases, type of cases, etc. This is all the data, but it is a snapshot - new cases get generated all day, every day and existing ones get processed as well.

I have found a way to align the data - it is ugly but it IS dynamic and will work. The data comes form web forms that my darling wife made. What seems to happen is that if a worker puts a hard return in a field, it exports as a new record from the point of the hard return. Once I figured that out, it was easy to associated the case number with the misaligned data and then "move" it to the right record and to the correct fields.

The whole process involves a few procedures but it works. If there is a better way to parse the data, I'm all ears/eyes.

Really appreciate your time with this

 

[Edgar_]

For the subject of parsing the json column, take a look at the attached.

Let me know if you can view the info correctly or if it throws errors. I had to craft a cleanup function for the json strings, it's not perfect, but it can be with more thought. You can apply this same technique to other columns, like those dynamic attributes of the employee, or those lists of people.

The approach for automating the browser control is as follows:
1. Declare some global variables for the browser, the html document and other elements that will be shared by procedures

Private wb As WebBrowser, doc As HTMLDocument, container As HTMLDivElement, div As HTMLDivElement

These can be late bound as well, but for that to work, you'll have to enable IE11 emulation, which is done by adding a few registry keys
2. Initialize your browser control in the load event

Private Sub Form_Load()
    Set wb = Me.wbFIELDS.Object
    Me.wbFIELDS.ControlSource = "=""about:blank"""
    Set doc = wb.Document
End Sub

3. In the procedure where the automation begins, use the following before adding any more code

    Do While wb.ReadyState <> 4
        DoEvents
    Loop

You can also use the document complete event as a helper if you need it
4. Automate the document. You can use createElement and appendChild to create HTML stuff, with any element, you can apply styles by using the .Style property, if you want to add some javascript code, you can use the document's parent window like: doc.parentWindow.execScript

And there are a lot of other tricks.

 

[NauticalGent]

Edgar, this is well above and beyond what I was looking for! My wife thinks you're and genius and she is over the moon - which means I am over the moon...

Colin(Isladogs) actually has a JSON App that I will look into as well. He has spent a crazy amount of time on it and it was something I thought was "cute" but ignored it because I never thought I would need it. Never say never.

Appreciate your time with this and the "nudge" in the right direction. I knew the "FIELDS" field contained about 99% of what I needed and was writing all kinds of INSTR and RegEx functions to parse it. I had a clue that it might be JSON from watching an API video by Richard Rost(Amicron) where he showed some weird looking text and said it was JSON...funny how the universe gives you all the ingrediants to make soup before you even know you need to make it...

Awesome job you did and this is going to save me loads of time and make my wife happy: Happy wife, happy life!