Time Issue CSV

[smythcounty]

When I export a CSV file from a third party it puts the times in a weird format. For example, the correct time should be 5:48 AM. If I open the CSV file in notepad the time value = 48:55.6. If I open the CSV file in Excel it shows 48:55.6 and when I select the cell is shows the time value as 12:48:56. AM. If I export into Tabular format I get 05:48:55.5640000. If I export the file into Excel form, its shows the correct time of 5:48 AM. I need it in CSV because this is how I import it with my macro. Any ideas to correct this, or is this a glitch in the exporting program?

Thank You!

 

[Joe4]

When I export a CSV file from a third party it puts the times in a weird format. For example, the correct time should be 5:48 AM. If I open the CSV file in notepad the time value = 48:55.6.

With a value like "48:55.6", how is Excel (or anything or anyone else) supposed to know that there should be a "5:" in front of it if it is missing?

I think you need to change how it is exported from this third-party software that contains the complete time, or else neither Excel or any user is going to be able to figure out what that missing value should be.

 

[rlv01]

If you open it in notepad before excel touches the file, and the value is "48.55.6" that sounds like an export issue from the 3rd party program and/or how it codes time exports. If I were to guess, it would be that the 3rd party app is exporting hours and minutes as separate fields and you need to take some kind of action in excel to reconstruct the time after import. Are you sure there is not a mysterious "5" in the csv file data?

",05,48:55.6,XYZ,"

 

[smythcounty]

Okay, here is what is happening. When I open the CSV file in Excel and then save it, its losing it formatting for those times. I'm saving it in UTF-8. If I look at the CSV file in notebook before I save it after downloading here is what is there "05:48:55.5640000". After I save it in Excel and reopen in notepad, here is what I get "48.55.6". I don't always have to open it in Excel first, but sometimes I do for some edits. What's your thoughts? Edit in Notepad and be done with it?

Thanks for the brainstorming.

 

[Joe4]

General rule of thumb is never directly open a CSV file in Excel if you want to see what it really contains, or if you wish to make edits to it and save it.
That is because Excel will automatically try to "guess" at the file formats and makes its own conversions on the file.

If you want to open in Excel to make edits on it, it is probably best to use the old legacy "File Import Wizard" (see: Text Import Wizard), and import every field as text to maintain all the formatting of the CSV file.

Note that you may need to change the file extension from "CSV" to "txt" in order to get Excel to try and open it using the "File Import Wizard".

 

[rlv01]

There are a couple of different ways to handle that. If it is limited to just these time format issues, you might be able to get away with a quick fixup sub like this:

Sub TimeFormatFix()
    Const TF = "hh:mm:ss AM/PM"  'desired time format
    Dim R As Range
    
    For Each R In ActiveSheet.UsedRange
        With R
            If TypeName(.Value) = "Double" And .NumberFormat <> TF Then
                If (InStr(.NumberFormat, "mm:") > 0) Then 'just look at the cells that excel thinks is some sort of time
                    .NumberFormat = TF
                End If
            End If
        End With
    Next R
End Sub

that you run on every 3rd party csv file the first time you open one. Then there is the text import wizard that Joe4 mentions, lastly it is possible to use the QueryTable object to open a .csv file as text without having excel try to "guess" the data type.