Import .csv (conditional, for each row)

PowerShell Help
#1

Hello,

first of all, I am really new to PowerShell and by far no Techi.
I am more like the process management guy who likes to understand the basic ideas behind the technical solution.

So, the situation is as follows:

First database: Active Directory

  • with the columns: user; office; place; ID (and many more, but this is not relevant yet).
    -> This database is very well maintained and up to date. Size: 4500 user

Second database: SQL - database (showing all the hardware that is used)

  • structure: computer name ; serial nr.; ID of the user; username; office; place; status of the computer (f.e. “used” or “sent” or “defect”)
    -> This database is NOT very well maintained and NOT up to date in terms of office and place. Size: total of 30.000 computers, printers etc.

The goal is, to build an interface which updates the information of “username”, “office”, and “place” in the second database (SQL) based on the information from AD. The link should be the ID, because the ID is in both databases the same for each user.

So, the general idea is:
1.) with powershell there will be an export of AD into a .csv - file containing the following information:
ID; user; office; place
2.) there should be an import into the SQL database based on the ID.

I think and what I have heard so far, this is not quite an issue and possible to implement.
But now there is the issue that according to the information that is given in the column “status” in the sql database, the import should be differentiate. That means f.e. if the status is “defect”, than there is no import needed, instead the script should not be able to overwrite the columns “user”, “office”, and “place”.
If I am not mistaken, it is necessary to “read” the output of the column “status” first, before importing something.

What I am looking for now, is not a finished script, but rather an explanation which commands could/should be used and in which order.

I am thankful for any advice, tips etc.
Hopefully, I could make the situation anyhow understandable. If not, please tell me, which point is not clear and I will do my best to clarify it.

Thanks in advance! :slight_smile:

#2

You not describe what state of records in second db besides ID
each ID have several records in second DB ? all of it should be updated except these with status “defect” ?

if so it should be not powershell but sql question :slight_smile:
and answer is
‘update DB02 set offce=? … where id=? and status != “defect”’
so, there is no powershell involved in database update filtering
and you basically need to write in powershell something like

connect-sql
import-csv | foreach-object {
update-sql $_
}
disconnect-sql

where update-sql is your function in which you prepare and execute sql clause showed above
and connect/disconnect -sql literally do what it mean

the question ‘how to access sql from powershell’ left beyond this topic…

#3

Hey Max,

Thank you for your reply!

I will try to answer your questions the best I could:

each ID have several records in second DB ?
-> yes, f.e. one user (one ID) has one computer, one monitor, one desktop and one printer, each in a single record.

all of it should be updated except these with status “defect” ?
-> no, its not that easy. Lets assume there are 4 possible values of status: 1,2,3 and 4
The following should happen:
If status = 1, then update username, office and place
If status = 2, then update place and remove the information of username and office
If status = 3, then do not update anything and do no changes to the current information
If status = 4, then remove every information of username, office and place

Regarding your approach:
'update DB02 set office=? … where id=?
-> If I am not mistaken I would have to write a script for this, to build the right pair between ID and office for each user.
I was thinking of a PowerShell script which does that automatically. In the end, this update/import should be an daily task which runs automatically (therefor I thought PowerShell is needed).

Thanks again for your help so far

#4

ok, Despite the fact that even these conditions can be performed on a clean sql
we can do it on powershell too :slight_smile:
my script skeleton above can do what you want.

after “import-csv” you get array of records with properties like columns in you data source
“foreach-object” get all of these records one by one and “send” it to “update-sql”
in which you can invoke sql update procedure. so, if you want to do filtering on sql side you should write your update-sql function like

function Update-SQL {
param(
  $id,
  $office,
  $place
#...
)
invoke-sqlcommand 'update db02 set ...... ' -Parameters @{ID = $ID; Office=$Office }
}

but if you want filtering on powershell side you just change update-sql with some spaghetti :wink: …

function Update-SQL {
param(
  $id,
  $office,
  $place
#...
)
$recorstate = invoke-sqlcommand 'select state from db02 where id=@id' -Parameters @{id=$id}
if ($recordstate.state -eq 1) {
  invoke-sqlcommand 'update db02 set ...... ' -Parameters @{ID = $ID; Office=$Office }
}
elseif ($recordstate.state -eq 2) {
  invoke-sqlcommand 'update db02 set ...... ' -Parameters @{ID = $ID; Office=$Office }
}
elseif ($recordstate.state -eq 3) {
  invoke-sqlcommand 'update db02 set ...... ' -Parameters @{ID = $ID; Office=$Office }
}
else {
  # for example there no update at all
}
}

where each bransh of code have different sql update clause

both code versions should call update sql like

...
foreach-object {
  update-sql -id $_.id -office $_.office -place $_.place
}
...
#5

Thank you so much for the answer!!
I will dig into that :slight_smile: