QuickBooks and Sage Data Exporter

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.

SageODBC1

Configure silent mode in Options Tab.

SageODBC2.png

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

  1. Load TableNames from database
  2. Load DataSet for each Table
  3. 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;">&#65279;</span>
        private static string ConvertToCSV(DataSet objDataSet)
        {
            StringBuilder content = new StringBuilder();

            if (objDataSet.Tables.Count &gt;= 1)
            {
                DataTable table = objDataSet.Tables[0];

                if (table.Rows.Count &gt; 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 &lt; intColumnCount)
                            content.Append(&quot;,&quot;);
                        else
                            content.Append(&quot;\r\n&quot;);
                        index++;
                    }

                    //add column data
                    foreach (DataRow currentRow in table.Rows)
                    {
                        string strRow = string.Empty;
                        for (int y = 0; y &lt;= intColumnCount - 1; y++)
                        {
                            strRow += &quot;\&quot;&quot; + currentRow[y].ToString() + &quot;\&quot;&quot;;

                            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.

  1. Load TableNames from file ( Quickbooks doesn’t export Schema in its ODBC Datasource)
  2. Load DataSet for each Table
  3. Export each table to CSV from DataSet

Below link in git hub has code to do this

https://github.com/ashwinrayaprolu1984/QuickBooksDesktopConnector.git

 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s