Hi, I have converted my business and personal finances from QB to SQL-ledger. Because QB does not have an export function (except for COA) it is not easy. I basically printed a report to a file and imported the data directly into the postgres database after massaging it in a spreadsheet. I suspect there may be better ways, but this worked for me. Here is what I did:
1. I created a blank SQL-ledger database. SQL-Ledger uses a postgres sequence (id) to assign unique numbers to all its records. During the course of my import, I had to restart this sequence at a higher number so SQL-Ledger would not use the same id number twice. 2. I exported the COA from QB and then loaded it into a spreadsheet. I then exported the chart table from SQL-Ledger using pgaccess and imported it into a spreadsheet. I matched the columns up in the two spreadsheets and appended the data from QB to the SQL-ledger spreadsheet. The one big change is to create new id numbers for the accounts migrated from QB. These ID numbers must be larger than the id sequence number in postgres (use pgaccess to see this number). Once this is done, then save the data as tab delimited and import it back into SQL-ledger using pgaccess and reset the id sequence to start with a number higher than the highest one you used on your imported QB accounts. 3. I ran a trial balance from QB for the start of the year (or whenever you want to start) and a trial balance for the most recent date (e.g. after your last transaction). I then hand entered the first trial balance into SQL-ledger. This gave me a starting point. The trial balance report in SQL-Ledger for the starting date (first of the year for me) was the same as the trial balance from QB. Now if I can import the GL correctly, the last trial balance from QB should be identical to the last trial balance from SQL-Ledger. 4. QB has no export facility for the GL so I ran a custom transaction detail report. I choose the start date to be the day of the start trial balance and the end date to be the day of the last trial balance to get all the GL transactions. The report ended up with columns for trans#, date, num (check number), name, memo, account, and amount. I then printed this report to a file. 5. I then exported from SQL-Ledger the gl and acc_trans tables to files and read them into two spreadsheets. They will have your initial transactions for creating the trial balance in SQL-ledger. Now I have a spreadsheet of some transactions in the format of SQL-ledger and I need to get the QB into the same format. To make this happen I had to: 5a. Reformat the date to the way sql-ledger wanted it (year-month-day) 5b. change the signs on all entries (e.g. a QB entry that is -10 needs to be +10 for sql-ledger and vice versa). 5c. change the trans# in the QB spreadsheet to an id number for the SQL-Ledger. I did this by adding 10000 to the QB trans#. 5d. Map QB accounts to sql-ledger accounts. You have done this for the chart table. I wrote a simple function in the spreadsheet that did a lookup on a table and did this mapping for me. 5e. Break the single QB table into two tables to match the GL and acc_trans tables in SQL-Ledger. The only trick here is to be sure that you do not mix up the tran_ids (they have to be consistent between the gl and acc_trans tables). I sorted by trans_id and make sure that if it showed up in one table, it was in the other one. 6. I saved the revised gl and acc_trans spreadsheets out and imported them into the Sql-ledger using pgaccess. Before I did this I made a backup of the Sql-ledger so I would not loose the COA if I messed up. 7. VERY IMPORTANT. I updated the sequence id using pgacess to be higher than the highest id number that I imported. First time I did not do this and it really mucked things up as the id numbers were not unique. 8. I ran a trial balance in sql-ledger and verified that it matched perfectly with the QB trial balance. I checked a few transactions and verified they were the same in both systems. Once this checked out, made another backup of sql-ledger and I was off to the races. Caveats: Because I imported data directly into postgres and did not go through any of the code of SQL-ledger, it is very easy to not have the trans_ids match up. Even though I checked things out a lot in the spreadsheets, I still found about 3 transactions (out of over 1000) that were not quite correct (e.g. the gl and acc_trans table were a bit out of sync). These were very easy to fix using pgaccess. I would be happy to help anyone who needs it with this process. cheers, ski On Tue, 2002-10-15 at 16:54, Paul Kallstrom wrote: > [EMAIL PROTECTED] wrote: > > >Could you let us know how you get on with this, as something like this would be > >really good for converting people across to SL, if they know that transferring > >data won't be a point of contention. > > > >Quoting Wes Warner <[EMAIL PROTECTED]>: > > > > > > > Likewise, we are migrating to Ledger, but the only contention is the > data migration from quickbooks. > > > > ------------------------------------------------------- > This sf.net email is sponsored by: viaVerio will pay you up to > $1,000 for every account that you consolidate with us. > http://ad.doubleclick.net/clk;4749864;7604308;v? > http://www.viaverio.com/consolidator/osdn.cfm > ------------------------------------------------------- > (un)subscribe: http://lists.sourceforge.net/lists/listinfo/sql-ledger-users > Archive: http://www.mail-archive.com/[email protected]/ ------------------------------------------------------- This sf.net email is sponsored by: viaVerio will pay you up to $1,000 for every account that you consolidate with us. http://ad.doubleclick.net/clk;4749864;7604308;v? http://www.viaverio.com/consolidator/osdn.cfm ------------------------------------------------------- (un)subscribe: http://lists.sourceforge.net/lists/listinfo/sql-ledger-users Archive: http://www.mail-archive.com/[email protected]/
