none
External Data Connection - Time Format Field RRS feed

  • Question

  • Hi

    I have a text file which contains two fields, date and time. The format of the fields is as below:

    dd/mm/yyyy,hh:mm:ss

    The text file is being read by Excel 2010 as an external data query. The date field is decoded correctly, however the time field is imported as 00:00:00. I have the Excel format as hh:mm:ss.

    I have spent hours today trying to sort this out but have not had any success. I couldn't find anyone reporting a similar problem in any internet searches.

    Has anyone got any ideas as to the problem here.

    Thanks for any advice you can offer.

    Saturday, November 1, 2014 3:47 PM

Answers

  • It's better to post this question in  Excel IT Pro forum, since it's not about development.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by HarrierJet Wednesday, November 5, 2014 9:51 PM
    Wednesday, November 5, 2014 3:08 AM
    Moderator

All replies

  • Hello HarrierJet,

    How did you import the fields in the text file into the Excel file? Could you please post your code here?

    If you just want to properly import the text file manually, I recommend that you post your question in Excel IT Pro forum. Because this forum is for questions about Excel development. Anyway you could set the date format as "Date>*3/14/2012", and the time format as "Custom>h:mm:ss", then it'll properly import the text file data into the Excel file, by using this ribbon menu: Data>Get External Data>From Text.

    If you want to do it with VBA code, check this sample, it'll do exactly the same thing:

    Sub OpenTextFile()
        Dim FilePath As String
        FilePath = "D:\files\OfficeDev\test files\test.txt"
        Open FilePath For Input As #1
        row_number = 0
        
        Do Until EOF(1)
            Line Input #1, LineFromFile
            LineItems = Split(LineFromFile, ",")
            ActiveCell.Offset(row_number, 0).Value = LineItems(0)
            ActiveCell.Offset(row_number, 1).Value = LineItems(1)
            row_number = row_number + 1
        Loop
        
        Close #1
    End Sub


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Tuesday, November 4, 2014 6:44 AM
    Moderator
  • Hi Caillen

    Thanks for the reply. I was importing the data through an external data connection. So in Excel 2010,

    Data/From Other Sources/From Microsoft Query, then creating a new data source and using the Microsoft Text Driver.

    There was no VBA code, but I will definitely look at the code you provided as an alternative.

    Thanks

    Tuesday, November 4, 2014 11:49 AM
  • It's better to post this question in  Excel IT Pro forum, since it's not about development.

    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    • Marked as answer by HarrierJet Wednesday, November 5, 2014 9:51 PM
    Wednesday, November 5, 2014 3:08 AM
    Moderator