Convert file format / Export REST API to CSV, JSON, XML, Excel - Command Line / ODBC Driver – ZappySys Help Center

In our previous post, we saw how to convert JSON to CSV using SSIS ETL approach. Now in this post we will cover how to Convert file formats without SSIS. You will also learn how to use ODBC Drivers for REST / CSV / JSON / XML

To make more interesting, we will not use SSIS and go pure Command Line approach this time. We will use ZappyShell Command Line tool to Convert CSV files to JSON. We will also see how to Export REST API data to the desired format.

For example purpose, we will use ZappySys CSV ODBC Driver to read data from CSV file / CSV API (URL). Approach displayed in this post use CSV as Source but you can use any Relational source becuase ZappyShell support ODBC / ADO.net / OLEDB Drivers to read data from any source , as long as you have a driver installed (e.g. Driver for SQL Server, Oracle, MySQL)

In this article we will use db.exe utility comes with ZappyShell. You can do the following awesome things with this tool.

Export relational data to CSV, XML, JSON, Excel, HTML, PDF (Check this article)
Export REST / SOAP API Data to CSV, XML, JSON, Excel, HTML, PDF
Split CSV files into multiple files (You can use ZappySys CSV Driver) and output as CSV with --split option
Export and generate compressed files automatically
Split files by data value (e.g. Split customer data by country - one file for each country)
Convert JSON to CSV or other format XML, Excel, HTML, PDF
Convert XML to CSV or other format JSON, Excel, HTML, PDF
Convert CSV to XML or other format JSON, Excel, HTML, PDF
Convert CSV to XML or other format JSON, Excel, HTML, PDF

Here is simple Use case of db.exe command line (Export SQL Server Table to CSV)

db.exe export "select * from customers" --csv -o c:\data\customers.csv --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;" -y

Steps to Convert CSV to JSON (Using ODBC)

Now let's look at few steps to convert CSV data to JSON format. We will see first how to export CSV file to JSON and then we will see how to Read CSV data from REST API URL and convert to JSON.

First download ZappyShell Command Line and ZappySys CSV ODBC Driver
Now we will define JSON Layout. ZappyShell uses special Layout format to output nested looking JSON.Layout file is an XML file. It contains Tables / SQL Query which will be used to generate JSON File, It will include Columns you want to output and other information such as dataset relationship if you need Array inside JSON.
Open Notepad and paste following Sample XML Layout. To learn more about Layout Syntax Check this help page. In below example, we use select * from _root_ to query CSV File but you can enter any valid SQL Query or Table name exposed by Driver. You can enter SQL Query or Just Table name. If you have nested JSON (e.g. Customers > Orders > OrderItems then you have to define multiple datasets and JOINS for them. Refer Help file for nested JSON.

MySampleMap.xml
```
<?xml version="1.0" encoding="UTF-8"?>
<settings ver="1">
 <dataset id="cust" main="true">select * from _root_</dataset>

 <map src="CustomerID" name="CustomerID_Alias" />
 <map src="CompanyName" />
 
 <map name="ContactInfo">
   <map src="Phone" />
   <map src="Fax" />
   <map name="Location">
     <map src="Country" />
     <map src="Region" />
   </map>
 </map>
</settings>
```
Now Open Command line and Paste following Command and Hit Enter to change directory.
```
cd "c:\Program Files (x86)\ZappySys\ZappyShell"
```

Now enter following command and hit enter to run db.exe to export CSV to JSON.

Read CSV From URL and Convert to JSON

db.exe export -y --map c:\test\MySampleMap.xml --out c:\test\customers.json --connstr "Driver={ZappySys CSV Driver};DataPath=https://zappysys.com/downloads/files/test/customers.csv"

Read CSV from Local File and Convert to JSON

db.exe export -y --map c:\test\MySampleMap.xml --out c:\test\customers.json --connstr "Driver={ZappySys CSV Driver};DataPath=c:\test\customers.csv"

Here is the screenshot of execution.

You can schedule this single line command line from any ETL or Programming tool to export data in JSON. You can do in PowerShell, Python, Informatica and many more.... or simply schedule via Windows Scheduler to automate your tasks.

Export REST API Data to CSV / JSON / XML / Excel

Here is another example of how to use ZappySys ODBC Driver to Read from REST API and export to CSV / JSON/ XML / Excel

First Configure ODBC DSN using this approach

Then you can all something like below to export your REST API into CSV format

db.exe export "select * from _root_" --csv -y --out c:\test\data.csv --connstr "Dsn=MyOdbcDsn"

Example : PowerShell - Export CSV to JSON

Here how to export CSV to JSON in PowerShell

&"c:\Program Files (x86)\ZappySys\ZappyShell\db.exe" export -y --map c:\test\MySampleMap.xml --out c:\test\customers.json --connstr "Driver={ZappySys CSV Driver};DataPath=https://zappysys.com/downloads/files/test/customers.csv;"

SQL Server Excel Export – Single table

c:\tools>db.exe export customers --out c:\data\customers.xlsx --excel --overwrite --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

SQL Server Excel Export – Multiple tables

In the following example, we are exporting 3 tables (separated by a vertical bar)

c:\tools>db.exe export customers|orders|products --out c:\data\customers.xlsx --excel --overwrite --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

SQL Server Excel Export – Multiple tables (using pattern)

In below example all tables where name staring with cust will be exported from sales schema.

c:\tools>db.exe export cust% --schema Sales --out c:\data\customers.xlsx --excel --overwrite --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

SQL Server Excel Export – SQL Query

In the following example, we are exporting output of SQL Query to Excel file. If you have a complex query with multi-line then you can use Map file with one dataset node.

c:\tools>db.exe export "select * from customers where country like 'US%'" --out c:\data\customers.xlsx --excel --overwrite --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

SQL Server Excel Export – Split Files and Tabs

Sometimes you may have the need to split a large amount of data into multiple files. With ZappyShell command line you can split data three different ways. Let's check each example.

SQL Server Excel Export – Split By Row Count

In below example, we are exporting SQL Server customer table to Excel files and splitting files with maximum 1000 records per file option.

c:\tools>db.exe export customers --out c:\data\cust.xlsx --split --splitrows 1000 --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

SQL Server Excel Export – Split By File Size

In below example, we are exporting SQL Server customer table to Excel files and splitting files with maximum file size = 10MB option. You also use another size specifier like KB, GB, BYTES etc.

c:\tools>db.exe export customers --out c:\data\cust.xlsx --split --splitsize 10MB --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

SQL Server Excel Export – Split By Group By Column

In this example, we have exported customers in to separate file by country.

c:\tools>db.exe export customers --out c:\data\cust.xlsx --groupby Country --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

SQL Server Excel Export – Table to Excel, Split files by Country Column

SQL Server Excel Export – Split sheets By Group By Column

In this example, we have exported customers into a separate sheet by country.

c:\tools>db.exe export customers --out c:\data\cust.xlsx --groupby Country --excel-split-ontab --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

ZappyShell – Export SQL Server to Excel Split on tabs

Exporting Compressed Excel files

If you want to output compressed Excel (GZIP format) then use below command.

c:\tools>db.exe export customers --out c:\data\cust.xlsx --gzip --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

Using date time placeholders in Excel file name

If you want to use date time placeholders in the file name so it automatically uses specified date-time parts use below command simply putting specifiers in file name anywhere.

c:\tools>db.exe export customers --out c:\data\cust_{{YYYY}}_{{MM}}_{{DD}}_{{HH24}}{{MI}}{{SS}}.xlsx --connstr "Data Source=localhost;Initial Catalog=Northwind;Integrated Security=SSPI;"

Conclusion

In this article we have gone through few ways to use ZappyShell Command Line Tools to export data from SQL Server to Excel. You can also use same command line to export in CSV, JSON, PDF format.

Contact Us

If you have more question(s) feel free to contact us via Live chat or email to support@zappysys.com

https://zappysys.com/support/