Exporting Accounts Data from QuickBooks

If you are migrating from QuickBooks to Brightpearl you will want to move across all your existing data. To help make this process as simple as possible we have developed a program that will export the relevant data into a format which, after a few minor adjustments, can then be imported straight into Brightpearl.

Our QBExport program will export the following data:

• Customers
• Suppliers/Vendors
• Products
• Trial balance
• Accounts receivable
• Accounts payable

You will need to carry out the following steps:

1. Contact us for a copy of the export program
2. Save, extract and run the program
3. Start QuickBooks and export the data
4. Check the exported data and make the necessary adjustments
5. Import the data to Brightpearl

1. Get a copy of the export program

To get your copy of the QuickBooks export program call us on:

0845 003 8935

We will email the ZIP file to you.

2. Save, extract and setup the QBExport Program

When you receive the QBExport file via email, follow these steps to save, extract and setup the program:

Create a new folder

Find a location somewhere on your computer where you can save all the files associated with the QBExport program so you know exactly where you can find them. You might want to call it "QBExport" or another suitable name so you know what the folder contains.

1. Open the location where you want to add the new folder.
2. Right-click your mouse.
3. Select New > Folder.
4. Name your folder appropriately.

Extract the files

We will send you the program as a ZIP file. This means it's compressed into a smaller file size to make it easier for you to receive and download, but before you can use it you'll need unzip it.

1. Open the email from us containing the QBExport ZIP file.
2. Download the file to your computer; doing this will be different depending on your email account.
3. Save the program in your new QBExport file created above.
4. Open your QBExport folder ready to unzip the files.
5. Unzipping the files will depend on what software you have installed, but often you will need to right-click the folder and click Extract All.
6. Confirm the location the files will be extracted to and click Extract.

The files should now be displayed in the folder.

Setup the QBExport program

You will need the following information:

Note: If the setup section fails, you may need to install C++ Run-time Library. Read more

1. Open your QBExport folder into which you have just extracted the zipped files.
2. Double click the file named QBExport.exe to start the program.
3. An empty window is displayed. On the menu bar, go to Edit > QB File Options.
4. The Batch Execution Options window is displayed. Make the following entries:
   • QuickBooks Company Name - Enter your QuickBooks company in this field (see above).
   • Output Data File Name Prefix - If you want to export the files with a specific prefix, enter it here. This will just help you identify different versions you export if you have more than one company.
   • QuickBooks Company File with full path, to be used in Batch mode - Click the Browse button and select the where the QuickBooks file location (see above).
   • If you have more than one company, use the Options for Company #2 and #3 to enter the details, otherwise leave them blank.



5. Click Save and then Exit.
6. Now go to Edit > Output File Options.
7. In the Output File Options window make the following entries:
   
   • Data separator - Select Comma.
   • Output File Directory - Click Browser and select your QBExport folder where you extracted the ZIP file to earlier.
   • Output Files Names for Data Export - These will be automatically populated. They are the names that will be given to each file exported. The date and time will also be automatically added to the file name.



8. Click Save and then Exit.
9. The empty QBExport program window is displayed, leave this open, you will need it in a minute.

All the exported data files will be deposited into the "Output File Directory" you defined in the steps above. The file name will be something like "Customer-YYYY-MM-DD-hhmmss.csv". This naming convention will help to precisely identify the files created if the program is run multiple times; all the files will co-exist in the same Output File Directory.

You are now ready to export the data from QuickBooks!

3. Start QuickBooks and export the data

Note: If this export fails, you may need to install C++ Run-time Library. Read more

1. Start QuickBooks and load the company for which you wish to extract the data.
2. You should still have the empty QBExport program window open, go to the ExportData menu and select Export data from the Company file currently open in QuickBooks.
3. The QBExport: Select Data Types for Export window is displayed. Make the following entries:
   • Data Separator - Select Pipe.
   • Data Selection - Select All Data to export all your data. It is possible to limit the exported data for a particular date range, but you should probably leave this to your qualified accountant.
   • Data Type Selection - Tick the checkbox next to each type of data to export, e.g. Customer, Vendor, Accounts Receivable... You'll want them all!



4. Click the Next button.
5. The program will begin exporting your data. You can watch its progress as the number of files increases. Note: You may need to grant our program access, read more
6. Once it is complete, the Exit button will become available. Click it to finish and exit.

Problems you may encounter:

• You may need to install Visual C++ Run-time Library before running the QBExport program:

Click here to download Visual C++ Run-time Library

• When you run the export process QuickBooks may require you to grant our QBExport program access. If a warning of an application with no certificate is given, select Yes, always; allow access even if QuickBooks is not running, and then click Yes.

4. Check the exported data and make the necessary adjustments

All the data exported will be saved into your QBExport folder you created; as long as you specified this location during the setup - "Output File Directory".

Open this folder to see a bunch of CSV type Excel files. You will need to reformat each of these files slightly so Brightpearl can read them:

1. Open the file in Excel. All the data is bunched up into Column A, we need to separate this out.
2. Left-click the column header to highlight the whole column of data.
3. Go to Data > Data Tools > Text to Columns.
4. Ensure Delimited is selected and click Next.
5. Tick the checkbox for Semicolon and click Finish.
6. Click Save.
7. Repeat this for each file you have exported.

You data should now be split out into all the correct columns.

Make necessary adjustments to the data

When data is imported into Brightpearl it needs to be formatted in a way which the system can read. This means the files from QuickBooks need to be adjusted, just a little, as they are designed to be formatted slightly differently. Here are the changes you will need to make to each file:

Accounts

This file is your trial balance, listing all of your nominal codes in QuickBooks with their balance. Locate each of the following nominal code names (in column B) and ensure the account code (in column A) is as indicated in the table:

Save the file if you have made any changes.

Accounts Receivable

This file lists out all of your outstanding accounts receivable (debtor) transactions. Each transaction has a code, in column E, to indicate what type of entry it is, such as SI for Sales Invoice. Most of these are the same as in Brightpearl, but you will need to change any occurences of SA to SR. The quickest way to do this is with Find and Replace:

1. Open the file in Excel.
2. Press CTRL+F on your keyboard to open the Find window.
3. Select the Replace tab.
4. Make the entries as displayed in this screen image:



5. Click the Replace All button.

You will also need to reformat the dates in the file; column F and J:

1. Left-click the column header F to highlight the whole column.
2. Hold down the CTRL key and left-click the column header J to also highlight this column.
3. Right-click anywhere on the highlighted areas.
4. Select Format Cells.
5. Select Date in the category list.
6. Select the date format 2001-03-14, i.e. YYYY-MM-DD.



7. Click OK to apply and close.
8. Save the file.

Account Payable

This file lists all your outstanding accounts payable (creditor) transactions. You will need to make the same changes to this file as you did to the Accounts Receivable file (above), but this time finding and replacing any occurence of "PA" with "PP". Remember to reformat the dates too!

Customer

This file lists all your QuickBook customers.

1. Open the file in Excel.
2. Delete column K; right-click the column header K and select Delete.
3. Save the file as an .xls file; do Save as and say Save as type: Excel 97-2003 Workbook (*.xls).

This is a temporary issue!

Vendor

This file lists all your QuickBook vendors.

1. Open the file in Excel.
2. Delete column K; right-click the column header K and select Delete.
3. Save the file as an .xls file; do Save as and say Save as type: Excel 97-2003 Workbook (*.xls).

This is a temporary issue!

Product

This file lists all your QuickBooks products.

1. Open the file in Excel.
2. Save the file as an .xls file; do Save as and say Save as type: Excel 97-2003 Workbook (*.xls).

This is a temporary issue!

5. Import the data to Brightpearl

We recommend importing the files in the following order:

1. Customers
2. Vendors
3. Accounts
4. Accounts Receivable
5. Accounts Payable
6. Products

Import Customers and Vendors

1. Log in to Brightpearl.
2. Go to Setup > Data/Import.
3. Select Customers/Vendors.

You will need to create a Data Map to read your file. A Data Map tells the system what columns are in the file and in what order. Create a new data map to match each of your files.

Learn more about importing contacts

Import Accounts

1. Log in to Brightpearl.
2. Go to Setup > Data/Import.
3. Select Accounts.

First import your Accounts file (trial balance), then import your Accounts Receivable (Debtors) and Payable (Creditors).

Learn more about importing accounts

Importing Products

1. Log in to Brightpearl.
2. Go to Setup > Data/Import.
3. Select Products.

You will need to create a Data Map to read your file. A Data Map tells the system what columns are in the file and in what order. Create a new data map to match your product file.

Learn more about importing products