Register a SA Forums Account here!
JOINING THE SA FORUMS WILL REMOVE THIS BIG AD, THE ANNOYING UNDERLINED ADS, AND STUPID INTERSTITIAL ADS!!!

You can: log in, read the tech support FAQ, or request your lost password. This dumb message (and those ads) will appear on every screen until you register! Get rid of this crap by registering your own SA Forums Account and joining roughly 150,000 Goons, for the one-time price of $9.95! We charge money because it costs us money per month for bills, and since we don't believe in showing ads to our users, we try to make the money back through forum registrations.
The Something Awful Forums > Discussion > Serious Hardware/Software Crap > Request a tiny app again
 
  • Post
  • Reply
tactlessbastard
Feb 4, 2001

Godspeed, post
Fun Shoe

mobby_6kl posted:

No, I think what this means is that we solved all of eveyone's little problem, so there's nothing left to do now.

OK, how's this:

I've got a folder on my win10 desktop that is the output dump of a process that as of today has 163 files (and growing, at an alarmingly increasing rate) , all named Mass Balance X.xls, where X corresponds to a work order # for a work order in NetSuite. Somebody has to open the respective work order in NetSuite, attach the corresponding .xls to the work order, and then move the .xls to a subfolder "Already Uploaded" in the original folder. This process needs to repeat until the folder is empty. If anyone can save me from this drudgery I'll ship them a case of our product (we bottle unicorn farts)*





*it isn't unicorn farts

* # ¿ Mar 8, 2019 22:34
  • Quote
Adbot
ADBOT LOVES YOU

# ¿ May 17, 2024 15:34
  • Quote
tactlessbastard
Feb 4, 2001

Godspeed, post
Fun Shoe
I'll look into AutoIt but I think there may be a slight snag. I'll demonstrate here:

https://imgur.com/nvDELBO

Here's my little nemesis, folder full of stuff that needs uploaded.

https://imgur.com/Ix295mq

Home screen for NetSuite

https://imgur.com/8BQa8Z

You access work orders by either searching through the list for the one you want (it's never going to be on the first page of results) OR typing the work order # into the search bar and clicking the corresponding work order. There will always be more than one result.

https://imgur.com/cnXMey7

Here's a work order, there's a add button there you hover over

https://imgur.com/KG17ck0

and then you can open up the file upload dialogue

https://imgur.com/a3I2LZg

walla

tactlessbastard fucked around with this message at 18:37 on Mar 11, 2019

* # ¿ Mar 11, 2019 18:31
  • Quote
tactlessbastard
Feb 4, 2001

Godspeed, post
Fun Shoe

mobby_6kl posted:

If it's a normal Windows Open File dialog, you can also just paste a full path to the file into the Name field. But getting more than one result in the search sucks. Is there really no way to filter it down to only the correct document? Everything else is quite doable otherwise.

No, If there's another way to pull up work orders in NetSuite, I don't know it. You can't even predict what the address of a given work order is going to be because they all get unique accounting IDs as they are created, same as every other object created in NetSuite from sales orders to item fulfillment. I'm going to go talk to the accounting VP that administrates NetSuite and see if he has any ideas.

* # ¿ Mar 12, 2019 16:15
  • Quote
tactlessbastard
Feb 4, 2001

Godspeed, post
Fun Shoe

Scaramouche posted:

Yeah I used to do Netsuite dev it's kind of crappy and insular. I don't know if what you want is possible without breaking their security model entirely. You could probably hack the file dialog button but you would lose the ability to relate the uploaded file to the specific order if I'm parsing what you want to do right. The alternatives as I see them would be to dev out a specific SuiteTalk app that has a better workflow than the existing ones in your screenshot, or maybe see if you can do it up with an API call (have no idea if the Work Order files are API accessible)

Yeah, the VP that administrates NetSuite reacted with growing horror as I described what I was trying to do so I'm probably going to let this one lie. I appreciate y'all's input and if you want any canned unicorn farts, send me a PM.

* # ¿ Mar 14, 2019 15:27
  • Quote
tactlessbastard
Feb 4, 2001

Godspeed, post
Fun Shoe
I need a hopefully very simple email parser. Due to corporate policy reasons, I'm not going to be able to use online services to do it.

What I have is an outlook inbox with 17k emails and rising. They are all automatically generated timer warnings. They all look like the attached imaged.

I need to troll through those emails, and extract only the time, machine, and line and output it as a csv or xls for all 17k events whereupon I can do my magic and demonstrate beyond a shadow of a doubt to my overlords that this goddamned monitoring system is a waste of loving time and electrons.

I would be willing to offer gift certs or whatever reasonable recompense.

Only registered members can see post attachments!

* # ¿ Dec 17, 2020 11:34
  • Quote
tactlessbastard
Feb 4, 2001

Godspeed, post
Fun Shoe

Toshimo posted:

This is probably complicated by the fact that you're going to try and parse html that wasn't meant for it, and if someone wants to tackle the whole thing head-on, more power to them.

If by "time", you mean whatever's listed under "Last Check", whoever is doing this will need access to the raw (sanitized) text of one of the e-mails because they'll need to figure out exactly what the HTML rendering that table is.

Otherwise, you can just use a short powershell script like the one below to dump it all out somewhat sanely.

Warnings:
  • This script has zero error checking and isn't suitable for Enterprise deployment as-is. It won't break anything, but don't try to amaze your friends and coworkers with it without trying it first.
  • It will try and write a log file to your C:\tmp directory. If you don't have one or can't write to it, change the location in the script or you won't get any output.
  • It grabs the "Sent On" timestamp from the e-mail because there's no reasonable way to parse the "Last Check" time from a screenshot.
  • Outlook/Exchange are known to artificially limit the number of items returned by a query, especially in situations where your organization may be archiving emails. 17k may be too much for one pass, so if you find that the number of emails you have doesn't match the number of rows you are getting dumped to CSV, you might need to do dumb things like portion out 1k emails at a time into a folder and run queries against just that folder.

That all said, here's a sample script:
code:
$mailbox = 'yourname@company.com'
$searchphrase = '^ALERT - Timer'
$outfile = "C:\tmp\timer_alerts_$(get-date -UFormat “%Y_%m_%d_%H_%M_%S”).csv"

$outlook = New-Object -comobject Outlook.Application
$namespace = $outlook.GetNameSpace("MAPI")
$store = $namespace.Stores[$mailbox]
$inbox = $store.GetDefaultFolder([Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
$root = $store.GetRootFolder()

$data = @()

Foreach ($email in $inbox.Items) {
    if($email.Subject -match $searchphrase) {
        $payload = @{ Time = ""; Machine = ""; Line = ""}
        if($body -match "Machine Name: (.*)") {
            $payload.Machine = $matches.1
        }
        if($body -match "Line Number: (.*)") {
            $payload.Line = $matches.1
        }
        $payload.Time = $email.SentOn | Get-Date -UFormat "%m/%d/%Y %R"

        $data += $payload
    }
}

$data | % {New-Object psobject -Property $_ } | Export-CSV $outfile -NoTypeInformation

By time I did mean the time the email was sent. Thanks, I'll test this tonight and report back.

* # ¿ Dec 17, 2020 23:45
  • Quote
tactlessbastard
Feb 4, 2001

Godspeed, post
Fun Shoe
Got it to run, and it is pulling time and placing that in the csv but it is not pulling machine or line. It is creating those columns but not making entries.

* # ¿ Dec 18, 2020 00:04
  • Quote
tactlessbastard
Feb 4, 2001

Godspeed, post
Fun Shoe

Toshimo posted:

That means it's probably using HTML #nbsp; for spaces or something. I'd need to see a plaintext dump of the message body to correct the regexes.


code:

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=us-ascii"></head>
<body><br>Defects found at:<br>Work Order: 285780<br>Machine Name: PACKER<br>Quality Check Type: PACKER - Case Check<br>Line Number: 08<br><br><table border="1"><tr><td><b>Check Type</b></td><td><b>Line Number</b></td><td><b>Work Order</b></td><td><b>Item SKU</b></td><td><b>Last Check</b></td><td><b>Check Interval</b></td><td><b>Time Elapsed</b></td><td><b>Last Tester Name</b></td></tr> <tr> &#160;<td>Packer - Case Check</td> &#160;<td>08</td> &#160;<td>285780</td> &#160;<td>71608010</td> &#160;<td>2020-12-17 18:00:15.0</td> &#160;<td>30 minutes</td> &#160;<td>00:45:01</td> &#160;<td>REDACTED</td></tr></table></body></html>

* # ¿ Dec 18, 2020 01:18
  • Quote
tactlessbastard
Feb 4, 2001

Godspeed, post
Fun Shoe

Toshimo posted:

Updated regexes:

code:
$mailbox = 'yourname@company.com'
$searchphrase = '^ALERT - Timer'
$outfile = "C:\tmp\timer_alerts_$(get-date -UFormat “%Y_%m_%d_%H_%M_%S”).csv"

$outlook = New-Object -comobject Outlook.Application
$namespace = $outlook.GetNameSpace("MAPI")
$store = $namespace.Stores[$mailbox]
$inbox = $store.GetDefaultFolder([Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
$root = $store.GetRootFolder()

$data = @()

Foreach ($email in $inbox.Items) {
    if($email.Subject -match $searchphrase) {
        $payload = @{ Time = ""; Machine = ""; Line = ""}
        if($body -match "Machine Name: ([^<]*)") {
            $payload.Machine = $matches.1
        }
        if($body -match "Line Number: ([^<]*)") {
            $payload.Line = $matches.1
        }
        $payload.Time = $email.SentOn | Get-Date -UFormat "%m/%d/%Y %R"

        $data += $payload
    }
}

$data | % {New-Object psobject -Property $_ } | Export-CSV $outfile -NoTypeInformation


Thank you so much! However, still getting incomplete pulls. Attaching a screenshot.

Only registered members can see post attachments!

* # ¿ Dec 18, 2020 09:25
  • Quote
tactlessbastard
Feb 4, 2001

Godspeed, post
Fun Shoe

Toshimo posted:

Ah yes, silly typo on my part going from my test machine.

code:
$mailbox = 'yourname@company.com'
$searchphrase = '^ALERT - Timer'
$outfile = "C:\tmp\timer_alerts_$(get-date -UFormat “%Y_%m_%d_%H_%M_%S”).csv"

$outlook = New-Object -comobject Outlook.Application
$namespace = $outlook.GetNameSpace("MAPI")
$store = $namespace.Stores[$mailbox]
$inbox = $store.GetDefaultFolder([Microsoft.Office.Interop.Outlook.OlDefaultFolders]::olFolderInbox)
$root = $store.GetRootFolder()

$data = @()

Foreach ($email in $inbox.Items) {
    if($email.Subject -match $searchphrase) {
        $payload = @{ Time = ""; Machine = ""; Line = ""}
        if($email.Body -match "Machine Name: ([^<]*)") {
            $payload.Machine = $matches.1
        }
        if($email.Body -match "Line Number: ([^<]*)") {
            $payload.Line = $matches.1
        }
        $payload.Time = $email.SentOn | Get-Date -UFormat "%m/%d/%Y %R"

        $data += $payload
    }
}

$data | % {New-Object psobject -Property $_ } | Export-CSV $outfile -NoTypeInformation

Beautiful! Thanks! What's your pleasure?

* # ¿ Dec 19, 2020 02:29
  • Quote
tactlessbastard
Feb 4, 2001

Godspeed, post
Fun Shoe
This is possibly something just really silly but I'm having a further hangup. Now that I have the data all extracted, when I try to do anything at all with it in excel (sort, copy, highlight, move) all the values in the cells change from the value to the entire text of the email the value was pulled from.

Now that I have 17k x 3 points of data, generally speaking this crashes excel.

* # ¿ Dec 19, 2020 05:49
  • Quote
tactlessbastard
Feb 4, 2001

Godspeed, post
Fun Shoe

Pikehead posted:

What does the data look like if you open it in notepad or something like notepad++?

Additionally, how big is the csv file?

Ok, I'm back at work. The first .csv which I ran on 10 emails as I tested the limits of the script (10 emails, 50 emails, 100, 500, 1000, 2000, etc.) Never ran into a show stopping limit before running out of emails to parse.

Anyway, that .csv with 10 emails is 6kb.

If I open them with notepad it shows the whole contents of the email. (Names deleted)

code:
"Line","Time","Machine"
"08


Check Type	Line Number	Work Order	Item SKU	Last Check	Check Interval	Time Elapsed	Last Tester Name	
Packer - Check Sheet	 08	 288485	 71608001	 2020-12-18 17:40:00.0	 30 minutes	 00:45:03	 	","12/18/2020 17:25","PACKER
Quality Check Type: PACKER - Check Sheet
Line Number: 08


Check Type	Line Number	Work Order	Item SKU	Last Check	Check Interval	Time Elapsed	Last Tester Name	
Packer - Check Sheet	 08	 288485	 71608001	 2020-12-18 17:40:00.0	 30 minutes	 00:45:03		"
"12


Check Type	Line Number	Work Order	Item SKU	Last Check	Check Interval	Time Elapsed	Last Tester Name	
Packer - Check Sheet	 12	 288665	 20012176	 2020-12-18 17:41:14.0	 30 minutes	 00:45:03	 	","12/18/2020 17:26","PACKER
Quality Check Type: PACKER - Check Sheet
Line Number: 12


Check Type	Line Number	Work Order	Item SKU	Last Check	Check Interval	Time Elapsed	Last Tester Name	
Packer - Check Sheet	 12	 288665	 20012176	 2020-12-18 17:41:14.0	 30 minutes	 00:45:03	 	"
"12


Check Type	Line Number	Work Order	Item SKU	Last Check	Check Interval	Time Elapsed	Last Tester Name	
Packer - Case Check	 12	 288665	 20012176	 2020-12-18 17:41:16.0	 30 minutes	 00:45:00	 	","12/18/2020 17:26","PACKER
Quality Check Type: PACKER - Case Check
Line Number: 12


Check Type	Line Number	Work Order	Item SKU	Last Check	Check Interval	Time Elapsed	Last Tester Name	
Packer - Case Check	 12	 288665	 20012176	 2020-12-18 17:41:16.0	 30 minutes	 00:45:00	 	"
"03


Check Type	Line Number	Work Order	Item SKU	Last Check	Check Interval	Time Elapsed	Last Tester Name	
Labeler - Verify Check	 03	 278845	 21000030	 2020-12-18 18:21:03.0	 30 minutes	 00:45:01		","12/18/2020 18:06","LABELER
Quality Check Type: LABELER - Verify Check
Line Number: 03


Check Type	Line Number	Work Order	Item SKU	Last Check	Check Interval	Time Elapsed	Last Tester Name	
Labeler - Verify Check	 03	 278845	 21000030	 2020-12-18 18:21:03.0	 30 minutes	 00:45:01		"
"12


Check Type	Line Number	Work Order	Item SKU	Last Check	Check Interval	Time Elapsed	Last Tester Name	
Packer - Check Sheet	 12	 288665	 20012176	 2020-12-18 18:44:55.0	 30 minutes	 00:45:03		","12/18/2020 18:29","PACKER
Quality Check Type: PACKER - Check Sheet
Line Number: 12


Check Type	Line Number	Work Order	Item SKU	Last Check	Check Interval	Time Elapsed	Last Tester Name	
Packer - Check Sheet	 12	 288665	 20012176	 2020-12-18 18:44:55.0	 30 minutes	 00:45:03		"
"12


Check Type	Line Number	Work Order	Item SKU	Last Check	Check Interval	Time Elapsed	Last Tester Name	
Packer - Check Sheet	 12	 288665	 20012176	 2020-12-18 18:44:55.0	 30 minutes	 00:45:04	 	","12/18/2020 18:30","PACKER
Quality Check Type: PACKER - Check Sheet
Line Number: 12


Check Type	Line Number	Work Order	Item SKU	Last Check	Check Interval	Time Elapsed	Last Tester Name	
Packer - Check Sheet	 12	 288665	 20012176	 2020-12-18 18:44:55.0	 30 minutes	 00:45:04	 	"
"12


Check Type	Line Number	Work Order	Item SKU	Last Check	Check Interval	Time Elapsed	Last Tester Name	
Packer - Case Check	 12	 288665	 20012176	 2020-12-18 18:44:58.0	 30 minutes	 00:45:00	 	","12/18/2020 18:29","PACKER
Quality Check Type: PACKER - Case Check
Line Number: 12


Check Type	Line Number	Work Order	Item SKU	Last Check	Check Interval	Time Elapsed	Last Tester Name	
Packer - Case Check	 12	 288665	 20012176	 2020-12-18 18:44:58.0	 30 minutes	 00:45:00		"
"08


Check Type	Line Number	Work Order	Item SKU	Last Check	Check Interval	Time Elapsed	Last Tester Name	
Triblock - Red Zone Check	 08	 288485	 71608001	 2020-12-18 19:14:49.0	 30 minutes	 00:45:01	 	","12/18/2020 18:59","TRIBLOCK
Quality Check Type: TRIBLOCK - Red Zone Check
Line Number: 08


Check Type	Line Number	Work Order	Item SKU	Last Check	Check Interval	Time Elapsed	Last Tester Name	
Triblock - Red Zone Check	 08	 288485	 71608001	 2020-12-18 19:14:49.0	 30 minutes	 00:45:01	 	"
"08


Check Type	Line Number	Work Order	Item SKU	Last Check	Check Interval	Time Elapsed	Last Tester Name	
Labeler - Verify Check	 08	 288485	 71608001	 2020-12-18 19:16:02.0	 30 minutes	 00:45:00	 	","12/18/2020 19:01","LABELER
Quality Check Type: LABELER - Verify Check
Line Number: 08


Check Type	Line Number	Work Order	Item SKU	Last Check	Check Interval	Time Elapsed	Last Tester Name	
Labeler - Verify Check	 08	 288485	 71608001	 2020-12-18 19:16:02.0	 30 minutes	 00:45:00		"
"12


Check Type	Line Number	Work Order	Item SKU	Last Check	Check Interval	Time Elapsed	Last Tester Name	
Palletizer - Pallet / Depal Check	 12	 288665	 20012176	 2020-12-18 19:22:44.0	 30 minutes	 00:45:00	 	","12/18/2020 19:07","PALLETIZER
Quality Check Type: PALLETIZER - Pallet / Depal Check
Line Number: 12


Check Type	Line Number	Work Order	Item SKU	Last Check	Check Interval	Time Elapsed	Last Tester Name	
Palletizer - Pallet / Depal Check	 12	 288665	 20012176	 2020-12-18 19:22:44.0	 30 minutes	 00:45:00	 	"

* # ¿ Dec 22, 2020 00:01
  • Quote
tactlessbastard
Feb 4, 2001

Godspeed, post
Fun Shoe
I'll try those out when I get back to work in the new year, thanks!

* # ¿ Dec 23, 2020 18:44
  • Quote
Adbot
ADBOT LOVES YOU

# ¿ May 17, 2024 15:34
  • Quote
tactlessbastard
Feb 4, 2001

Godspeed, post
Fun Shoe
You beautiful bastards, it worked perfectly! Toshimo, pseudorandom, please name your desire!

* # ¿ Jan 7, 2021 01:59
  • Quote
  • 1
  • 2
  • 3
  • 4
  • 5
  • Post
  • Reply
The Something Awful Forums > Discussion > Serious Hardware/Software Crap > Request a tiny app again