2017-10-15

From SharePoint to MySQL - 4

In the last post on mail automation I described how I imported  mail recipients from an Excel sheet hosted in SharePoint. With a PowerShell script I download the Excel sheet and converted it to a CSV file, FTP it to a Linux environment and imported it to a MySQL database by stating a job in the Data Warehouse. Now it’s time to scrutinize the data Warehouse job.
The first part picks the CSV file from the FTP server and inserts the file into a table:

One job for convert the csv from iso-8859-1 to UTF-8 and one job to import it into MysQL. Now i have all data into one table, but I need some additional information, each recipient works at a location, each location is located in a timezone and all recipients have a preferred language. I create these three tables by select distinct from the first loaded table and add some columns for the extra info I needed for my mail system:
.

That’s it now I have a database, with four tables, Users which is my master table with all recipients and three support tables, Location, Language and TimeZone. Now I’m ready to tackle the mail templates, that will be the topic for the next post.

From SharePoint to MySQL - 3

Moving information from MS SharePoint to MySQL the hard way.

In the previous post on mail automation , I wrote I had the recipients in an Excel sheet, that was not was I was actually told. I was told the recipients was stored in SharePoint. But that was cool,  
moving data from SharePoint to MySQL should be easy, I done it before with a PowerShell script. You grab the list data save in a text file and FTP it over to the Linux box. Except this time the data was not stored in a SP list it was stored in an Excel sheet. It was supermany (+5000) rows so an SP list was useless. No harm in that, Excel is a fine table presenter de facto standard in the corporate world and it is easy to download the Excel sheet with a PowerShell script. Now the problem started. There is always problems accessing SharePoint programmatically. I could see my Excel sheet in SharePoint, but I could not for my life find it from PowerShell. One good thing with SharePoint all addresses are URLs, point to a resource and you got it, but this time I just got ‘not authorized, or a stupid HTML page. After many futile attempts I asked a colleague for help, he didn’t know either but suggested I should remove the last part of the URL. It did not work, but I started to look at the URL it looked quite odd:
 
What the f-k is the WopiFrame crap in there? After some googling that didn’t give much I decided to remove what I thought was crap in the URL and come up with this URL:


To my great astonishment that worked and I was able in no time to download the Excel sheet.
Next problem there are commas in the Excel Sheet data, but that is not a problem for me my swedish locale specify semicolon as a delimiter for Excel. But PowerShell scripts do not respect locale, documentation says it does but there is no explanation how to tell PowerShell to respect the Locale. I have a bad feel, the Excel guys are not my friends. Any rookie programmer knows it is easy peasy to add a delimiter parameter to a CSV conversion program, but the Excel guys have not implemented that simple and very useful feature, instead they claim to respect the locale by ‘local=true’ but give no hint how to specify the ‘local’ parameter. For the moment I’m stuck with the comma delimiter. In the previous post I learnt do not try to convert encoding in Microsoft environment, take whatever MS software give you transfer it to Linux and convert there. With all this in mind it was easy to write a PowerShell script that downloads the Excel sheet, converts it to a CSV file. transport it to Linux, and run a job in Linux converting the file to UTF-8 encoding and import the file into MySQL here it is:
#Before we start, where are we?
$cwd = Get-Location
write-host "$MyInvocation.ScriptName runs in $cwd"

$tofile   = "c:\excelfile3.xlsx"
#Credentials

$Username    = myuser'
$Password    = ConvertTo-SecureString 'mypassword' -AsPlainText -Force
$FTPuser     = "userid"
$FTPpw       = "password"


#Load the webinterface and fix credentials
$wc = New-Object system.Net.WebClient
$credentials = New-Object System.Management.Automation.PSCredential $Username, $Password

$webclient = New-Object System.Net.WebClient
$webclient.UseDefaultCredentials = $true
$webclient.DownloadFile($fromfile, $tofile)
write-host "Excel file downloaded from SharePoint"

$Excel = New-Object -ComObject Excel.Application
$Excel.Visible = $false
$Excel.DisplayAlerts = $false

$ExcelFiles | ForEach-Object {
       $workbook = $Excel.Workbooks.Open($tofile).Sheets.Item('Employees')
       $csvFilePath = $tofile -replace "\.xlsx$", ".csv"
       $workbook.SaveAs($csvFilePath, [Microsoft.Office.Interop.Excel.XlFileFormat]::xlCSV)
   }

$Excel.Quit()
[System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null
Remove-Variable Excel

write-host "$csvFilePath written"

$webclient = New-Object System.Net.WebClient
Register-ObjectEvent -InputObject $webclient -EventName "UploadProgressChanged" -Action { Write-Progress -Activity "Upload progress..." -Status "Uploading" -PercentComplete $EventArgs.ProgressPercentage } > $null

$FTPserver   = "servername"
$FTPmap      = "/"
$FTPfileName = "filename.csv"

$FTPfile = $FTPserver + $FTPmap + $FTPfileName
write-host "FTP file:$FTPfile"

$File = $csvFilePath
Write-Host "upload $File"
write-host "ftp://${FTPuser}:${FTPpw}@$FTPserver/$FTPfileName"

$client = New-Object System.Net.WebClient
$client.Credentials = New-Object System.Net.NetworkCredential("$FTPuser", "$FTPpw")
$uri = New-Object System.Uri("ftp://$FTPserver/$FTPfileName")
$client.UploadFile($uri, $File)

write-host "NGAD users transferred to the Data Warehouse"

# start a Data Warehouse job and check for it to end
$wr = Invoke-WebRequest http:/Linuxserver:8001/dw/tooljn/NGAD_import1.xml -method get
Write-Host "Importing NGAD users into the Data Warehouse job PID="$wr.Content "Status=" $wr.StatusDescription '('  $wr.StatusCode  ')'
$dwpid = $wr.Content

do {
   Start-Sleep -s 1
   $wrp = Invoke-WebRequest http://Linuxserver:8001/pid/$dwpid -method get
} while ( $wrp.Content -eq 0)
Write-Host "Done, the NGAD user list is imported into the Data Warehouse"
 

.In the next post I write about the Linux Job loading the Data Warehouse database.

Email automation - 2

This is the third post about mail automation. (You find the second here.)


“Hey Lars can you help us?” I was approached by a  colleague asking for help with a scheme for distributing mails to users before (and after) they were migrated to a new AD.
“We need an Excel sheet sorted by date where we can see when we should send the 2 and 1 weeks in advance warnings, the day before migration mail, the day after confirmation mail and the 2 weeks after evaluation mail.”
“Do you really need that, it’s sounds complicated just for keeping track of some mails.”
“It’s about 60.000 mails, and there will be a larger batch later on”
I started to realize this could be lot’s of work,  so I tried “Go and buy a professional mail package or ask a service provider.”
“We do not have time, you know how it is, it takes time to create a business case and have that approved and then make the solution work.”
Yes, I know how it is, it takes time so I said “Let me see what I can do. What do you have?”
My colleague sent me an Excel sheet with all recipients and mail info and then there was the timezone of the users. “Are the users spread out all over the globe?”
“Yes”
“Does it matter if some mails arrives one day early or late depending on time zone” I asked.
“We do not really know how to handle that situation.”
“Let me see what I can do” I said again.

I had an idea in the back of my head to turn my Data Warehouse into a mail generator, the Data Warehouse already have most tools needed for mail automation. First thing, restructure the Excel sheet into a MySQL database.

Email automation - 1

This is the second post (the first post, the next post you find here) in my quest for mail automation I need to grab some data from an Excel sheet. This is my first attempt. Exporting data from Excel the wrong way.
For my email generator I need the recipients to be stored in a MySQL table, today the recipients are maintained in an Excel sheet.  First I need to export the recipients from Excel. Below you see the Location/City Pará in my Excel sheet.




Now I save this  as  a UTF-8 CSV file, following this simple procedure:  


End of simple Excel  UTF-8 export  procedure.


And what do I get?
Do you see the dark area above? That is an ‘át’ as in  ‘Parát’. Of course it should be an á as in Pará, (‘át’ vas my first attempt to paste it in this text).

This do not look good, and even worse no matter how I try to load this csv file into MySQL I fail. Whatever the problem is this is clearly not the solution. And since the final goal is mail automation, this manual erroneous process this is a dead end.

2017-10-14

Email automation - 0

My so called smartphone signals incoming email with a merry sound in a major tone. This weekend the company ERP system is down for service, that in itself is bad. In the 90ties I promised myself only creating “never down” IT systems. I had to cheat a bit neither the technology or the cost was on my side, so I learned when ‘my’ systems was not used and serviced them at those time slots, yes I burned the lamp between 02:00 to 05:00 Sunday nights a lot. When I created the Data Warehouse I kept my promise to myself, the Data Warehouse was up the twelve years I was in charge except three power outages, I think that still is damn good, certainly no other system in the company even come close to those twelve years..
So far this morning my telephone has signalled over 300 incoming mail warning alerts from the Data Warehouse of broken workflow schedules all depending on the company ERP system, you can read about the failed workflows here. (And warning alerts keep on coming.)
Email automation is something I started in the early 80ties using the Memo system of Volvo IT in the mainframe world. Memo was an early Email system not built for automation at all, but I with some ingenuity and assembler skills was able to send warnings from the company ERP system at the time, the ERP system I had built myself (yes I am immensely proud of that, not many can say they built an ERP system for a global enterprise, yes again I did it all myself, production, logistics & purchase).


Email automation is something I worked with the last two weeks and that is the reason for this post, hopefully the first in a serie about my new email generator.

2017-09-25

Perl6 CRO - 2

In my first post about Perl6 and CRO I  mentioned I had problems take care of POST and GET messages at the same time.  The problem was a misspelled semi-colon


get -> 'greet', :%zname {
  say %zname:
 content 'text/plain',  'bye';
}
I wrote a colon instead of the correct semi-colon, you see it above say %zname: the result was


Cannot resolve caller say(Hash: DateTime); none of these signatures match:
   (Mu $: *%_)
 in block  at myfstserver.p6 line 26
 in code  at /home/tooljn/.rakudobrew/moar-nom/install/share/perl6/site/sources/1DC86B9F9A31CA5BEE35765E177159BAEDB91AAA (Cro::HTTP::Router) line 79
It took me some four hours to figure this out. If the error message had been a more helpful


%zname: is uninitialized or %zname: does not exist ...
I had probably spotted the problem instantly.

Now  my CRO server works beautifully. CRO seems amazing so far, a hell lot of functionality stuffed in there. Now I only have to learn Perl6. The language is truly great and CRO looks even greater and it is only the first beta version, really looking forward to the next version.

Update:
I just learned the colon I tripped over is an alternative method invocation syntax, which makes my complaint invalid. I need to learn Perl6 before complaining, but thinking before talking is not one of my stong suits, (either😊)

2017-09-20

Object Orientation is not such a good idea

 I found this on youtube https://www.youtube.com/watch?v=QM1iUe6IofM
The speaker expresses much the same ideas and opinions I have about Object Oriented programming. Actually on programming  at large, the speaker offers an alternative the good old procedural style as a better alternative.
I have many times found myself in the same situation the speaker describs when you need to change OO programs.You end up with many classes and interfaces with small code bits scattered all over.