search

hargata / lubelog

LubeLogger is a web-based vehicle maintenance and fuel mileage tracker
https://lubelogger.com
MIT License
1.12k stars 57 forks source link

Add CSV Imports from Fuelly and SpiritMonitor #2

Closed hargata closed 9 months ago

JohnnyPicnic commented 10 months ago

fuelups.csv Here is some sample data.

car_name, model, l/100km, odometer, km, litres, price, city_percentage, fuelup_date, date_added, tags, notes, missed_fuelup, partial_fuelup, latitude, longitude, brand "My SX4","Suzuki SX4",9.85,222998.0,315.995,31.139,1.139,50,2024-01-03,2024-01-03 23:53:49,"","",0,0,,,"" "My SX4","Suzuki SX4",10.4,222682.0,308.994,32.127,1.099,50,2023-12-20,2023-12-20 22:55:05,"","",0,0,,,"" "My SX4","Suzuki SX4",10.25,222373.0,311.006,31.873,1.189,50,2023-12-13,2023-12-13 12:56:55,"","",0,0,,,""

hargata commented 10 months ago

@JohnnyPicnic is this for fuelly? and do the units(liters, kilometers) change if the user is using imperial units?

watling777 commented 9 months ago

Here is the Fuelly export for my old car with imperial units Fuelly Export.csv

hargata commented 9 months ago

@watling777 thanks, I noticed that the CSV you attached has a total column but the other csv attached above does not, are fuelly exports not standardized in terms of columns?

watling777 commented 9 months ago

@hargata Looks like the columns differ based on where the export is from. Mine was from the iOS app. These are the columns when downloaded from fuelly.com: car_name | model | mpg | odometer | miles | gallons | price | city_percentage | fuelup_date | date_added | tags | notes | missed_fuelup | partial_fuelup | latitude | longitude | brand

hargata commented 9 months ago

@watling777 Can you pull the latest image and test the imports from Fuelly? Right now I have it set up where the CSV records can only come from one vehicle, but I've tested both CSVs and they seem to be importing alright.

MatStace commented 9 months ago

I'm in the UK, so my units are a total mess, because we buy in litres, but still measure consumption in miles per gallon. Here's the header row from a fuelly.com export set to UK units: car_name, model, uk mpg, odometer, miles, litres, price, city_percentage, fuelup_date, date_added, tags, notes, missed_fuelup, partial_fuelup, latitude, longitude, brand (and yes, it really does have a space in uk mpg instead of an underscore 🤦🏻)

hargata commented 9 months ago

@MatStace I'm trying to figure out a good way to implement this. If you buy fuel in litres but measure in MPG, do you perform a conversion from litres to gallons before dividing the miles? I can make the import work to an extent by checking for a "uk mpg" column and then converting the litres to gallons before it gets imported in, but that will also mean that all future gas records will have to be in gallons instead of litres.

MatStace commented 9 months ago

@hargata yeah, the more I think about this, the more awful it gets, because the UK. Fuel is sold in litres, odometers in cars measure in miles, and to top it off, the gallons in the UK are a different size to US gallons.

Feel free to take my "here are the UK CSV headers from fuelly" as purely informational to improve the import function; I'm going to do some thinking around this and maybe see if I can come up with something more constructive, like a PR

hargata commented 9 months ago

@MatStace I gave this some more thought and I think I got a pretty good way to solve this.

  1. In the Settings page I will add a "UK MPG Calculation" switch.
  2. If the switch is enabled, when you try to add a gas record, it will allow you to input your gas consumption as litres.
  3. When the app performs the MPG calculation, it will perform the following calculation: (Miles traveled) / (litres / 4.546) to get Miles per UK Gal.
  4. This will work for the fuelly imports from UK as well.

I will try to find some time this week to work on this, but you can expect a draft of this functionality to be up by this weekend at the latest.

florianwgnr commented 9 months ago

Spritmonitor-Export is split into 2 parts: Fuelings CSV export & Costs CSV export. I created an example car, put some example data into it and here are the exports as well as the explanation:

Fuelings The format of the file is as follows: • Date: Format DD.MM.YYYY (e.g., 23.02.2010) • Odometer, distance, quantity, total price: Numeric without thousands separator • Currency: Standard abbreviation (e.g., EUR, USD) • Fueling type: 0=invalid fueling, 1=full fueling, 2=partial fueling, 3=first fueling • Tires: 1=summer tires, 2=winter tires, 3=all-year tires • Roads: Sum of 2=motor-way, 4=city, 8=country roads (e.g., motor-way and country roads: 10) • Driving style: 1=moderate, 2=normal, 3=fast • Fuel sort: 1=Diesel, 2=Biodiesel, 3=Vegetable oil, 4=Premium Diesel, 6=Normal gasoline, 7=Super gasoline, 8=SuperPlus gasoline, 9=Premium Gasoline 100, 12=LPG, 13=CNG H, 14=CNG L, 15=Bio-alcohol, 16=Two-stroke, 18=Premium Gasoline 95, 19=Electricity, 20=E10, 21=AdBlue, 22=Premium Gasoline 100+, 23=Hydrogen, 24=Green electricity, 25=GTL Diesel • Note: Text • Consumption, BC-consumption, BC-quantity, BC-speed: Numeric without thousands separator • Company, Country, Area, Location: Text

Exported sample data:

Date;Odometer;Trip;Quantity;Total price;Currency;Type;Tires;Roads;Driving style;Fuel;Note;Consumption;BC-Consumption;BC-Quantity;BC-Speed;Company;Country;Area;Location
08.01.2024;12200,00;550,00;45,00;82,80;"EUR";1;2;10;2;7;"";8,29;;;;"";"";"";""
05.01.2024;11650,00;850,00;71,12;125,17;"EUR";2;1;6;2;7;"";8,29;;;;"";"";"";""
04.01.2024;10800,00;800,00;70,00;122,50;"EUR";1;1;6;2;7;"";8,75;;;;"";"";"";""
03.01.2024;10000,00;500,00;60,00;102,00;"EUR";3;1;6;2;7;"";0,00;;;;"";"";"";""

Costs The format of the file is as follows: • Date: Format DD.MM.YYYY (e.g., 23.02.2010) • Odometer, trip: Numeric without thousands separator • Type: 1=Maintenance, 2=Repair, 3=Change tires, 4=Change oil, 5=Insurance, 6=Tax, 7=Supervisory board, 8=Tuning, 9=Accessories, 10=Purchase price, 11=Miscellaneous, 12=Care, 13=Payment, 14=Registration, 15=Financing, 16=Refund, 17=Fine, 18=Parking tax, 19=Toll, 20=Spare parts • Note: Text

Exported sample data:

Date;Odometer;Cost type;Total price;Currency;Note
06.01.2024;12200,00;Maintenance;500,00;"EUR";"Demo demo "
05.01.2024;12200,00;Change oil;250,00;"EUR";"Change oil "
01.01.2024;10000,00;Purchase price;5000,00;"EUR";""
hargata commented 9 months ago

@florianwgnr what is Consumption/BC-Consumption

hargata commented 9 months ago

@MatStace when you've got a chance, can you pull down the latest docker image and test it?

  1. Go to the Settings page: check enable UK MPG and Imperial Calculation:
  2. Go to Gas/Fuel, and try to add a new gas record, you will be able to add odometer in miles and liters for consumption.
  3. Add another record so you get the MPG calculation
  4. The liters will be converted to UK Gal(divided by 4.546) and then the fuel mileage should show up as MPG, consumption unit should show up as "imp gal" instead of "US gal"
  5. Go back to settings, uncheck Imperial Calculation but keep UK MPG checked, go back to the gas tab, the consumption should stay as liters and the fuel mileage will be calculated as l/100mi.
MatStace commented 9 months ago

@hargata that looks good to me from my initial testing, thank you so much :)

hargata commented 9 months ago

@MatStace did you test the import from Fuelly?

MatStace commented 9 months ago

@hargata I did, and apart from one row which threw an error and stopped the import, it worked great. The one row which caused the error was a data thing - my odometer reading in fuelly had a decimal rather than being an integer, but I think that one is on me for having that one strange fuelly odo reading.

hargata commented 9 months ago

@MatStace ah alright then, thank you!

florianwgnr commented 9 months ago

@florianwgnr what is Consumption/BC-Consumption

BC means "Bordcomputer" (trip computer). It is optional and can be used for example to compare the real consumption against the cars display value. I think you can ignore it for the first integration.

Company, area and location are also optional, you can enter the gas stations address here

hargata commented 9 months ago

@florianwgnr thank you, I'm assuming the amount of fuel is the quantity column then?

florianwgnr commented 9 months ago

@florianwgnr thank you, I'm assuming the amount of fuel is the quantity column then?

Yes, that's correct!

hargata commented 9 months ago

@florianwgnr I have tested your CSV, I'm able to import it after changing the delimiter from ; to , using https://onlinecsvtools.com/change-csv-delimiter Note that you need to run LubeLogger in the right culture in order to import it correctly, if you import it in en-US culture it will not import right because the numbers are formatted in de-DE.

hargata commented 9 months ago

feature delivered.

thehijacker commented 9 months ago

Strugling hard to import CSV export from another page. What are the bare minimum colums needed to get a successful import?

poraba.csv

Thank you.

hargata commented 9 months ago

@thehijacker if you change "Kilometers" to Odometer in your csv it will work.

hargata commented 9 months ago

Closing this since this is specifically for fuelly/spiritmonitor, if anyone has any issues with CSV Imports in the future please open a new FR.

thehijacker commented 9 months ago

@thehijacker if you change "Kilometers" to Odometer in your csv it will work.

Sadly it still doesn't. I get this error:

      Error Occurred While Bulk Inserting
      CsvHelper.ReaderException: No members are mapped for type 'CarCareTracker.Models.ImportModel'.
      IReader state:
         ColumnCount: 0
         CurrentIndex: −1
         HeaderRecord:
      ["Date;Liters;Price;Odometer;Difference;Average usage;Notes;Fuel pump"]
      IParser state:
         ByteCount: 0
         CharCount: 148
         Row: 2
         RawRow: 2
         Count: 3
         RawRecord:
      09.09.2011;30,50;39,25;9.135;;Prvo tankanje po prevzemu avtomobila.;OMV Trebnje

         at CsvHelper.Expressions.ObjectRecordCreator.CreateCreateRecordDelegate(Type recordType)
         at CsvHelper.Expressions.RecordCreator.GetCreateRecordDelegate(Type recordType)
         at CsvHelper.Expressions.RecordCreator.Create[T]()
         at CsvHelper.CsvReader.GetRecords[T]()+MoveNext()
         at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
         at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)
         at CarCareTracker.Controllers.VehicleController.ImportToVehicleIdFromCsv(Int32 vehicleId, ImportMode mode, String fileName) in /App/Controllers/VehicleController.cs:line 246

poraba.csv

Other columns are fine? Can delimeter be ";"?

hargata commented 9 months ago

@thehijacker delimiter cannot be ";" it has to be "," per CSV spec. Please open a new FR.

thehijacker commented 9 months ago

I have change Price to Cost and change delimeter to comma on page you advised (https://onlinecsvtools.com/change-csv-delimiter). And it imported.

Still would be nice to document which column name can be used per which required input.

Looks great now that I have it imported. Time to pass this to wife so she will use it to track fuel consumption.

florianwgnr commented 9 months ago

feature delivered.

Hi, first thanks for your effort regarding the CSV-Import! I just got to test the import from Spritmonitor and want to note all neccessary steps for upcoming users:

  1. On the spritmonitors export-page, first select "english" as language, as this affects the CSV-file

  2. since spritmonitor uses the decimal comma (Germany) instead of the decimal point (USA), the odometer values are wrong by 100. The provided example has 12200km and not 1220000km. For importing, i used Notepad++ to convert , to .

  3. convert the csv delimiter with this tool from ; to ,

Now the import works for the provided demo-data. But sadly with my full export i get this error: 

lube-app-1  | fail: CarCareTracker.Controllers.VehicleController[0]
lube-app-1  |       Error Occurred While Bulk Inserting
lube-app-1  |       System.FormatException: String '' was not recognized as a valid DateTime.
lube-app-1  |          at System.DateTimeParse.Parse(ReadOnlySpan`1 s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
lube-app-1  |          at System.DateTime.Parse(String s)
lube-app-1  |          at CarCareTracker.Controllers.VehicleController.ImportToVehicleIdFromCsv(Int32 vehicleId, ImportMode mode, String fileName) in /App/Controllers/VehicleController.cs:line 293
lube-app-1  | fail: CarCareTracker.Controllers.VehicleController[0]
lube-app-1  |       Error Occurred While Bulk Inserting
lube-app-1  |       System.FormatException: String '' was not recognized as a valid DateTime.
lube-app-1  |          at System.DateTimeParse.Parse(ReadOnlySpan`1 s, DateTimeFormatInfo dtfi, DateTimeStyles styles)
lube-app-1  |          at System.DateTime.Parse(String s)
lube-app-1  |          at CarCareTracker.Controllers.VehicleController.ImportToVehicleIdFromCsv(Int32 vehicleId, ImportMode mode, String fileName) in /App/Controllers/VehicleController.cs:line 293
hargata commented 9 months ago

@florianwgnr have you checked your data to make sure all the data under the date column has data in it and are not blank?