Migrate data from one site to another using LibreOffice

Knowledge Base Misc
1

This article covers exporting data—customers, inventory (AKA products & services), tickets, invoices and assets—from one site and importing it into another. Both the source and destination sites can be RepairShopr or Syncro. A few things to note.

  1. For customers and inventory, you have the ability to import them yourself. All data is retained in the transfer.
  2. For tickets, invoices and assets, we will need to import them. You will need to prepare the data before sending it to us according to the below instructions. Only some of the columns in the data are able to be imported.
  3. No other data can be imported, such as purchase orders or estimates.
  4. All tickets are imported as resolved, regardless of their status on the source site.
  5. All invoices are imported as paid by credit card (don't worry—it doesn't actually charge the credit cards again), regardless of their payment status on the source site. If you need to indicate some are not paid, you will need to go to the Payments screen (Invoices > Invoice Modules > Payments) and delete the appropriate payments.
  6. The Daily Payments report will reflect the total financial amount of invoice payments imported. For example, one site imported over 4,000 invoices for a total of over $972,000.
  7. If you have extremely large customer and inventory files, break down the import files into no more than 5,000 rows per file, and then import them one at a time. Using a program such as this free CSV file splitter can help (use at your own risk).
  8. We do not recommend using Google Sheets, as it will bog down your computer. Use a program like Excel, LibreOffice or Numbers instead.
  9. For step 4 below, Prepare Files to Send to Syncro, we have not tested them in Excel. These instructions are for LibreOffice, a free, cross platform, open source app.

Steps to follow

  1. Export Data
  2. Clean Up Data
  3. Import Data
  4. Prepare Files to Send to Syncro

Export Data

Use the URL below to trigger an export of customers, contacts, logistics line items, tickets, ticket comments, and assets all at once.
https://yoursubdomain.syncromsp.com/reports/downloads?export=true

Export Invoices via More > Admin > Reports > Invoices - Invoice Export, or with this URL.
https://yoursubdomain.syncromsp.com/invoices_dump_csv

To export your inventory,

  1. Go to the Inventory tab.
  2. In the upper right click Inventory Modules > Export to CSV.

After starting a download, you will then be redirected to the Downloads page (located in More > Admin > Reports > Downloads). If a file is large, it may take a few minutes and require you to manually refresh the page in order to see it.
https://yoursubdomain.syncromsp.com/reports/downloads

Clean Up Data

In order to import customers, tickets and invoices into your new site, customers MUST have either an email or phone number. Those where both the email and phone are missing will not get imported, so you’ll want to go through your customers to fix those like that by filling in at least their email or phone.

Each customer must also have unique phone numbers and email addresses. If three customers all have the same phone number, each will overwrite the previous customer. So the 2nd will overwrite the 1st, and then the 3rd will overwrite the 2nd, with the end result being that only the 3rd customer shows up.

If certain inventory is not at your new site, edit the inventory file to delete rows with unneeded items.

Import Data

To import your customers into your new site,

  1. Navigate to More > Admin > Customers - Customer Import.
  2. Under step 4 on the page, click Choose File.
  3. Navigate to the Customers file you downloaded and double click it.
  4. On the page, click Import.

To import your inventory into your new site,

  1. Navigate to the Inventory tab.
  2. In the upper right click Inventory Modules > Import.
  3. Click Choose File.
  4. Navigate to the Inventory file you downloaded and double click it.

Prepare Files to Send to Syncro

The below steps may not work in Excel. These instructions are for LibreOffice, a free, cross platform, open source app. We encourage you to download and use it for these steps.

It is extremely important that you precisely follow each step. Missing any step could result in data getting corrupted or not getting imported. If we discover any problems with the files, we may have you correct them and resend them to us.

Prepare Customers
Prepare Tickets
Prepare Ticket Comments
Prepare Invoices
Prepare Assets

Once you have the Excel file(s) ready, send them to help@syncromsp.com for us to import.

Prepare Customers

These steps get the Customers file ready to be pasted into the Tickets, Invoices and Assets files.

  1. Open the Customers CSV spreadsheet.
  2. In the Text Import dialog, make sure only the Comma checkbox is turned on, then click OK. This will be true for the other imports as well.

     
  3. Change cell I1 (the zip column) to customer_phone.
  4. Copy this formula:
    =IF(J2<>"",J2,(IF(K2<>"",K2,(IF(L2<>"",L2,(IF(M2<>"",M2,(IF(O2<>"",O2,N2)))))))))
  5. Click in cell I2, customer_phone column.
  6. Paste as unformatted text (Edit > Paste Special > Paste Unformatted Text).
  7. Copy the current cell (I2).
  8. Select cells I3 down to the end of the column data.
  9. Paste the formula. You should now see phone numbers in every cell except when all the phone fields are blank.
  10. Click File > Save As...
  11. Rename it to Customers if desired.

     
  12. For File type, select Excel 2007-365 (.xlsx).
  13. Click Save.

We will refer to this as the Customers Excel spreadsheet.

Prepare Tickets

  1. Open or switch to the Customers Excel spreadsheet.
  2. Copy the whole thing.
  3. Open the Tickets CSV spreadsheet.
  4. Add a new sheet (this will be Sheet 2). Do not rename this or else the formulas will not work.
  5. Paste the customer data you copied.
  6. You may close the Customers spreadsheet, if desired. (Waiting to close until after you paste is faster due to memory issues.)
  7. Switch to Sheet 1 (the ticket data).
  8. If you are only importing certain locations, now is the time to sort by location (column O) and delete rows with unneeded locations. Same for any other criteria.
  9. Change cell G1 (the status column) to customer_email.
  10. Change cell H1 (the billing_status column) to customer_phone.
  11. Change cell J1 (the created_at column) to made_at.
  12. Change cell L1 (the due_date column) to created_at.
  13. Select column K (cancelled) and delete all the text.
  14. In the Delete Contents dialog, turn on Delete all and click OK.

     
  15. In cell K1, type body. Make sure it doesn't change to Body; it needs to be all lower case or else it will not work.
  16. Change cell Q1 (the employee column) to tech. It also needs to be all lowercase.
  17. Copy this formula:
    =VLOOKUP(B2,$Sheet2.A:D,4,0)
  18. Click in cell G2, customer_email column.
  19. Paste as unformatted text (Edit > Paste Special > Paste Unformatted Text).
  20. Copy the current cell (G2).
  21. Select cells G3 down to the end of the column data.
  22. Paste the formula. If the spreadsheet is extremely large, it may take awhile for all the cells to fill in with the results.
  23. Now repeat steps 18-22 but with these formulas and columns.
  24. H2, customer_phone column.
    =VLOOKUP(B2,$Sheet2.A:I,9,0)
  25. L2, created_at column.
    =MID(J2,6,2)&"/"&MID(J2,9,2)&"/"&LEFT(J2,4)
  26. Click File > Save As...
  27. Rename it to Tickets if desired.
  28. For File type, select Excel 2007-365 (.xlsx).
  29. Click Save.
  30. If you want to put ticket comments in the body column (K), see Ticket Comments instructions below. Otherwise, put something into every cell of the body column. This field is required.
  31. If you want Syncro to generate new ticket numbers, delete all values in the number column (N). However, it will make troubleshooting almost impossible if there are errors since it will not have ticket numbers to reference.
  32. Sort by customer_email (Sort Key 1) AND customer_phone (Sort Key 2) and note those where both are blank, as they will not get imported. Either delete the rows, or add the missing emails/phones in both the spreadsheet and the customer accounts on your site.
  33. Sort by the number column (N) after doing those.
  34. You will be replacing all the formulas with their results, so you may also want to save a copy of the file (File > Save a Copy) in case you make a mistake and need to go back to the version with the formulas.
  35. Select and copy column G, customer_email.
  36. Paste as only text (Edit > Paste Special > Paste Only Text).
  37. Select and copy column H, customer_phone.
  38. Paste as unformatted text (Edit > Paste Special > Paste UnformattedText).
  39. Select and copy columns K and L, body and created_at.
  40. Paste as only text (Edit > Paste Special > Paste Only Text).
  41. Select the created_at column (L) except for cell L1.
  42. Change the format to MM/DD/YYYY (Format > Cells..., Category: Date, Format: 12/31/1999).
  43. Delete all sheets except sheet 1.
  44. Save the file.

Prepare Ticket Comments

This shows how to add the initial issue comments. Getting all the comments would involve writing scripts to combine the separate comment rows into once giant comment per ticket.

  1. Open the Comments CSV spreadsheet.
  2. In the last column, in cell I1, type sano.
  3. Sort by subject (column C).
  4. Delete all rows except for “Initial Issue” as the subject.
  5. Select the whole spreadsheet and copy it.
  6. Switch to the Tickets spreadsheet.
  7. Add a new Sheet (this will be Sheet 3). Do not rename this or else the formulas will not work.
  8. Paste what you copied from the Comments spreadsheet.
  9. Close the Comments spreadsheet. You do not need to save the changes.
  10. Switch to Sheet 1.
  11. Copy this formula:
    =VLOOKUP(A2,Sheet3.B:D,3,0)
  12. Click in cell K2, body column.
  13. Paste as unformatted text (Edit > Paste Special > Paste Unformatted Text).
  14. Copy the current cell (K2).
  15. Select cells K3 down to the end of the column data.
  16. Paste the formula. If the spreadsheet is extremely large, it may take awhile for all the cells to fill in with the results.
  17. Return to next step in ticket instructions.

Prepare Invoices

Important: You must use the daily_invoices_report spreadsheet (obtained from Reports > Invoices - Invoice Export) for this. It will not work with the one with a filename such as 7678_2022_05_03_05_01_57__0000_invoices.
  1. Open or switch to the Customers Excel spreadsheet.
  2. Copy the whole thing.
  3. Open the daily_invoices_report CSV spreadsheet.
  4. Add a new sheet (this will be Sheet 2). Do not rename this or else the formulas will not work.
  5. Paste the customer data you copied.
  6. You may close the Customers spreadsheet, if desired. (Waiting to close until after you paste is faster due to memory issues.)
  7. Switch to Sheet 1 (the invoice data).
  8. If you are only importing certain locations, now is the time to sort by Location (column P) and delete unneeded locations. Same for any other criteria.
  9. Change cell F1 (the Tech column) to customer_email.
  10. Change cell G1 (the PaymentType column) to customer_phone.
  11. Change cell H1 (the Invoice Number column) to number.
  12. Change cell J1 (the Subtotal column) to subtotal. Note that it does not include tax. Yes, it needs to be changed to all lowercase or else none of the spreadsheet will import.
  13. Select column L (took_payment) and delete all the text.
  14. In the Delete Contents dialog, turn on Delete all and click OK.

     
  15. Change cell L1 to optional_line_item_name.
  16. Change the date column (N) format to 12/31/1999 (Format > Cells..., Category: Date, Format: 12/31/1999). You may want to widen the column so you can see the dates.
  17. Copy this formula:
    =VLOOKUP(B2,$Sheet2.A:D,4,0)
  18. Click in cell F2 (customer_email).
  19. Paste as unformatted text (Edit > Paste Special > Paste Unformatted Text).
  20. Copy the current cell (F2).
  21. Select cells F3 down to the end of the column data.
  22. Paste. You should now see emails in every cell except when the email field is blank.
  23. Now repeat steps 18-22 but with this formula and column.
  24. G2, customer_phone column
    =VLOOKUP(B2,$Sheet2.A:I,9,0)
  25. If you want Syncro to generate new invoice numbers, delete all values in the number column (H). However, it will make troubleshooting almost impossible if there are errors since it will not have invoice numbers to reference.
  26. Optional: Put something into every optional_line_item_name field.
  27. Sort by customer_email (Sort Key 1) AND customer_phone (Sort Key 2) and note those where both are blank, as they will not get imported. Either delete the rows or add the missing emails/phones in both the spreadsheet and the customer accounts on your site.
  28. Sort by the number column (H).
  29. Click File > Save As...
  30. Rename it to Invoices if desired.
  31. For File type, select Excel 2007-365 (.xlsx).
  32. Click Save.
  33. You will be replacing all the formulas with their results, so you may also want to save a copy of the file (File > Save a Copy) in case you make a mistake and need to go back to the version with the formulas.
  34. Select and copy column F, customer_email.
  35. Paste as only text (Edit > Paste Special > Paste Only Text).
  36. Select and copy column G, customer_phone.
  37. Paste as unformatted text (Edit > Paste Special > Paste UnformattedText).
  38. Delete all sheets except sheet 1.
  39. Save the file.

Prepare Assets

  1. Open or switch to the Customers Excel spreadsheet.
  2. Copy the whole thing.
  3. Open the Assets CSV spreadsheet.
  4. Add a new sheet (this will be Sheet 2). Do not rename this or else the formulas will not work.
  5. Paste the customer data you copied.
  6. You may close the Customers spreadsheet, if desired. (Waiting to close until after you paste is faster due to memory issues.)
  7. Switch to Sheet 1 (the asset data).
  8. Change cell F1 (the contact_name column) to properties.
  9. Change cell H1 (the created_at column) to customer_email.
  10. Change cell I1 (the updated_at column) to customer_phone.
  11. Change cell K1 (the properties column) to raw_props. You may need to widen the column to see it.
  12. Select column L (asset_type_id) and change the Cell data format to Text (Format > Cells..., Category: Text).
  13. Select column M and change the data format to Automatic (Format > Cells..., Category: All).
  14. Change cell M1 to asset_type_name.
  15. Copy this formula:
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(K2,"""",""), "=>",":"), "}", ""), "{", ""), ", ", ";")
  16. Click in cell F2, properties column.
  17. Paste as unformatted text (Edit > Paste Special > Paste Unformatted Text).
  18. Copy the current cell (F2).
  19. Select cells F3 down to the end of the column data.
  20. Paste the formula.
  21. Now repeat those steps 16-20 but with these formulas and columns.
  22. H2, customer_email column.
    =VLOOKUP(B2,$Sheet2.A:D,4,0)
  23. I2, customer_phone column.
    =VLOOKUP(B2,$Sheet2.A:I,9,0)
  24. Do the following to convert asset type IDs to asset type names.
    1. Add a new sheet (this will be Sheet 3).
    2. Copy the below row of columns.
      asset_type_id asset_type_name asset_url
    3. Paste as unformatted text in Sheet 3 (Edit > Paste Special > Paste Unformatted Text).
    4. In Syncro, go to More > Admin > Customers - Asset Custom Fields.
    5. Type all the items in the NAME column into the spreadsheet in the asset_type_name (B) column.
    6. For each name on the site, right click the Manage Fields link > Copy Link.
    7. Paste the link in the asset_url (C) field and repeat for all the names.
    8. Copy this formula:
      =NUMBERVALUE(MID(C2,FIND("s/",C2)+2,LEN(C2)-FIND("s/",C2)-14))
    9. Click in cell A2, asset_type_id column.
    10. Paste the formula as unformatted text (Edit > Paste Special > Paste Unformatted Text). This extracts the asset type ID from the URL.
    11. Copy the current cell (A2).
    12. Select cells A3 down to the end of the column data.
    13. Paste the formula.
    14. Switch to Sheet 1.
    15. Copy this formula:
      =VLOOKUP(L2,$Sheet3.A:B,2,0)
    16. Click in cell M2, asset_type_name column.
    17. Paste as unformatted text (Edit > Paste Special > Paste Unformatted Text).
    18. Copy the current cell (M2).
    19. Select cells M3 down to the end of the column data.
    20. Paste the formula.
  25. Sort by customer_email (Sort Key 1) AND customer_phone (Sort Key 2) and note those where both are blank, as they will not get imported. Either delete the rows or add the missing emails/phones in both the spreadsheet and the customer accounts on your site.
  26. Click File > Save As...
  27. Rename it to Assets if desired.
  28. For File type, select Excel 2007-365 (.xlsx).
  29. Click Save.
  30. You will be replacing all the formulas with their results, so you may also want to save a copy of the file (File > Save a Copy) in case you make a mistake and need to go back to the version with the formulas.
  31. Select and copy column F, properties.
  32. Paste as unformatted text (Edit > Paste Special > Paste UnformattedText).
  33. Select and copy column H, customer_email.
  34. Paste as only text (Edit > Paste Special > Paste Only Text).
  35. Select and copy column I, customer_phone.
  36. Paste as unformatted text (Edit > Paste Special > Paste UnformattedText).
  37. Select and copy column M, asset_type_name.
  38. Paste as unformatted text (Edit > Paste Special > Paste UnformattedText).
  39. Delete all sheets except sheet 1.
  40. Save the file.