Author Posts

July 11, 2015 at 12:04 am

I am very new to PowerShell and starting to learn. I found a script and modified it as follows below. I am trying to export the contact list of several users from an SQL database to an XML file using dbimpexp, It is working but on my "for each" statement the file gets overwritten every time it process a new user. How could I append to the file while it loops through all the users.

$inputlist = "C:\users.txt"
$outputresultsfile = "C:\alluserscontacts.xml"
$sqlserver = "sql01"
$DBImpExp = "D:\Office Communication Server 2007 R2\Support\dbimpexp.exe"
$userlist = Get-Content $inputlist

ForEach ($user in $userlist) {
$output = & DBImpExp /hrxmlfile:$outputresultsfile /sqlserver:$sqlserver /user:$user

}

July 11, 2015 at 1:01 am

Hi Brandy,

I've never used dbimpexp.exe before, so can't comment on how it operates. However, it might be possible to get round by changing the filename for every user, and then grouping the information together after the last export has happened.

I'm thinking along the lines of something like this :

ForEach ($user in $userlist) {
$output = & DBImpExp /hrxmlfile:$user.xml /sqlserver:$sqlserver /user:$user
}

Is it possible for you to post how the xml looks, so I can try and help you with joining the data together?

July 13, 2015 at 8:46 am

Hi Tim,
I appreciate the help. What dbimpexp does, it extracts OCS instant messaging buddy contact info from a database and puts it in an XML file for each users. It actually create a block of info for each user, one after the other on the same file. At this point, every time I execute the script, it creates the block of data for a user and then it loops and overwrites the data with the next user data, instead of appending the next user to the file. I cant post the xml file because it contains addresses and phone numbers :(.

July 13, 2015 at 9:46 am

I did a quick search for "Powershell dbimpexp" and there a lot of solutions for DR for importing\exporting this data. Most of these solutions are not exporting individual users, they are exporting the entire database.

In your script, you just running a command (dbimpexp) multiple times for each user. You are specifying the same path for the result of the command, which is "C:\alluserscontacts.xml". Even though you are running it for each user, you are going to overwrite the file each time the command is run because you are not providing a unique path like Tim is showing you ( /hrxmlfile:$user.xml ). In this scenario, if there were 5 users, you would have a XML created for each user. Once that is complete, the assumption is you want to consolidate the XML files into a single XML file. What are you going to do with the data? Do you want to parse it and put it in a CSV? What is the end goal? If you want to parse the data, you need to post the XML format with fake data so that we can look at the format and show you how to parse or even consolidate the XML into a single XML file. You don't need any huge data, just enough of an example to assist you with parsing.

July 13, 2015 at 10:38 am

I could just run the dbimpexp command natively but it will export ALL user's data in the company with one swipe which did previously. The reason I am using Powershell and making it read from a file for specific users is that these users in the file failed to import due to a quota issue so I am importing them separately.
My end goal was to run Powershell and extract the users data to an xml file named userscontacts.xml for example. Each time a users loops through the script, the usercontacts.xml file gets appended. This is how it works when the dbimpexp is run natively without the need of Powershell. I could try Tim's solution but it will create a separate file for each user which wouldn't help me as I will still have to open 100+ files to cut and paste the data. Here is a sample of the xml for a single user










Michelle Smith

Michelle.Smith@domain.com










420

0
00:00:00
0
0
Sunday


0
00:00:00
0
0
Sunday



Monday Tuesday Wednesday Thursday Friday
480
1020






true
(602) 639-6786
tel:6026396786;phone-context=Campus




None
3300 W Camelback Rd
Phoenix





18000




6500





18000




6500

July 13, 2015 at 11:11 am

Brandy,

You XML was eaten by the Powershell.org XML gremlins. Try posting the XML on GIST.

Understand that the exe you are running isn't dumping XML data as output from the command. You have two basic choices.

  1. Export all records and parse the XML with Powershell to remove XML nodes that do NOT meet the user criteria (e.g. Maria.Smith).
  2. Export each user's file individually and the loop through each file, open it as XML, grab the content you want and then generate a final XML file in Powershell.