0

I am importing a load of time-stamped data into a spreadsheet using VBA.

I am using the following code to import it:

Private Sub SelectFiles()
    '
    ' Part of a form for importing the date
    '
    
    Dim iFileSelect As FileDialog, sFile
    Set iFileSelect = Application.FileDialog(msoFileDialogOpen)
    
    Application.ScreenUpdating = False
    Application.DisplayAlerts = False
    
    ' Run file dialoug
    With iFileSelect
        .AllowMultiSelect = False
        .Title = "Select NPD File"
        .Filters.Clear
        .Filters.Add "NPD Files", "*.npd, *.NPD"
        .InitialView = msoFileDialogViewDetails
        If .Show = -1 Then
            sFile = .SelectedItems(1)
            Workbooks.Open Filename:=sFile, _
                Delimiter:=","
        End If
    End With
    
    ' Run deliminations (the Dimiliter in the import didn't seem to work
    Columns("A:A").TextToColumns _
        Destination:=Range("A1"), _
        DataType:=xlDelimited, _
            Space:=False, _
            Comma:=True
    
    ' Update a text box in the form
    tb_ImportData.Value = sFile
    
    Set iFileSelect = Nothing
    
    Application.ScreenUpdating = True
    
End Sub

An example of the Raw data file is:

Time,Position: Vessel Ref (Priority 1): East,North,Lat,Long,Height,Position: Vessel Ref (Priority 2): East,North,Lat,Long,Height,Position: Vessel Ref (Priority 3): East,North,Lat,Long,Height,Position: Veripos 1 Port: East,North,Lat,Long,Height,Position: Veripos 2 Stbd: East,North,Lat,Long,Height,Position: Veripos 3 Survey: East,North,Lat,Long,Height,Gyro: Seapath 1,Gyro: Seapath 2,Gyro: Octans - Port,Gyro: Octans - Stbd,Gyro: Anschutz 1,Gyro: Anschutz 2,Motion: Seapath RPH1 R, P, H,Motion: Seapath RPH2 R, P, H,Motion: Vsl Oct MRU - Port R, P, H,Motion: Vsl Oct MRU - Stbd R, P, H,Motion: MRU1 R, P, H,Motion: MRU2 R, P, H,
14/01/2021 15:38:55,  340828.7321,5482873.9254,  049°28'39.44929",  000°48'9.54126", 49.5634,  340828.7697,5482873.8963,  049°28'39.44838",  000°48'9.54317", 49.5200,  340828.7905,5482873.8792,  049°28'39.44785",  000°48'9.54423", 49.5479,  340799.5217,5482888.9646,  049°28'39.90835",  000°48'8.06891", 87.3525,  340800.1545,5482890.0231,  049°28'39.94320",  000°48'8.09880", 87.3100,  340802.1133,5482891.7275,  049°28'40.00020",  000°48'8.19360", 87.0900,  296.3800,  294.5400,  295.6100,  296.9500,  296.2000,  296.7000,    0.2000,   0.1800,   0.0000,    0.1700,   0.1500,   0.0100,   -2.0900,   0.3300,   0.0000,    2.6000,  -0.1800,   0.0000,    0.1894,   0.1816,   3.2660,    0.1901,   0.1553,   2.5260
14/01/2021 15:38:56,  340828.7283,5482873.9168,  049°28'39.44901",  000°48'9.54108", 49.5664,  340828.7697,5482873.8963,  049°28'39.44838",  000°48'9.54317", 49.5300,  340828.7843,5482873.8687,  049°28'39.44750",  000°48'9.54393", 49.5485,  340799.5236,5482888.9670,  049°28'39.90843",  000°48'8.06900", 87.3556,  340800.1545,5482890.0231,  049°28'39.94320",  000°48'8.09880", 87.3200,  340802.1133,5482891.7275,  049°28'40.00020",  000°48'8.19360", 87.0900,  296.3800,  294.5400,  295.6100,  296.9600,  296.2000,  296.7000,    0.2000,   0.1800,   0.0000,    0.1900,   0.1600,   0.0100,   -2.0900,   0.3300,   0.0000,    2.6000,  -0.1800,   0.0000,    0.1902,   0.1824,   2.8160,    0.1917,   0.1567,   2.2100
14/01/2021 15:38:57,  340828.7320,5482873.9302,  049°28'39.44944",  000°48'9.54125", 49.5632,  340828.7545,5482873.8908,  049°28'39.44819",  000°48'9.54242", 49.5302,  340828.7843,5482873.8687,  049°28'39.44750",  000°48'9.54393", 49.5385,  340799.5221,5482888.9702,  049°28'39.90853",  000°48'8.06892", 87.3524,  340800.1425,5482890.0235,  049°28'39.94320",  000°48'8.09820", 87.3200,  340802.1133,5482891.7275,  049°28'40.00020",  000°48'8.19360", 87.0800,  296.3600,  294.5300,  295.6200,  296.9600,  296.2000,  296.7000,    0.2000,   0.1800,   0.0000,    0.1900,   0.1600,   0.0100,   -2.0900,   0.3300,   0.0000,    2.6100,  -0.1800,   0.0000,    0.1910,   0.1827,   2.2890,    0.1924,   0.1573,   1.8520
14/01/2021 15:38:58,  340828.7220,5482873.9107,  049°28'39.44880",  000°48'9.54078", 49.5605,  340828.7584,5482873.8976,  049°28'39.44841",  000°48'9.54260", 49.5302,  340828.7905,5482873.8780,  049°28'39.44781",  000°48'9.54423", 49.5385,  340799.5235,5482888.9725,  049°28'39.90861",  000°48'8.06898", 87.3499,  340800.1425,5482890.0235,  049°28'39.94320",  000°48'8.09820", 87.3200,  340802.1133,5482891.7275,  049°28'40.00020",  000°48'8.19360", 87.0800,  296.3800,  294.5300,  295.6200,  296.9600,  296.2000,  296.7000,    0.2200,   0.1800,   0.0000,    0.2000,   0.1600,   0.0000,   -2.0800,   0.3300,   0.0000,    2.6100,  -0.1700,   0.0000,    0.1922,   0.1832,   1.5800,    0.1942,   0.1577,   1.1350
14/01/2021 15:38:59,  340828.7370,5482873.9382,  049°28'39.44971",  000°48'9.54148", 49.5554,  340828.7662,5482873.8896,  049°28'39.44816",  000°48'9.54300", 49.5202,  340828.7781,5482873.8571,  049°28'39.44712",  000°48'9.54364", 49.5396,  340799.5266,5482888.9775,  049°28'39.90877",  000°48'8.06913", 87.3445,  340800.1545,5482890.0231,  049°28'39.94320",  000°48'8.09880", 87.3100,  340802.1133,5482891.7275,  049°28'40.00020",  000°48'8.19360", 87.0800,  296.3700,  294.5400,  295.6200,  296.9600,  296.2000,  296.7000,    0.1900,   0.1800,   0.0000,    0.2000,   0.1600,   0.0000,   -2.0800,   0.3400,   0.0000,    2.6100,  -0.1700,   0.0000,    0.1944,   0.1840,   7.3700,    0.1953,   0.1574,   1.4510
14/01/2021 15:39:00,  340828.7400,5482873.9400,  049°28'39.44977",  000°48'9.54163", 49.5581,  340828.7673,5482873.9157,  049°28'39.44901",  000°48'9.54302", 49.5174,  340828.7886,5482873.8778,  049°28'39.44780",  000°48'9.54413", 49.5372,  340799.5296,5482888.9792,  049°28'39.90883",  000°48'8.06928", 87.3472,  340800.1551,5482890.0416,  049°28'39.94380",  000°48'8.09880", 87.3100,  340802.1133,5482891.7275,  049°28'40.00020",  000°48'8.19360", 87.0800,  296.3700,  294.5400,  295.6300,  296.9700,  296.2000,  296.7000,    0.2200,   0.1900,   0.0000,    0.1900,   0.1600,   0.0000,   -2.0800,   0.3400,   0.0000,    2.6100,  -0.1700,   0.0000,    0.1970,   0.1838,  -4.0400,    0.1968,   0.1564,  -9.3500
14/01/2021 15:39:01,  340828.7278,5482873.9244,  049°28'39.44925",  000°48'9.54105", 49.5462,  340828.7676,5482873.9143,  049°28'39.44896",  000°48'9.54304", 49.5183,  340828.7799,5482873.8688,  049°28'39.44750",  000°48'9.54372", 49.5354,  340799.5325,5482888.9780,  049°28'39.90880",  000°48'8.06942", 87.3413,  340800.1551,5482890.0416,  049°28'39.94380",  000°48'8.09880", 87.3100,  340802.1133,5482891.7275,  049°28'40.00020",  000°48'8.19360", 87.0800,  296.3600,  294.5400,  295.6300,  296.9700,  296.2000,  296.7000,    0.2000,   0.1800,   0.0000,    0.2000,   0.1600,   0.0000,   -2.0800,   0.3400,   0.0000,    2.6100,  -0.1700,   0.0000,    0.1981,   0.1823,  -1.4600,    0.1982,   0.1555,  -1.9200
14/01/2021 15:39:02,  340828.7411,5482873.9428,  049°28'39.44986",  000°48'9.54168", 49.5509,  340828.7676,5482873.9209,  049°28'39.44918",  000°48'9.54303", 49.5154,  340828.7873,5482873.8791,  049°28'39.44784",  000°48'9.54407", 49.5257,  340799.5319,5482888.9825,  049°28'39.90894",  000°48'8.06939", 87.3407,  340800.1551,5482890.0416,  049°28'39.94380",  000°48'8.09880", 87.3100,  340802.1133,5482891.7275,  049°28'40.00020",  000°48'8.19360", 87.0700,  296.3500,  294.5400,  295.6300,  296.9800,  296.2000,  296.7000,    0.2100,   0.1800,  -0.0100,    0.2000,   0.1600,   0.0000,   -2.0700,   0.3300,   0.0000,    2.6100,  -0.1700,   0.0000,    0.1993,   0.1809,  -2.3800,    0.1999,   0.1531,  -2.7500

The date/time string is coming in the correct format (e.g. 14/01/2021 16:08:57). This is an example of the data:

enter image description here

Here, the date-time is in a string format. However, if you select a cell and press F2, it will convert it into a DateTime format.

I am able to use VBA to convert each cell individually into DateTime format as well. However, I have over 10,000 rows in this dataset and thus is loop is taking too long.

Is it possible to convert the entire column without having to loop through the individual cells?

Improve this question
2
  • What happens if you try Range("A1:A10000").value = Range("A1:A10000").value? Or you try to copy a cell with the value 1 in it and paste it to Range("A1:A1000") telling Excel to do a multiplication. Commented Mar 6, 2021 at 15:06
  • @Storax I have tried Range("A1:A10000").value = Range("A1:A10000").value and that did not work. The second option also does not work in VBA. It works fine of you do it in Excel, though, which is making me start to think that VBA just hates me today and there is an incorrect setting/bug somewhere Commented Mar 6, 2021 at 15:30

3 Answers 3

Reset to default
1

Try this procedure to convert the text into date. As I copy the data into memory this is a lot faster than doing it in the worksheet

Sub convDate()

    Dim vDat As Variant
    vDat = Range("A2:A1000")
    
    
    Dim i As Long
    For i = LBound(vDat) To UBound(vDat)
        vDat(i, 1) = CDate(vDat(i, 1))
    Next
    Range("A2:A10000") = vDat
    
End Sub
Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

This works. Didn't think about pulling it all into memory like that. No noticeable drop in speed either
1

Assuming your date is in B2, in C2 type this:

=Date(RIGHT(LEFT(B2, 10), 4), RIGHT(LEFT(B2, 5), 2), LEFT(B2, 2))
+
Time(RIGHT(LEFT(B2, 13), 2), RIGHT(LEFT(B2, 16), 2), RIGHT(B2, 2))

Copy that down the column C.

Explanation

We are using the =Date(year, month, day) and =Time(hour, minutes, seconds) functions in Excel.

If time is 14/01/2021 16:08:57, LEFT(B2, 10) will give you 14/01/2021. RIGHT(LEFT(B2, 10), 4) will give you 2021. Using that method, we pluck year, month and date and pass it to Date function.

We repeat that with Time function and add them up.

Improve this answer

6 Comments

Thanks, @zedfoxus. I tried that, but I was hoping to be able to keep it all in VBA. It seems ridiculous that Excel knows that it is time and date yet it is so difficult to make it see it as the correct type. Why can't companies all just start using Python?
@GalacticPonderer in VBA, it might be easier. You can do CDate("14/01/2021 16:08:57") or whatever the value is. Perhaps the problem is a bit different than what I am assuming. Can you include a sample text data and the sample VBA you have written to import it into Excel? I can take that and run some tests to see how you can get the right date in Excel.
I have edited my question a little bit. To summaries the changes, I am bringing in a data file, when it comes into Excel, DT is a string. You can convert String to DT in Excel by pressing F2 on each cell. How can you block change all cells at once (without adding extra columns or inserting formulas if possible)
@GalacticPonderer it seems like you want to change the data in-place. I don't know how to do that. If you can share an example of your data file and the VBA you are writing to import data, I can re-write the VBA so data is imported correctly. My answer adds a new column and converts your data. It'd require very little effort and you'd still get clean data.
I have just implemented your method and it is working. Thank you for that. It is a little more "hacky" / unstable than I would like, but I suppose that is just VBA for you.
|
0

When using TextToColumns you can specify the data format for the columns via the FieldInfo argument.

To specify that the column is a date with the format YMD you would use xlYMDFormat.

Columns("A:A").TextToColumns _
    Destination:=Range("A1"), _
    DataType:=xlDelimited, _
        Space:=False, _
        Comma:=True, FieldInfo:=Array(Array(1, xlYMDFormat))
Improve this answer

6 Comments

Not sure if the TS tried this but I did and it just did not work as one would expect. Maybe the reason is the additional time data which is contained in the string. I also tried xlDMYFormat, see here.
I have also just tried this and can confirm that it does not work. TBH, the software that is producing this data is a nightmare for generating issues like this.
The time part shouldn't cause a problem, but there's plenty of other things that could - for example there might be trailing/leading space, or perhaps some other non-printing character.
@norie, good thought but nope. The data is "clean" in that there are not extra chars (else zedfoxus' suggestion would not have worked). I really have no idea what else is causing this
Have you tried importing the data rather than using Workbooks.Open? When you import a text file you should automatically get the Text to columns... wizard.
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.