Appending exported data to file

This topic contains 5 replies, has 3 voices, and was last updated by  Rob Simmers 2 years, 3 months ago.

  • Author
    Posts
  • #27414

    Brandy Davis
    Participant

    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

    }

  • #27415

    Tim Pringle
    Participant

    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?

  • #27456

    Brandy Davis
    Participant

    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 :(.

  • #27458

    Rob Simmers
    Participant

    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.

  • #27462

    Brandy Davis
    Participant

    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

  • #27466

    Rob Simmers
    Participant

    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.

You must be logged in to reply to this topic.