Instructables

Geo Data Logger: Arduino+GPS+SD+Accelerometer to log, time-stamp, and geo-tag sensor data

Featured

Step 7: SCRUBBING & FORMATTING DATA WITH A SPREADSHEET

Picture of SCRUBBING & FORMATTING DATA WITH A SPREADSHEET
Excel-delimited.jpg
Excel-import-comma.jpg
Excel-column-types.jpg
Excel-invalid-data-more.jpg
Excel-invalid-data.jpg
Excel-jumbled-rows.jpg
Excel-hide-column.jpg
Exce-column-lables.jpg
Excel-copy-data-cells.jpg
With enough road data captured on the SD card, we now start the data scrubbing and re-formatting in preparation for analysis and visualization in Excel, GPSvisualizer, or our favorite data analysis and charting tool. 

Remove the SD card from the socket and insert into PC SD card reader. Copy the GPS.log file to your PC and Run Excel (or your favorite spreadsheet application). The steps below are for Excel 2007
  • STEP 1: From Excel, File/Open and select GPS.log. Make sure you select File Type All Files (*.*) else you will not see the file GPS.log listed. Open file. This will launch the Text Import Wizard.
  • STEP 2: Select Delimited radio button. Click Next.
  • STEP 3: Select Comma check box only. Cick Next.
  • SETP 4: Excel will import the GPS.log file into columns and rows. The columns are ordered  in this manner: X, Y, Z, NMEA output type, UTC Time, Status A: A=data valid or V=data not valid, Latitude, N/S: Indicator N N=north or S=south, Longitude, E/W Indicator: E E=east or W=west, Speed over ground, Course over ground, Date, Magnetic variation, Variation sense: E=east or W=west, Mode A: A=autonomous, D=DGPS, E=DR, Checksum.
  • STEP 5,6: In column 'F' you will see one letter either A or V. A means valid fix. V means invalid data. So delete all rows that are invalid.
  • STEP 7: Also, delete jumbled lines. 
  • STEP 8: Keep columns C (z-axis), G (Latitude), and I (Longitude) but hide other imported columns. 
  • STEP 9: Add header labels to the top of the remaining three columns: N, Latitude,  Longitude.
  • STEP 10: Now select and copy to clipboard the range of rows you wish to map in GPSvisualizer and don't forget to also copy the columns header labels. 
With the selected data in the clipboard, we are ready to paste it into GPSvisualizer so we can map and analyze our logged data.
 
Remove these adsRemove these ads by Signing Up