sync 2 seperate gps times in two different columns

[xavier73]

I have 2 different gps loggings that i want to synchronise in time. Can someone help to do this in a macro? The first gps is logged every second and the other one logs every 8 seconds.

[p45cal]

Supply a file (or 2) containing the logs.

[xavier73]

Here are 2 files i want to compare.

[p45cal]

Where do you want to see data when the times are the same; on the same row or one below the other?
Also (not very important) are the dates DMY or MDY?

[p45cal]

Oh, and what version of Excel?

[xavier73]

I'm using office 2010.

[p45cal]

See attached. Lots of assumptions made.
Currently it only looks at the time element and ignores the date element. This can be catered for if necessary.
When you run the macro pay attention to the title bar of the Open File dialogue boxes.

For those interested, the macro:
Sub blah()
fn1 = Application.GetOpenFilename("CSV files,*.csv", , "Select 1 second csv file")
fn2 = Application.GetOpenFilename("CSV files,*.csv", , "Select multiple second csv file")
If fn1 = False Or fn2 = False Then Exit Sub
Set Sht1 = Sheets.Add(After:=ActiveSheet)
Set qt1 = Sht1.QueryTables.Add(Connection:="TEXT;" & fn1, Destination:=Sht1.Range("$A$1"))
With qt1
.FieldNames = True
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileSemicolonDelimiter = True
.TextFileColumnDataTypes = Array(4, 1, 1, 1)
.TextFileDecimalSeparator = ","
.TextFileThousandsSeparator = "."
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Set sht2 = Sheets.Add(After:=ActiveSheet)
Set qt2 = sht2.QueryTables.Add(Connection:="TEXT;" & fn2, Destination:=sht2.Range("$A$1"))
With qt2
.FieldNames = True
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileSemicolonDelimiter = True
.TextFileColumnDataTypes = Array(4, 1, 1, 1)
.TextFileDecimalSeparator = ","
.TextFileThousandsSeparator = "."
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
Sht1.Range("C1:D1").Copy Sht1.Range("F1")
With Intersect(qt1.ResultRange.Offset(1), qt1.ResultRange).Offset(, 5).Resize(, 2)
.Columns(1).FormulaR1C1 = "=VLOOKUP(RC2," & sht2.Name & "!" & qt1.ResultRange.Offset(, 1).Address(1, 1, xlR1C1) & ",2,0)"
.Columns(2).FormulaR1C1 = "=VLOOKUP(RC2," & sht2.Name & "!" & qt1.ResultRange.Offset(, 1).Address(1, 1, xlR1C1) & ",3,0)"
On Error Resume Next
.SpecialCells(xlCellTypeFormulas, 16).ClearContents
On Error GoTo 0
.Value = .Value
End With
qt1.Delete
Application.DisplayAlerts = False: sht2.Delete: Application.DisplayAlerts = True
End Sub

[p45cal]

Please note that the file and code have been significantly changed in the previous message since I first posted it.

[xavier73]

This is exactly what I need. Thank you for the quick response. I'm now trying to figure out how the code works so that I can change the output format.

[snb]

Sub M_snb()
With CreateObject("scripting.filesystemobject")
sn = Split(.opentextfile("J:\download\seconds.csv").readall, vbCrLf)
sp = Split(.opentextfile("J:\download\10seconds.csv").readall, vbCrLf)

With CreateObject("scripting.dictionary")
For j = 1 To UBound(sn) - 1
st = Split(sn(j), ";")
.Item(st(0) & st(1)) = sn(j)
Next
For j = 1 To UBound(sp) - 1
st = Split(sp(j), ";")
If .exists(st(0) & st(1)) Then .Item(st(0) & st(1)) = .Item(st(0) & st(1)) & ";" & st(2) & ";" & st(3)
Next
c00 = Join(.items, vbCrLf)
End With

.createtextfile("G:\OF\GPS.csv").write c00
End With

Workbooks.Open "G:\OF\GPS.csv", local:=True
End Sub

[xavier73]

I can't seem to figure out how to make it work with the original loggings. The first file will be a little bit different in the future and the resolution will be in seconds. But I hope that you can help me to make it work for these files. And i only want to show the date, time, lat, long, status and course.

[snb]

Handig hoor om nu ineens heel andere bestanden te plaatsen.

[xavier73]

Handig hoor om nu ineens heel andere bestanden te plaatsen.
Ik had gehoopt met de code deze loggings zelf aan te kunnen passen. Dit lukt me helaas niet. Sorry.

[xavier73]

I have found the problem. I had to change the following. Thanks for all the help.


.TextFileCommaDelimiter = True
.TextFileColumnDataTypes = Array(9, 1, 1, 1, 1, 9, 1, 1, 9, 9, 9, 9, 9)
.TextFileDecimalSeparator = "."
.TextFileThousandsSeparator = ","

[p45cal]

A different approach. Look in the vicinity of J1 of the added sheet after clicking the button.

[xavier73]

Thanks. That looks great. Do you think it's possible to put a bracket in, that if the time difference is less than 8 seconds it can put that lat/long position in the same line? Example: 08:08:08 of GPS01 and 08:08:10 of GPS02 can be aligned because of the time difference of 2 seconds.

[p45cal]

I'm sure it's possible and I'm thinking about it.
In the meantime, I've taken a lazy approach and tweaked the existing macro.
I've added a new column headed Rounded Time after importing the data which rounds the time to the nearest 6 seconds. 6 seconds was the largest rounding figure I could use (with your particular sample data - it's likely to be different for other raw data) before mutiple readings from the same gps would round to the same value. I use this at the left of the pivot instead of plain Time but now add the real Time to the right in the pivot so you can see what times have been merged onto one line. The maximum spread on one line is 5 seconds.

[xavier73]

Thank you for all you're effort. I think this will work great.

[xavier73]

Is it possibel to allign the time when the data is in the same worksheet? 17679

[p45cal]

In the previous solutions we had a column "Device Name" which was used put the merged data in separate columns; which columns are the equivalent in your most recent file?

At its simplest,

insert three blank columns between columns F and G
select and cut (not copy) cells F2:K114
and paste to cell A1949
cut J1:K1 to E1:F1
(at this stage you could simply sort cells A1:F2061 by column A, but this would repeat times in column A)
insert a pivot table using A1:F2061 as source data, place it at H1
change the Design|Report Layout to Tabular
remove subtotals and grandtotals
add data to the pivot as follows:


17680

see attachment.

[xavier73]

I want to add the Kolom G and H next to kolom D Without the import function. Maybe it is easier to put kolom F, G and H in a seperate sheet.

[p45cal]

see edited msg#20

[xavier73]

This works great. These Pivot table is new to me. Thanks for you're help.