Mr Deans Mr Deans - 3 months ago 24
PowerShell Question

Powershell CSV to SQL Query

I am back on my on-going learning into powershell, I have hit the limits of my capabilities. So wondering if anyone can throw me in the right direction and or tell me if what I am wanting to do is possible?

I have written a script which downloads a number of file(s) from the web and then deletes out all but the data that I wish to work with. These are .csv's...

I have added the Powershell code that I have managed to cobble together so far, when the data is downloaded see below. The data extract of one of these files looks like this.

Column A contains a description

Column B contains an ISIN (this is the working information)

Column C contains a numerical figure

Column D is where I want the SQL query data to be returned to

Please find a link to the sample data
Data Example
Data Example

CSV Extract

What I then want to achieve is to run the following SQL command based on the data within Column B

Select *
From CL
Where CLISIN in ('GB0004835483',
'BE0003793107',
'GB00B7V2GY97',
'GB0000595859',
'GB00B1VCNQ84',
'GB0004992003',
'GB0002369352')


I believe that I will need to export this as another file perhaps? Though using the final results that are exported I then need to place these within Column D in this csv file.

Hoping that I have made this clear, if not please let me know and I will be as expansive as possible.

Long and the short is can I use powershell to automatically run a query for each item in column B and add the results of that matching query to the correlating line in column D?

I cannot find the answer via google nor here...

#### DOWNLOAD LOCATIONS ####
$DownloadPTMLocation = "L:\Operations Database\TakeOverPanel\PTMDisclosureTable.xls"
$DownloadPTMCSVLocation = "L:\Operations Database\TakeOverPanel\PTMDisclosureTable.csv"
$DownloadIPTMLocation = "L:\Operations Database\TakeOverPanel\IPTMDisclosureTable.xls"
$DownloadIPTMCSVLocation = "L:\Operations Database\TakeOverPanel\IPTMDisclosureTable.csv"

#### WEB URLS ###
$PTMURL = "http://www.thetakeoverpanel.org.uk/new/disclosureTable/v3/disclosuretable.xls"
$PTMCSVURL = "http://www.thetakeoverpanel.org.uk/new/disclosureTable/v3/disclosuretable.csv"
$IPTMURL = "http://irishtakeoverpanel.ie/disclosure/disclosuretable.xls"
$IPTMCSVURL = "http://irishtakeoverpanel.ie/disclosure/disclosuretable.csv"

$Path = Get-Location

#### Load Web Service ####
$WebClient = New-Object System.Net.WebClient

#### Start download Process ####
Write-Host "Downloading PTM File 1 of 4" $Path -ForegroundColor Green
$Url = $PTMURL
$Path = $DownloadPTMLocation
$WebClient.DownloadFile($PTMURL, $DownloadPTMLocation)

Write-Host "Downloading IPTM File 2 of 3" $Path -ForegroundColor Blue
$Url = $IPTMURL
$Path = $DownloadIPTMLocation
$WebClient.DownloadFile($IPTMURL, $DownloadIPTMLocation)

Write-Host "Downloading PTM Csv File 3 of 4" $Path -ForegroundColor Gray
$Url = $PTMCSVURL
$Path = $DownloadPTMCSVLocation
$WebClient.DownloadFile($PTMCSVURL, $DownloadPTMCSVLocation)

Write-Host "Downloading IPTM File 4 of 4" $Path -ForegroundColor Red
$Url = $IPTMCSVURL
$Path = $DownloadIPTMCSVLocation
$WebClient.DownloadFile($IPTMCSVURL, $DownloadIPTMCSVLocation)



#####################


## PTM ##

#Customise Vars
$DownloadPTMCSVLocation = "L:\Operations Database\TakeOverPanel\PTMDisclosureTable.csv"
$OutputPTMCSVLocation = "L:\Operations Database\TakeOverPanel\PTMDisclosureTableb.csv"
$Match = "ISIN"
$Matchs = "NSI"

## Strips all lines that do not contain ISIN ##

(Get-Content $DownloadPTMCSVLocation) -match $Match | Out-File $OutputPTMCSVLocation

Remove-Item $DownloadPTMCSVLocation

Rename-Item $OutputPTMCSVLocation -NewName $DownloadPTMCSVLocation

(Get-Content $DownloadPTMCSVLocation) -match $Matchs | Out-File $OutputPTMCSVLocation

Remove-Item $DownloadPTMCSVLocation

Rename-Item $OutputPTMCSVLocation -NewName $DownloadPTMCSVLocation

Get-Content $DownloadPTMCSVLocation | % {
$_ -replace 'ISIN: ',''
} | % {
$_ -replace 'NSI: ',''
} | Set-Content $OutputPTMCSVLocation

Answer
#####################


## PTM ## 

$Match = "ISIN"
$Matchs = "NSI"

Import-Csv $DownloadPTMCSVLocation -Header @("A", "ISIN", "NSI", "Output") | #Import the CSV
  Where { $_.ISIN -like "ISIN: ????????????" -and $_.NSI -like "NSI:*" } | #Filter rows
  Foreach-Object {
    $_.ISIN = $_.ISIN.Replace("ISIN: ", "")
    $_.NSI = $_.NSI.Replace("NSI: ", "")

    $query = "Select * From CL Where CLISIN='$($_.ISIN)"

    # Tweak the below as appropriate
    # $_.Output = Invoke-SQL -Query

    # Pass row on through the pipeline
    Write-Output $_ 
} | Export-Csv $OutputPTMCSVLocation -NoTypeInformation

Just need to insert your SQL code (can help if needed) and it's good to go.

Basically I threw out all the file renaming as it's completely unnecessary (use variables for that sort of thing if you need to), Import-Csv returns an array of objects, one for each row so I pipe them to the filter (where ISIN is checked to be 12 chars only) then loops through sorting out the data.

Can't really write the SQL bit as I don't know what the output will be or how you will be calling it but you'll probably need to extract a property or something as just adding an object to $_.Output will mess up exporting it back to a file.