Copying data from *.csv into excel

Question

I have a csv file that is filled with outputs from a temperature sensor, it has date, temperature controller address, temp sensor 1 value, temp sensor 2 value, heat power, cool power and limit and alarm states, here is an example with spaces added for readability when opened in notepad:

2/10/2016 14:52:26.2, 1, 73.9039, 74.89208, 14.63515, 0, F, None, None, None,
2/10/2016 14:52:36.594, 1, 73.75067, 74.86765, 25.21247, 0, F, None, None, None,
2/10/2016 14:52:47.165, 1, 73.66284, 74.83871, 35.95927, 0, F, None, None, None,
2/10/2016 14:52:57.788, 1, 73.59991, 74.79031, 47.17537, 0, F, None, None, None,
2/10/2016 14:53:8.381, 1, 73.54018, 74.75883, 58.62064, 0, F, None, None, None,

however, if I open it in excel, i get this format:

52:26.2, 1, 73.9039, 74.89208, 14.63515, 0, F, None, None, None
52:36.6, 1, 73.75067, 74.86765, 25.21247, 0, F, None, None, None
52:47.2, 1, 73.66284, 74.83871, 35.95927, 0, F, None, None, None
52:57.8, 1, 73.59991, 74.79031, 47.17537, 0, F, None, None, None
53:08.4, 1, 73.54018, 74.75883, 58.62064, 0, F, None, None, None

I'm trying to use vba in excel to pull the data from this csv file or multiple csv files based on user selection through a file picker dialog into a workbook for graphing. The problem I'm running into is that the date and time column doesn't show up correctly. I think it has something to do with the formatting I'm using for the data, but I'm not sure. The format I use is

m/d/yyyy h:mm:ss.000

For some reason this is the data that shows up in the excel file:

1/0/1900 0:52:26.200, 1, 73.90390, 74.89208, 14.64, 0, F, None, None, None
1/0/1900 0:52:36.600, 1, 73.75067, 74.86765, 25.21, 0, F, None, None, None
1/0/1900 0:52:47.200, 1, 73.66284, 74.83871, 35.96, 0, F, None, None, None
1/0/1900 0:52:57.800, 1, 73.59991, 74.79031, 47.18, 0, F, None, None, None
1/0/1900 0:53:08.400, 1, 73.54018, 74.75883, 58.62, 0, F, None, None, None

I tried formatting the first column as a number with the format

0.0000000000

then moving the data, but the column is just blank then because I had to use a range data type to be able to change the format. Here is the code I use to show the file picker and move the data:

    Sub fileDialogStart()

    Dim fd As FileDialog
    Dim vrtSelectedItem As Variant

    Set fd = Application.FileDialog(msoFileDialogFilePicker)

    With fd
        If .Show = -1 Then
            For Each vrtSelectedItem In .SelectedItems
                ' Finds the last row in the current workbook
                With ThisWorkbook.Sheets(1)
                    lastRow = .Cells(.Rows.Count, "A").End(xlUp).Row
                End With
                ' Imports the data from the selected file
                Call importData(vrtSelectedItem)
            Next vrtSelectedItem
        Else
        End If
    End With

    Set fd = Nothing
End Sub

Sub importData(ByVal filePath As String)

    Dim targetWorkbook As Workbook, sourceWorkbook As Workbook
    Dim finalRow As Integer
    'Dim targetData as Range

    Set sourceWorkbook = Workbooks.Open(filePath)

    With sourceWorkbook.Sheets(1)
        ' Finds the number of rows in the file selected from the file picker
        finalRow = .Cells(.Rows.Count, "A").End(xlUp).Row
        ' Stores the first column
        targetData = .Range(.Cells(1, 1), .Cells(finalRow, 1))
    End With

    ' Sets the number format
    'targetData.NumberFormat = "0.0000000000"

    With ThisWorkbook.Sheets(1)
        ' Puts the new data into the current workbook after the last row of data
        ' in case there is already data in the workbook from a previous import
        .Range(.Cells(lastRow, 1), .Cells(lastRow + finalRow - 1, 1)) = targetData
    End With

    With sourceWorkbook.Sheets(1)
        ' Gets the rest of the data from the file selected
        sourceData = .Range(.Cells(1, 2), .Cells(finalRow, 10))
    End With

    With ThisWorkbook.Sheets(1)
        ' Puts the rest of the data into the current workbook
        .Range(.Cells(lastRow, 2), .Cells(lastRow + finalRow - 1, 10)) = sourceData
    End With

    sourceWorkbook.Save
    ThisWorkbook.Save
    sourceWorkbook.Close False
End Sub

The fileDialogStart() sub is called in the sub used to graph at the very beginning and I set the format of the entire first column back to m/d/yyy h:mm:ss.000 in there after the data is imported. Maybe the problem has something to do with the fact that i'm opening the csv file as a workbook? I'm not sure. Any help would be appreciated, thanks!

Answer 1

The Workbooks.OpenText method allows you to specify the TextFileColumnDataTypes property of each field. This equates to the Range.TextToColumns method. Specifying an xlMDYFormat format for the first field is sufficient to bring the datetimes in correctly (including their fractions of seconds).

Sub openCSV()
    Dim fp As String, fn As String, wbCSV As Workbook

    fp = Environ("TMP")
    fn = "datetemp.csv"
    Debug.Print fp & Chr(92) & fn
    Workbooks.OpenText Filename:=fp & Chr(92) & fn, DataType:=xlDelimited, _
                       Tab:=False, Semicolon:=False, Space:=False, _
                       Other:=False, Comma:=True, FieldInfo:=Array(1, xlMDYFormat), _
                       Local:=True
    With ActiveWorkbook
        With Worksheets(1)
            With .Columns(1)
                .NumberFormat = "mm/dd/yyyy  hh:mm:ss.000"
                .AutoFit
            End With
        End With
    End With

End Sub

The Local:=True may not be desired depending upon your own regional settings. It worked fine for my EN-US defaults.

Answer 2

Excel is doing some weird things; however when I run this:

Sub WTF()
   Dim FilesToOpen
   Dim wf As WorksheetFunction
   Set wf = Application.WorksheetFunction
   Dim ary, bry, DQ As String
   DQ = Chr(34)

   FilesToOpen = Application.GetOpenFilename _
      (FileFilter:="Text Files (*.csv), *.csv", Title:="Text Files to Open")

   Close #1
   Open FilesToOpen For Input As #1

   j = 1
   Do While Not EOF(1)
      Line Input #1, TextLine
      ary = Split(TextLine, ",")
      bry = Split(ary(0), " ")
         Cells(j, 1).Formula = "=datevalue(" & DQ & bry(0) & DQ & ")+timevalue(" & DQ & bry(1) & DQ & ")"
         Cells(j, 1).NumberFormat = "mm/dd/yyyy hh:mm:ss.000"
         For k = 2 To 10
            Cells(j, k) = ary(k - 1)
         Next k
      j = j + 1
   Loop

   Close #1
End Sub

On your posted data, I get:

What I cannot explain is why Excel does such a poor job of opening the file directly.

Perhaps some one else can explain this.