Import Everlance Mileage to Quickbooks Self Employed

Importing Everlance Milage

You can import Everlance mileage, and presumably mileage from other tracking apps, into Quickbooks Self Employed with a little effort, as Quickbooks Self Employed supports MileIQ csv formatted data.

To start off, you could create a free MileIQ account, and manually add in a couple fake trips to export as per the tutorial on Quickbooks Self Employed importer for MileIQ. Another way is to use the CSV file template below, and modify it as I’ll instruct you.

Part 1: Gather the Data

1. If you choose to use this template, just copy and paste this data into a text editor, such as Xed on Linux, Notepad on Windows, or whatever text editor Mac users have access to. DO NOT use Word, Google Docs, or another fancy editor, or it will corrupt the data! Use only a very basic text editor that doesn’t support text formatting. This tutorial will focus on how to use the template below.
   

   ---

   START_DATE*,END_DATE*,CATEGORY*,START*,STOP*,MILES*,MILES_VALUE,PARKING,TOLLS,TOTAL,VEHICLE,PURPOSE,NOTES
   07/21/2017 16:49,07/21/2017 17:02,Personal,”8199 Pearl Road, Strongsville, Ohio”,”16224 Pearl Road, Strongsville, Ohio”,2.1,1.12,0,0,1.12,Wildflower,Personal,

   ---

2. Now save the template as a csv file, and import it to Google Sheets. Open it as a Google Sheets document.
3. In Everlance, take the free trial or buy a premium account so it will let you export your data. Export only the trip data, and optionally tell it to export only work-related trips as that’s what QuickBooks Self Employed will import. (It scraps the trips not marked as “Business” trips.)
4. Grab the csv file from your email that Everlance sent you, and open it in Google Sheets.

Part 2: Merge the Files

There are a few things to note before we migrate the CSV content from Everlance to the MileIQ format.

1. If you don’t keep all of the columns, including the “NOTES” column, Quickbooks won’t recognize it as a MileIQ csv file. The headers are key to making it recognize the file as the correct format. (“Uh-oh, looks like we ran into some trouble importing this file. File could not be parsed as MileIq details file. Correct it and try again.“)
2. If you exported your MileIQ dummy data, you can crop off the totals and other junk at the top and bottom of the CSV file. Make it look like the template above. The rest isn’t used by Quickbooks anyway.
3. Lots of people have opened tickets with Intuit asking about the, “Uh-oh, looks like we ran into some trouble importing this file. Could not parse csv row. Correct it and try again.” This issue is because the row data is in the correct format. When I was getting that, it was due to the date and time not being formatted correctly. In short, you MUST use the “07/21/2017 16:49” format. It’s in 24-hour time, and the date is MM/DD/YYYY. You must have the preceding 0s in the date, and they must be separated by a “/” with the month coming firs, then the day, and then the year. The time must have preceeding 0s, be separated by a “:” without having the seconds. The date and time as separated by a single space.
4. As Quickbooks Self Employed doesn’t import “Personal” trips, you can test your formatting work by marking a trip as “Personal” instead of as “Business” so that the importer will verify that the file is importable, yet it won’t add bogus trips to your account.

Which Column Does What

1. “START_DATE” – The starting time and date for your trip. Everlance uses a single date for all trips, and two time columns for the start and stop times.
   1. To quickly format this column, use Google Sheets to format the dates correctly, and format the time columns correctly, as stated above.
   2. Put the date start time columns next to each other in Google Sheets.
   3. Next, copy the rows from Google Sheets into a text editor.
   4. In the text editor, the columns will be separated by a tab. Copy the tab character to your find and replace feature. Replace all tabs with a space.
   5. Now copy all data from your text editor into the “START_DATE” column in your MileIQ sheet.
2. “END_DATE” – This column works just like “START_DATE” and instead you just use your ending time.
3. “CATEGORY” – This column needs to say “Business” for any trips marked as business. use the text editor trick from above to quickly replace all “Work” and other business-related trips to say “Business.”
4. “START” and “STOP” – These are your starting address and ending address. You can use any address format for them. Be sure to edit these fields in Google Sheets so you don’t accidentally botch the formatting where it relates to the CSV format.
5. “MILES” – This column shows how many miles you drove. It must be a number.
6. “MILES_VALUE” – This column shows how many dollars credit the IRS will give you for the miles. It must be a number, and must not include the “$” or other text. Use Google Sheets to format them as a number, not as currency.
7. “PARKING” – This column shows the cost you paid to park. In my case, it was all 0s even though I did pay for parking a couple times. Quickbooks Self employed doesn’t take that into account.
8. “TOLLS” – As with “PARKING,” it can be all 0s, and Quickbooks ignores it.
9. “TOTAL” – This column is the total of “MILES_VALUE” + “PARKING” + “TOLLS.”
10. “VEHICLE” – Put what you want here. Quickbooks Self employed only imports this data into your existing car, so the name of the car is unimportant. (I only have one car, so you may get more options on the importer if you have multiple cars.)
11. “PURPOSE” – This column needs to match “CATEGORY” exactly.
12. “NOTES” – Just leave it blank, as Quickbooks doesn’t use it.

Part 3: Importing the CSV

Now that you’ve finished creating the sheet, export it as a CSV file, and upload it to Quickbooks Self Employed. You’ll see that Quickbooks will import your business trips. Now you can go through the list of trips and click each one to add your addresses. Just copy the starting address from the note into the “address” box you’ll see when you click the link to add a starting address. Do the same for the end address.

Conclusion

Quickbooks Self employed REALLY needs a smarter way to import data from other tracking apps so we don’t need to manually do it. However, if you want to keep everything in the same place, you can use this method to migrate your data by exploiting the MileIQ feature. As an engineer, I know it’s not too terribly difficult to build a unified way to import the data.