Migrate data from one site to another

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 or Numbers instead.
  9. For step 4 below, Prepare Files to Send to RepairShopr, they do not work for Excel. These instructions are for Numbers on a Mac. We will add instructions for Excel in the future. For now you will need to send the files to us to perform those steps.

Steps to follow

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

Export Data

Use the URL below to trigger an export of customers, contacts, logistics line items, tickets, ticket comments, and assets all at once. Replace "yoursite" in the URL with your subdomain.
https://yoursite.repairshopr.com/reports/downloads?export=true

Export Invoices via Admin > Reports > Invoice Export, or with this URL.
https://yoursite.repairshopr.com/invoices_dump_csv

To export your inventory,

  1. Navigate 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 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://yoursite.repairshopr.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 without either will not get imported, so you’ll want to go through your customers to fix those without either.

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 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 RepairShopr

The below steps do not work for Excel. These instructions are for Numbers on a Mac. We will add instructions for Excel in the future. For now you will need to send the Ticket, Invoice and Asset files to us to perform those steps.

It is extremely important that you precisely follow each step. Missing any step could result in data getting corrupted or not getting imported.

Prepare Tickets
Prepare Ticket Comments
Prepare Invoices
Prepare Assets

Prepare Tickets

  1. Open the Customers spreadsheet.
  2. Copy the whole thing.
  3. Open the Tickets spreadsheet.
  4. 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.
  5. Add a new sheet. Default Label is Sheet 2. Do not rename this or else the formulas will not work.
  6. Paste the customer data you copied.
  7. Close the Customers spreadsheet. (Waiting to close until after you paste is faster due to memory issues.)
  8. Change cell I1 (the zip column) to customer_phone.
  9. Select column I except for cell I1.
  10. Copy and paste this formula (Edit > Paste and Match Style):
    =if(phone<>"",phone,(if(mobile_phone<>"",mobile_phone,(if(office_phone<>"",office_phone,(if(home_phone<>"",home_phone,(if(other_phone<>"",other_phone,fax_phone)))))))))
  11. Switch to Sheet 1 (the ticket data).
  12. Change cell G1 (the status column) to customer_email.
  13. Change cell H1 (the billing_status column) to customer_phone.
  14. Change cell J1 (the created_at column) to made_at.
  15. Change cell L1 (the due_date column) to created_at.
  16. Select column K (cancelled) and delete all the text.
  17. In cell K1, type body.
  18. Change cell Q1 (the employee column) to tech.
  19. Select column G except for cell G1.
  20. Copy and paste this formula (Edit > Paste and Match Style):
    =VLOOKUP(customer_id,Sheet 2::Table 1::A:D,4,FALSE)
  21. Select column H except for cell H1.
  22. Copy and paste this formula (Paste and Match Style):
    =VLOOKUP(customer_id,Sheet 2::Table 1::A:I,9,FALSE)
  23. Select column L (now created_at) except for cell L1.
  24. Copy and paste this formula (Paste and Match Style):
    =MID(made_at,6,2)&”/“&MID(made_at,9,2)&”/“&LEFT(made_at,4)
  25. 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 (perhaps "Imported ticket"). This field is required.
  26. If you want RepairShopr to generate new ticket numbers, delete all values in the number column (N). However, it will make troubleshooting more difficult if there are errors since it will not have ticket numbers to reference. Instead, we recommend numbering them starting at a number higher than your last ticket number.
  27. Click File > Export To > Excel...
  28. Open the Excel file.
  29. Select and copy columns G and H.
  30. Click Edit > Paste Formula Results.
  31. Select and copy columns K and L.
  32. Click Edit > Paste Formula Results.
  33. Change the created_at column (L) format to MM/DD/YYYY.
  34. Delete row 1.
  35. Sort by customer_email column (G), then delete all “0” values.
  36. For all rows with a customer_email, delete the text in customer_phone (H).
  37. Sort by customer_phone (H) and note those with 0, as they will not get imported.You may want to export these to deal with later.
  38. Sort by the number column (N) after doing those.
  39. Export to Excel again (you can overwrite the existing file).
  40. Send file to help@repairshopr.com for us to import.

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 spreadsheet.
  2. Sort by subject (column C).
  3. Delete all rows except for “Initial Issue” as the subject.
  4. Select the whole spreadsheet and copy it.
  5. Switch to the tickets spreadsheet.
  6. Add a new Sheet (this will be Sheet 4).
  7. Paste what you copied from the comments spreadsheet.
  8. Close the Comments spreadsheet. You do not need to save the changes.
  9. Switch to Sheet 1.
  10. Change the cancelled column (K) to body.
  11. Select column K except for cell K1.
  12. Copy and paste this formula (Paste and Match Style):
    =VLOOKUP(A,ticket_id:body,3,FALSE)
  13. Return to next step in ticket instructions.

Prepare Invoices

  1. Open the Customers spreadsheet.
  2. Copy the whole thing.
  3. Open the daily_invoices_report spreadsheet (Obtained from Reports > Invoice Export).
  4. 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.
  5. Add a new sheet. Default Label is Sheet 2. Do not rename this or else the formulas will not work.
  6. Paste the customer data you copied.
  7. Close the Customers spreadsheet. (Waiting to close until after you paste is faster due to memory issues.)
  8. Change cell I1 (the zip column) to customer_phone.
  9. Select column I except for cell I1.
  10. Copy and paste this formula (Edit > Paste and Match Style):
    =if(phone<>"",phone,(if(mobile_phone<>"",mobile_phone,(if(office_phone<>"",office_phone,(if(home_phone<>"",home_phone,(if(other_phone<>"",other_phone,fax_phone)))))))))
  11. Switch to Sheet 1 (the invoice data).
  12. Change cell F1 (the Tech column) to customer_email.
  13. Change cell G1 (the PaymentType column) to customer_phone.
  14. Change cell H1 (the Invoice Number column) to number.
  15. Change cell J1 (the Subtotal column) to subtotal. Note that it does not include tax. (May not need to do this)
  16. Select column L (took_payment) and delete all the text.
  17. In cell L1, type optional_line_item_name.
  18. Change the date column (N) format to MM/DD/YYYY.
  19. Select column F except for cell F1.
  20. Copy and paste this formula (Paste and Match Style):
    =VLOOKUP(Customer_ID,Sheet 2::Table 1::A:D,4,FALSE)
  21. Select column G except for cell G1
  22. Copy and paste this formula (Paste and Match Style):
    =VLOOKUP(Customer_ID,Sheet 2::Table 1::A:I,9,FALSE)
  23. If you want RepairShopr to generate new invoice numbers, delete all values in the number column (H). However, it will make troubleshooting more difficult if there are errors since it will not have invoice numbers to reference. Instead, we recommend numbering them starting at a number higher than your last invoice number.
  24. Optional: Put something into the optional_line_item_name field (perhaps "Imported invoice").
  25. Click File > Export To > Excel...
  26. Open the Excel file.
  27. Select and copy columns F and G.
  28. Click Edit > Paste Formula Results.
  29. Delete row 1.
  30. Sort by customer_email column, then delete all “0” values.
  31. For all rows with a customer_email, delete the customer_phone.
  32. Sort by customer_phone and note those with 0, as they will not get imported. You may want to export these to deal with later.
  33. Sort by the number column (H).
  34. Export to Excel again (you can overwrite the existing file).
  35. Send file to help@repairshopr.com for us to import.

Prepare Assets

  1. Open the Customers spreadsheet.
  2. Copy the whole thing.
  3. Open the Assets spreadsheet.
  4. Add a new sheet. Default Label is Sheet 2. Do not rename this or else the formulas will not work.
  5. Paste the customer data you copied.
  6. Close the Customers spreadsheet. (Waiting to close until after you paste is faster due to memory issues.)
  7. Change the zip column (I) to customer_phone.
  8. Select column I except for cell I1.
  9. Copy and paste this formula (Edit > Paste and Match Style):
    =if(phone<>"",phone,(if(mobile_phone<>"",mobile_phone,(if(office_phone<>"",office_phone,(if(home_phone<>"",home_phone,(if(other_phone<>"",other_phone,fax_phone)))))))))
  10. Switch to Sheet 1 (the asset data).
  11. Change cell K1 (the properties column) to raw_props.
  12. Change cell F1 (the contact_name column) to properties.
  13. Change cell H1 (the created_at column) to customer_email.
  14. Change cell I1 (the updated_at column) to customer_phone.
  15. Select column L (asset_type_id) and change the Cell data format to Text.
  16. Add a new column (M).
  17. Select column M and change the data format to Automatic.
  18. In cell M1, type asset_type_name.
  19. Select column F (properties) except for cell F1.
  20. Copy and paste this formula (Paste and Match Style):
    =SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(raw_props,"""",""), "=>",":"), "}", ""), "{", ""), ", ", ";")
  21. Select column H except for cell H1.
  22. Copy and paste this formula (Paste and Match Style):
    =VLOOKUP(Customer_ID,Sheet 2::Table 1::A:D,4,FALSE)
  23. Select column I except for cell I1.
  24. Copy and paste this formula (Paste and Match Style):
    =VLOOKUP(Customer_ID,Sheet 2::Table 1::A:I,9,FALSE)
  25. Do the following to convert asset type IDs to asset type names.
    1. Add a new sheet, which should be Sheet 3. Do not rename this or else the formulas will not work.
    2. Copy the below row of columns.
      asset_type_id asset_type_name asset_url
    3. Paste it in Sheet 3 (Paste and Match Style).
    4. In RepairShopr, navigate to Admin > Customers - Asset Custom Fields.
    5. Type all the items in the NAME column into the spreadsheet in the asset_type_name column.
    6. After typing all the names, if there are any empty rows, delete them.
    7. For each name on the site, right click the Manage Fields link > Copy Link.
    8. Paste the link in the asset_url field and repeat for all the names.
    9. Select column A (asset_type_id) except for cell A1.
    10. Copy and paste this formula in cell A2 (Paste and Match Style). This extracts the asset type ID from the URL.
      =TEXTBETWEEN(asset_url, "/", "/", 4,−1)
    11. Switch to Sheet 1.
    12. Select column M except for cell M1.
    13. Copy and paste this formula (Paste and Match Style):
      =VLOOKUP(asset_type_id,Sheet 3::Table 1::A:B,2,FALSE)
  26. Click File > Export To > Excel...
  27. Open the Excel file.
  28. Delete row 1.
  29. Select and copy column F.
  30. Click Edit > Paste Formula Results.
  31. Select and copy columns H and I.
  32. Click Edit > Paste Formula Results.
  33. Select and copy column M.
  34. Click Edit > Paste Formula Results.
  35. Sort by customer_email column, then delete all “0” values.
  36. For all rows with a customer_email, delete the customer_phone.
  37. Sort by customer_phone and note those with 0, as they will not get imported. You may want to export these to deal with later.
  38. Export to Excel again (you can overwrite the existing file).
  39. Send file to help@repairshopr.com for us to import.

Feedback and Knowledge Base