How to compare csv file with sql database using PowerShell

Question

I've latest data in my CSV file, I'm trying to compare to my SQL Server Database Example1 table that contain old data using the EmpId and Name columns. If there is new data in the csv file then update Example1 table and insert only the new data into Example2 table.

I'm beginner and I'm stuck with the logic part so I would be really appreciated if I can get any help or suggestion. Any guidance would be really appreciated.

My CSV file, Example1 and Example2 database tables look like this

EmpId    Name           Hold
324   John Smith     Yes
432   Tim Cook       Yes

        $csv_file = "C:\scripts\user-info.csv"
        
        $csvImport = import-csv $csv_file

        foreach ($i in $csvImport){
              $EmpId = $i.EmpId
              $Name = $i.Name
              $Hold = $i.Hold

        }


        $Server = 'Server'
        $query = "SELECT EmpId,Name FROM Example1"
        $Database = 'DatabaseName'
        $result = Invoke-Sqlcmd -Query $query -ServerInstance $Server -Database $Database  | Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors

Why can't you load the data into a SQL Server table. Consider it like a staging table and do the required Update and Insert operation from there with the help of that staging table? That will make it easier. — Teja Goud Kandula
– Teja Goud Kandula, Commented Apr 12, 2022 at 4:36
I'm not sure what you mean. At the beginning, I'll load all my data into Example1 table. Other team will send me the CSV file everyday that's why, I'll need to compare to my Example1 table just incase there is a new user. — aasenomad
– aasenomad, Commented Apr 12, 2022 at 4:43

Theo · Accepted Answer · 2022-04-12 12:55:05Z

1

Since I'm always struggling with the Compare-Object cmdlet, here's an option to 'manually' compare the differences

Assuming the EmpId field is unique:

$Server   = 'Server'
$query    = "SELECT EmpId,Name FROM Example1"
$Database = 'DatabaseName'
$dbData   = Invoke-Sqlcmd -Query $query -ServerInstance $Server -Database $Database | 
            Select-Object * -ExcludeProperty ItemArray, Table, RowError, RowState, HasErrors
$csvData  = Import-Csv -Path 'C:\scripts\user-info.csv'

# iterate over the data rows in the CSV file
$result = foreach ($item in $csvData) {
    $dbRecord = @($dbData | Where-Object { $_.EmpId -eq $item.EmpId })
    if ($dbRecord.Count -eq 0) {
        # this is a new user to be added in the database

        # you can perform the INSERT statement right here or do that
        # afterwards by iterating the $result array and checking for items
        # where the Status field is 'New'.
        # for the latter, output an object:
        $item | Select-Object *, @{Name = 'Status'; Expression = {'New'}}
    }
    else {
        $dbRecord | ForEach-Object {
            if ($_.Name -ne $item.Name -or $_.Hold -ne $item.Hold) {
                # this is an existing record that needs updating. output what is in the CSV

                # you can perform the UPDATE statement right here or do that
                # afterwards by iterating the $result array and checking for items
                # where the Status field is 'Update'.
                # for the latter, output an object:
                $item | Select-Object *, @{Name = 'Status'; Expression = {'Update'}}
            }
        }
    }
}

Now in variable $result you should have an array of all differences including a status property from which you know whether to insert a new record or to update an existing one.

answered Apr 12, 2022 at 12:55

Theo

61.9k8 gold badges28 silver badges48 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

aasenomad Over a year ago

Hi @Theo , I'm really appreciated for the help and thank you so much. quick question the new data is not inserting to my Example2 table so could you please help with that again here? stackoverflow.com/questions/71846202/…

Drew Brooks · Accepted Answer · 2022-04-12 21:20:22Z

0

It's best to do what Teja Goud Kandula mentioned...load the full csv to a stage table, then use SQL commands to quickly insert new or update existing records (aka UPSERT). This is an extremely common scenario. The steps are:

TRUNCATE the stage table
Run your Powershell script to load csv to the stage table
Run SQL script to INSERT or UPDATE from stage to production table

Finally, if you have SQL Server then you probably have SSIS, which is made to do exactly these types of steps...without the need for Powershell.

answered Apr 12, 2022 at 21:20

Drew Brooks

631 silver badge5 bronze badges

Collectives™ on Stack Overflow

How to compare csv file with sql database using PowerShell

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related