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" $fromfile = "https://thehubabub.group.thecompany.com/sites/path/26431/My%20Documents/NGAD%20Detail%20plan%20user%20migration.xlsx" $tofile = "c:\temp\excelfile9.xlsx" #Load the webinterface and fix credentials #The credentials ares supposed to be a certificate but it is unclear to me how to use cerificates in Powerhell scripting at this time. For now I use userid & password $wc = New-Object system.Net.WebClient $credentials = New-Object System.Management.Automation.PSCredential $Username, $Password #$webclient = New-Object System.Net.WebClient $wc.UseDefaultCredentials = $true #$wc.Credentials = Get-Credential #$wc.Credentials = $credentials $wc.DownloadFile($fromfile, $tofile) write-host "Excel file downloaded from SharePoint" $Excel = New-Object -ComObject Excel.Application #$Workbook = $Excel.Workbooks.Open($tofile) #$Worksheet = $Workbook.Sheets.Item('Employees') $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]::xlUnicodeText) } $Excel.Quit() [System.Runtime.Interopservices.Marshal]::ReleaseComObject($Excel) | Out-Null Remove-Variable Excel If (Test-Path $tofile){ Remove-Item $tofile } 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 $FTPfile = $FTPserver + $FTPmap + $FTPfileName write-host "FTP file:$FTPfile" $File = $csvFilePath Write-Host "upload $csvFilePath" 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) If (Test-Path $csvFilePath){ Remove-Item $csvFilePath } write-host "NGAD users transferred to the Data Warehouse" $wr = Invoke-WebRequest http://http://nn.nn.nn.nn:nnnn/path/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://nn.nn.nn.nn:nnnn/path/$dwpid -method get # Write-Host $wrp.Content } 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.
No comments:
Post a Comment