Many small and medium business work with QuickBooks for their accounting module. Similarly many companies also use Sage for their accounting. Most of them face problem when they need to export data out of these systems.
Many of the connectors that are available online are costly and don’t meet exact requirements. Attached is small snippets of code that explains what was done to export their data into CSV . I’ve also attached github links to download the code.
Both SAGE and Quickbooks come with ODBC Drivers that can be configured and programmatically queried
#Sage
Create a silent ODBC DSN in your ODBC DataSource.
Configure silent mode in Options Tab.
Now we are going to use below datasource to load and export data.
We are going to DotNet Core to write our code as Its best language to talk to DSN on windows
I broke the problem into 3 different parts
- Load TableNames from database
- Load DataSet for each Table
- Export each table to CSV from DataSet
private static List loadTableNames(string connectionString){
var tableNames = new List();
using (OdbcConnection connection =
new OdbcConnection(connectionString))
{
try
{
connection.Open();
using(DataTable tableschema = connection.GetSchema("Tables"))
{
// first column name
foreach(DataRow row in tableschema.Rows)
{
tableNames.Add(row["TABLE_NAME"].ToString());
//Console.WriteLine(row["TABLE_NAME"].ToString());
}
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
}
return tableNames;
}Now we need to write code to load data for given table. I’m going to use DataSet in this case. There are many ways to do this..
public static DataSet GetDataSetFromAdapter(
DataSet dataSet, string connectionString, string queryString)
{
using (OdbcConnection connection =
new OdbcConnection(connectionString))
{
OdbcDataAdapter adapter =
new OdbcDataAdapter(queryString, connection);
// Open the connection and fill the DataSet.
try
{
connection.Open();
adapter.Fill(dataSet);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
// The connection is automatically closed when the
// code exits the using block.
}
return dataSet;
}And finally below is function to export all data to CSV
<span id="mce_SELREST_start" style="overflow:hidden;line-height:0;"></span>
private static string ConvertToCSV(DataSet objDataSet)
{
StringBuilder content = new StringBuilder();
if (objDataSet.Tables.Count >= 1)
{
DataTable table = objDataSet.Tables[0];
if (table.Rows.Count > 0)
{
DataRow dr1 = (DataRow) table.Rows[0];
int intColumnCount = dr1.Table.Columns.Count;
int index=1;
//add column names
foreach (DataColumn item in dr1.Table.Columns)
{
content.Append(String.Format("\"{0}\"", item.ColumnName));
if (index < intColumnCount)
content.Append(",");
else
content.Append("\r\n");
index++;
}
//add column data
foreach (DataRow currentRow in table.Rows)
{
string strRow = string.Empty;
for (int y = 0; y <= intColumnCount - 1; y++)
{
strRow += "\"" + currentRow[y].ToString() + "\"";
if (y = 0)
strRow += ",";
}
content.Append(strRow + "\r\n");
}
}
}
return content.ToString();
}https://github.com/ashwinrayaprolu1984/SageDataExporter.git
#QuickBooks
We follow same approach for QuickBooks.
- Load TableNames from file ( Quickbooks doesn’t export Schema in its ODBC Datasource)
- Load DataSet for each Table
- Export each table to CSV from DataSet
Below link in git hub has code to do this
https://github.com/ashwinrayaprolu1984/QuickBooksDesktopConnector.git
| Published on Java Code Geeks with permission by Ashwin Kumar, partner at our JCG program. See the original article here: QuickBooks and Sage Data Exporter Opinions expressed by Java Code Geeks contributors are their own. |
