If you need to sync SingleOps client data to QuickBooks Online, here's how:
his article is written in the context of client data, but the methods outlined can be used for any data type for which QuickBooks Online (QBO) allows.
If a customer wishes to export SingleOps data for import directly into QuickBooks Online (QBO) for the purposes of populating a new data set, updating an existing data set, or resetting data which has become corrupted or fallen out of sync, you can use the data import functionality built in to QBO to import your data.
Exporting the SingleOps Data
You will need to first export the data to be transferred from SingleOps. This can be via a CSV export from a report, or you can run a SQL query and export the data to either a CSV file or an Excel spreadsheet. You will need to ensure, to the extent possible, that the data being exported from SingleOps closely matches the data expected by QBO; else you will find that your data records will have gaps or holes. Know which data QBO expects... and what data SingleOps offers to populate QBO's needs.
For this article, we are going to use SingleOps' Client Export report to generate the data for a customer data export/import into QBO. This report matches about 75% of the expected fields in QBO's customer import.
If using a SingleOps report, the data can be exported by the customer. If generated via a SQL query, a SingleOps employee will need to supply the customer with the CSV or Excel file. IMPORTANT: QBO will expect any date fields to be in the YYYY-MM-DD format. That does not mean just 'year, month, day' order: it literally must contain the dashes as the separators. As many SQL queries may return the date as YYYY/MM/DD, you will need to convert this to use dashes instead of slashes. You can do this in Excel by using a custom format and explicitly specifying 'yyyy-mm-dd' or you can construct your SQL query to format the return of data to use the required format.
Importing the Data in QBO
• Log into QBO using an admin-level authentication.
• Click the gear icon upper-right and select Tools >> Import Data.
• Select the type of data to be imported. For this article, we'll select Customers
• Browse to the location on your computer of the exported CSV or Excel file. NOTE: when creating this test import, a sandbox environment was used. When attempting to load an Excel file will 2300+ records, QBO gave a message stating it could not load the file since it contained more than 1,000 records. It is not known if this was a limitation of the sandbox, but if this also holds true of production QBO environments, your import data will need to be broken into smaller subsets to meet the size limitation.
• Click Next.
• QuickBooks will give you a list of its customer fields, and ask you to select the fields in SingleOps which most closely match the QBO field. Those fields which it can determine are a good match will be auto-selected - but you can still change the value if/as needed. Below are the list of customer fields which I added or changed:
- for QBO Street, I selected SO's bill_addr_1
- for QBO ZIP, I selected SO's bill_addr_postal_code
- for QBO Name, I changed from SO's First Name, Last Name to Display Name
- for QBO Opening Balance Date, I changed from SO's Date Created to Date Updated
• Click Next.
• On this page, QBO will present a grid of the data and ask that you validate the data to match QBO's import restrictions. You will not be able to import the data until all fields match the validation requirements (the Import button lower-right will be greyed out). Here are a few of the validation requirements of which you'll want to be aware:
- as afore-mentioned, the date field(s) must be in the format 'yyyy-mm-dd'
- if you concatenate e-mails, they must be comma-delimited (not semi-colon)
- phone fields cannot exceed 21 characters
Once you've cleaned all the data to QBO's satisfaction, the Import button will light up and you'll be able to import the data into QBO.