Results 1 to 2 of 2

Thread: Using POWERSHELL to add tables from CSV file to Replicate JSON task defintion

  1. #1
    Hein is offline Senior Member
    Join Date
    Dec 2007
    Location
    Nashua, NH - USA.
    Posts
    165
    Rep Power
    13

    Using POWERSHELL to add tables from CSV file to Replicate JSON task defintion

    The attached script parses a CSV file with table owner/name information to add those to a Replicate JSON Task definition, if it does not already exist.
    The exportrespository and importrepository is expect to be done outside the script after verifications and further changes perhaps.

    Here is the core code for the script

    Code:
    $my_json_object  = ConvertFrom-Json $old_json_string
    :
    foreach ($task in $my_json_object."cmd.replication_definition".tasks) {
    : 
        $prop = @{name=$table; owner=$owner; estimated_size='0'} 
        $task.source.source_tables.explicit_included_tables += New-Object pscustomobject -Property $prop
    :
    ConvertTo-Json $my_json_object -depth 99   ... out-file -FilePath $OutputJson -encoding ASCII

    Enjoy!
    Hein
    Attached Files Attached Files

  2. #2
    Hein is offline Senior Member
    Join Date
    Dec 2007
    Location
    Nashua, NH - USA.
    Posts
    165
    Rep Power
    13
    Quote Originally Posted by Hein View Post
    The attached script ...
    :
    ConvertTo-Json $my_json_object -depth 99 ... out-file -FilePath $OutputJson -encoding ASCII
    :
    [/CODE]
    The "To-Json" Powershell function is rather verbose, and seems to have a bug (special feature) handling single quotes in the output.
    Those are escaped as \u0027. It is not clear to me why, not did I find a way to disable that.
    The script is ready to blindly replace them, just remove the "#" in the lien with "# -replace "\\u0027","'"
    Hmm, I may have uploaded the wrong version of the scripts. Anyway, easy to fix.

    Comment from script and additional brute-force cleaning build in for now:

    Code:
    # Generate JSON and post-process to make it look more like the Replicate JSON (and make it smaller)
    # More importantly, the json generator seems to specicial cas a single-quote outputting \u0027. Brute-force fix that here.
    :
    $new_json_string = $new_json_string -replace ":  \[.\n\s+{",":  [{"   -replace "},.\n\s+{","},{"   -replace "}.\n\s+\]","}]"   -replace "\n {47}", "`n`t" # -replace "\\u0027","'"
    $new_json_string -replace " {8}", "`t" | out-file -FilePath $OutputJson -encoding ASCII

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •