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. 😛
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!
Advertisement
Answer
SQL Commands, no not exactly. Although I did find a site that described a way to do it here.
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.