{"id":10020,"date":"2023-03-30T17:17:30","date_gmt":"2023-03-30T17:17:30","guid":{"rendered":"https:\/\/zappysys.com\/blog\/?p=10020"},"modified":"2023-03-30T17:18:58","modified_gmt":"2023-03-30T17:18:58","slug":"export-create-json-file-informatica-sql-query-database-tables","status":"publish","type":"post","link":"https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/","title":{"rendered":"Export \/ Create JSON File in Informatica (from SQL Query \/ Database Tables)"},"content":{"rendered":"

Introduction<\/h2>\n

In this article, you will learn how to create simple or complex JSON file in Informatica just in a few clicks. We will use the Powerful Export JSON Command line Builder from ZappyShell<\/a>. Using this Portable \/ xCopy style command line you can build nested\/complex JSON \/ XML or even CSV from single \/ multiple SQL Queries as Input or Select Database Tables as Source. Later you can copy generated command line from Tool and execute in any tool like Informatica \/ Talend \/ DataStage or Automation Script like Python \/ PowerShell.<\/p>\n

Prerequisite<\/h2>\n

This article assumes you have access to Informatica Developer Tools and basic knowledge of command-line tools.<\/p>\n

    \n
  1. Download ZappyShell<\/a> Command line utility (Click on Download Button). You can get msi and Zip (Poratble \/ xCopy version)<\/li>\n
  2. If you want to use the Portable version just extract ZappyShell-xCopy zip to some folder lets say (C:\\ZappyShell). If you installed MSI then its usually in C:\\Program Files (x86)\\ZappySys<\/li>\n
  3. Double click on DbCommandBuilder.exe as listed here<\/a>\u00a0 to get started. At the end of this exercise, you will be able to generate something like the below JSON File in few clicks in Informatica or any other Tool \/ Programming Language you prefer which has option to invoke command line…<\/li>\n
  4. For all possible command line options Refer this<\/a><\/li>\n
  5. So lets get started<\/li>\n<\/ol>\n

    Sample JSON Created by ZappyShell from Informatica<\/h2>\n

     <\/p>\n

    \"Sample

    Sample JSON Created by Informatica Workflow (Create From Database SQL Query or Tables)<\/p><\/div>\n

    Create Command Line to Export JSON File from Database SQL Query \/ Tables<\/h2>\n

    In this section, we will see how to build Command Line which you can then execute in Informatica Command Time and later Schedule Export Job to Produce JSON File on a predefined schedule or manually.<\/p>\n

      \n
    1. Open DbCommandBuilder.exe and configure the Connection \/ Data source(s)\u00a0as listed here<\/a>\u00a0.\"ZappyShell<\/li>\n
    2. Select JSON Export Option from the Dropdown. You can also choose XML, Excel or CSV\"XML<\/li>\n
    3. You can use the Custom Layout Option if you wish to Export using custom Output Layout else just choose tables you like to export or type SQL query.
      \n\"JSON<\/li>\n
    4. Configure your Input Dataset(s) (can be SQL Queries or Tables) and define Desired Layout for your Output File.\"JSON<\/li>\n
    5. Here is just an example of how to define an Array for a nested layout in a JSON or XML file.\"Layout<\/li>\n
    6. t the end of configuration you may see command line like below (more examples listed here<\/a>)Example: Simple mode Export (Microsoft SQL Server)<\/strong>\n
      db.exe export customers --json -o c:\\data\\cust.json --connstr \"Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;\" -y<\/pre>\n
      Example: For Simple mode Export (MySQL SQL Server – ODBC Driver)<\/strong><\/p>\n
      export customers --out c:\\data\\customers.json --json --overwrite --connstr \"Driver={MySQL ODBC 5.2 ANSI Driver};Server=your_server_name_or_ip;Port=3306;Uid=user1;pwd=SomeP@ssword;Database=your_db_name\"<\/pre>\n
      Example: For custom layout mode Export
      \n<\/strong><\/strong><\/p>\n
      export --map c:\\data\\MySampleMap.xml -o c:\\data\\customers.json --json --connstr \"Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;\" -y<\/pre>\n<\/li>\n
    7. Once the Command line is created you can click Run<\/strong>\u00a0to test and then copy it into tools like Informatica Command Task (see next section).<\/li>\n<\/ol>\n
      Export \/ Create JSON File In Informatica<\/h2>\n
        \n
      1. Once the command line is prepared (see previous section) we can open Informatica Developer (D)<\/li>\n
      2. Connect to your Repository and create a new workflow<\/li>\n
      3. Drag new Command Task,\u00a0from Toolbox and double click to edit\n
      4. Enter the sample command line we obtained from the previous step<\/li>\n
      5. Save the workflow and run it… That’s it you will see the JSON file generated<\/li>\n<\/ol>\n
        Export \/\u00a0Create XML File In Informatica<\/h2>\n
        Same Steps as above except choose File format as XML and you will see output like the below once you run the command line.<\/p>\n
        \"Export
        Export \/ Create XML File In Informatica<\/p><\/div>\n
        Export \/ Create CSV File In Informatica<\/h2>\n
        Same Steps as above except choose File format as CSV and you will see output like the below once you run the command line.<\/p>\n
        \"Generate
        Generate CSV Files in Informatica<\/p><\/div>\n
        Export \/ Create Excel File In Informatica<\/h2>\n
        Same Steps as above except choose File format as Excel and you will see output like the below once you run the command line.<\/p>\n
        \"Export<\/p>\n
        Conclusion<\/h2>\n
        In this post, we just saw how easy it is to generate JSON \/ XML \/ Excel or CSV files in tools like Informatica, Talend, python, PowerShell in few clicks without coding. You can schedule job to generate JSON \/ XML files from Database tables or SQL Query. Download ZappyShell<\/a> Today and try it to see what else you can do with it.<\/p>\n","protected":false},"excerpt":{"rendered":"
        Introduction In this article, you will learn how to create simple or complex JSON file in Informatica just in a few clicks. We will use the Powerful Export JSON Command line Builder from ZappyShell. Using this Portable \/ xCopy style command line you can build nested\/complex JSON \/ XML or even CSV from single \/ […]<\/p>\n","protected":false},"author":1,"featured_media":10021,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[612],"tags":[97,128,21,75,6,7],"yoast_head":"\r\nExport \/ Create JSON File in Informatica (from SQL Query \/ Database Tables) | ZappySys Blog<\/title>\r\n<meta name=\"description\" content=\"Export \/ Create JSON File in Informatica (from SQL Query \/ Database Tables) - ZappySys Blog\" \/>\r\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\r\n<link rel=\"canonical\" href=\"https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/\" \/>\r\n<meta property=\"og:locale\" content=\"en_US\" \/>\r\n<meta property=\"og:type\" content=\"article\" \/>\r\n<meta property=\"og:title\" content=\"Export \/ Create JSON File in Informatica (from SQL Query \/ Database Tables) | ZappySys Blog\" \/>\r\n<meta property=\"og:description\" content=\"Export \/ Create JSON File in Informatica (from SQL Query \/ Database Tables) - ZappySys Blog\" \/>\r\n<meta property=\"og:url\" content=\"https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/\" \/>\r\n<meta property=\"og:site_name\" content=\"ZappySys Blog\" \/>\r\n<meta property=\"article:author\" content=\"https:\/\/www.facebook.com\/ZappySys\/\" \/>\r\n<meta property=\"article:published_time\" content=\"2023-03-30T17:17:30+00:00\" \/>\r\n<meta property=\"article:modified_time\" content=\"2023-03-30T17:18:58+00:00\" \/>\r\n<meta property=\"og:image\" content=\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2023\/03\/informatica-command-task.png\" \/>\r\n\t<meta property=\"og:image:width\" content=\"681\" \/>\r\n\t<meta property=\"og:image:height\" content=\"281\" \/>\r\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\r\n<meta name=\"author\" content=\"ZappySys\" \/>\r\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\r\n<meta name=\"twitter:creator\" content=\"@https:\/\/twitter.com\/zappysys\/\" \/>\r\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"ZappySys\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"4 minutes\" \/>\r\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"WebPage\",\"@id\":\"https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/\",\"url\":\"https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/\",\"name\":\"Export \/ Create JSON File in Informatica (from SQL Query \/ Database Tables) | ZappySys Blog\",\"isPartOf\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2023\/03\/informatica-command-task.png\",\"datePublished\":\"2023-03-30T17:17:30+00:00\",\"dateModified\":\"2023-03-30T17:18:58+00:00\",\"author\":{\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\"},\"description\":\"Export \/ Create JSON File in Informatica (from SQL Query \/ Database Tables) - ZappySys Blog\",\"breadcrumb\":{\"@id\":\"https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/#primaryimage\",\"url\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2023\/03\/informatica-command-task.png\",\"contentUrl\":\"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2023\/03\/informatica-command-task.png\",\"width\":681,\"height\":281,\"caption\":\"Informatica Command Task\"},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\/\/zappysys.com\/blog\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Export \/ Create JSON File in Informatica (from SQL Query \/ Database Tables)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/zappysys.com\/blog\/#website\",\"url\":\"https:\/\/zappysys.com\/blog\/\",\"name\":\"ZappySys Blog\",\"description\":\"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/zappysys.com\/blog\/?s={search_term_string}\"},\"query-input\":\"required name=search_term_string\"}],\"inLanguage\":\"en-US\"},{\"@type\":\"Person\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82\",\"name\":\"ZappySys\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g\",\"caption\":\"ZappySys\"},\"sameAs\":[\"http:\/\/www.zappysys.com\/\",\"https:\/\/www.facebook.com\/ZappySys\/\",\"https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/\"],\"url\":\"https:\/\/zappysys.com\/blog\/author\/admin\/\"}]}<\/script>\r\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"Export \/ Create JSON File in Informatica (from SQL Query \/ Database Tables) | ZappySys Blog","description":"Export \/ Create JSON File in Informatica (from SQL Query \/ Database Tables) - ZappySys Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/","og_locale":"en_US","og_type":"article","og_title":"Export \/ Create JSON File in Informatica (from SQL Query \/ Database Tables) | ZappySys Blog","og_description":"Export \/ Create JSON File in Informatica (from SQL Query \/ Database Tables) - ZappySys Blog","og_url":"https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/","og_site_name":"ZappySys Blog","article_author":"https:\/\/www.facebook.com\/ZappySys\/","article_published_time":"2023-03-30T17:17:30+00:00","article_modified_time":"2023-03-30T17:18:58+00:00","og_image":[{"width":681,"height":281,"url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2023\/03\/informatica-command-task.png","type":"image\/png"}],"author":"ZappySys","twitter_card":"summary_large_image","twitter_creator":"@https:\/\/twitter.com\/zappysys\/","twitter_misc":{"Written by":"ZappySys","Est. reading time":"4 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"WebPage","@id":"https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/","url":"https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/","name":"Export \/ Create JSON File in Informatica (from SQL Query \/ Database Tables) | ZappySys Blog","isPartOf":{"@id":"https:\/\/zappysys.com\/blog\/#website"},"primaryImageOfPage":{"@id":"https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/#primaryimage"},"image":{"@id":"https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/#primaryimage"},"thumbnailUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2023\/03\/informatica-command-task.png","datePublished":"2023-03-30T17:17:30+00:00","dateModified":"2023-03-30T17:18:58+00:00","author":{"@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82"},"description":"Export \/ Create JSON File in Informatica (from SQL Query \/ Database Tables) - ZappySys Blog","breadcrumb":{"@id":"https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/#primaryimage","url":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2023\/03\/informatica-command-task.png","contentUrl":"https:\/\/zappysys.com\/blog\/wp-content\/uploads\/2023\/03\/informatica-command-task.png","width":681,"height":281,"caption":"Informatica Command Task"},{"@type":"BreadcrumbList","@id":"https:\/\/zappysys.com\/blog\/export-create-json-file-informatica-sql-query-database-tables\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/zappysys.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Export \/ Create JSON File in Informatica (from SQL Query \/ Database Tables)"}]},{"@type":"WebSite","@id":"https:\/\/zappysys.com\/blog\/#website","url":"https:\/\/zappysys.com\/blog\/","name":"ZappySys Blog","description":"SSIS \/ ODBC Drivers \/ API Connectors for JSON, XML, Azure, Amazon AWS, Salesforce, MongoDB and more","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/zappysys.com\/blog\/?s={search_term_string}"},"query-input":"required name=search_term_string"}],"inLanguage":"en-US"},{"@type":"Person","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/2756c237457fbc95d82cb38962f81f82","name":"ZappySys","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/zappysys.com\/blog\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/5c9be148088ba9b8af8e955c5f7c22b5?s=96&d=mm&r=g","caption":"ZappySys"},"sameAs":["http:\/\/www.zappysys.com\/","https:\/\/www.facebook.com\/ZappySys\/","https:\/\/twitter.com\/https:\/\/twitter.com\/zappysys\/"],"url":"https:\/\/zappysys.com\/blog\/author\/admin\/"}]}},"_links":{"self":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/10020"}],"collection":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/comments?post=10020"}],"version-history":[{"count":2,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/10020\/revisions"}],"predecessor-version":[{"id":10023,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/posts\/10020\/revisions\/10023"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media\/10021"}],"wp:attachment":[{"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/media?parent=10020"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/categories?post=10020"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/zappysys.com\/blog\/wp-json\/wp\/v2\/tags?post=10020"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}