0

As a disclaimer, I'm no professional powershell scripter, but more of a 'script-kiddie' level scripter who was assigned something far outside the scope of his job, tech support. :P

I've been working on a migration script to bring some really old data into a new software app.

As a part of this, I'm using the following code to 'Join' two objects (which were imported from csv files). Because of the way the data is presented to me, I'm having to run this join script several times, against rather large data sets, and it's taking a very long time to complete.

The code I'm using is:

Import-Csv extract-accounts.csv | ForEach-Object -Begin {
    $Jobs = @{}
} -Process {
    $Jobs.Add($_.NAME,$_.ID)
}

Import-Csv Job.csv | ForEach-Object {
    $_ | Add-Member -MemberType NoteProperty -Name ContactID -Value $Jobs."$($_.DisplayName)" -PassThru
} | Export-Csv -NoTypeInformation Joined-AccountJob.csv

What I'm wondering is, can I use SQL-like commands on objects in powershell to simplify and speed-up this process? Or, would I be better to take the CSV files, push them into an SQL database (I have no knowledge of this process), perform my joins, then export them back out to csv files?

Thanks!

Red-Beard
  • 125
  • 1
  • 1
  • 6
  • You could try Q... https://github.com/harelba/q – Mark Setchell May 20 '14 at 17:56
  • Does this answer your question? [In PowerShell, what's the best way to join two tables into one?](https://stackoverflow.com/questions/1848821/in-powershell-whats-the-best-way-to-join-two-tables-into-one) – TylerH May 23 '22 at 13:17

3 Answers3

1

SQL Commands, no not exactly. Although I did find a site that described a way to do it here.

http://social.technet.microsoft.com/Forums/windowsserver/en-US/e242e1e9-6a8f-4a46-a884-8bd6811b5e35/combining-multiple-csv-files-with-powershell

Basically what they describe, and what sounds like the right way to do it to me, is to grab each column out of the CSVs you want into a variable then dump them into a text file with each variable separated by a comma.

If that doesn't work for you and you want to get them into SQL anyway this article describes the process of getting CSVs into SQL through several methods.

http://blogs.technet.com/b/heyscriptingguy/archive/2011/11/28/four-easy-ways-to-import-csv-files-to-sql-server-with-powershell.aspx

NoanCares
  • 51
  • 1
0

After looking at the options, I didn't see a way to simplify exactly what I was doing, however I did discover where-object, which helped in other areas of my project. Here's an example:

Import-Csv JobContact.csv |
Select-Object JobContactRole,JobExternalReference,ContactExternalReference |
Where-Object {$_.JobContactRole -ne "Policyholder" -and $_.JobContactrole -ne "Broker"} |
Export-Csv Cleaned-JobContact.csv -NoTypeInformation

I hope this helps someone!

Red-Beard
  • 125
  • 1
  • 1
  • 6
0

Import-Csv extract-accounts.csv | Join (Import-Csv Job.csv) -On ContactId

For details see: In Powershell, what's the best way to join two tables into one?

iRon
  • 20,463
  • 10
  • 53
  • 79