Page 1 of 2 1 2 LastLast
Results 1 to 10 of 13

Thread: how to autofill data

  1. #1

    how to autofill data



    Register for a FREE account, and/
    or Log in to avoid these ads!

    Hi, please bear with me; first time poster. Not sure this is even the right forum for my query!

    I have data in notepad format that I want to import into a spreadsheet. It comprises time in one minute intervals and the number of events in that period (see below). It can run into 400+ rows of data.

    TIME NO
    22:00:00 1
    22:04:00 4
    22:07:00 2

    I want to know whether I can paste the data into excel in a way where excel automatically fills in the missing 1-minute intervals and adds a 0 (shown in bold) so that the data looks like the list below

    TIME NO
    22:00:00 1
    22:01:00 0
    22:02:00 0
    22:03:00 0

    22:04:00 4
    22:05:00 0
    22:06:00 0

    22:07:00 2

    Any help would be much appreciated
    Regards
    Dave

  2. #2
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    If the data file is space delimited then run a tweaked version of:
    Code:
    Sub blah()
    Dim TextLine, FileNumber As Integer, DestRow As Long, x, mytime As Date, NextTime As Date
    FileNumber = FreeFile
    DestRow = 2
    Open "data.txt" For Input As #FileNumber    ' Open file.
    Do  'loop in case there are text headers
      Line Input #FileNumber, TextLine    ' Read line into variable.
      x = Split(Application.Trim(TextLine))
      If Not IsDate(x(0)) Then
        Cells(DestRow, 1).Resize(, 2).Value = x
        DestRow = DestRow + 1
      End If
    Loop Until IsDate(x(0))
    mytime = TimeValue(x(0))
    Cells(DestRow, 1).Value = mytime
    Cells(DestRow, 2).Value = x(1)
    DestRow = DestRow + 1
    mytime = mytime + TimeValue("00:01:00")
    Do While Not EOF(FileNumber)    ' Loop until end of file.
      Line Input #1, TextLine
      x = Split(Application.Trim(TextLine))
      NextTime = TimeValue(x(0))
      Do Until NextTime <= mytime
        Cells(DestRow, 1).Value = mytime
        Cells(DestRow, 2).Value = 0
        Cells(DestRow, 1).Resize(, 2).Font.Bold = True  'include to make inserted values bold
        DestRow = DestRow + 1
        mytime = mytime + TimeValue("00:01:0")
      Loop
      Cells(DestRow, 1).Value = mytime
      Cells(DestRow, 2).Value = x(1)
      mytime = mytime + TimeValue("00:01:00")
      DestRow = DestRow + 1
    Loop
    Close #FileNumber    ' Close file.
    End Sub
    Last edited by p45cal; 2014-07-18 at 04:49 PM.

  3. #3
    Many thanks for taking the time to reply but I'm a zoologist not a computer programmer, and without further guidance, I have absolutely no idea how to apply your answer.

    I was hoping for instructions as to which buttons to press on the toolbar!

  4. #4
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by barb666 View Post
    but I'm a zoologist not a computer programmer, and without further guidance, I have absolutely no idea how to apply your answer.
    I was hoping for instructions as to which buttons to press on the toolbar!
    Writing out instructions would take me far too long, especially as I don't know what delimites the data in your text file.

    Solution 1. I can point you to http://www.excelguru.ca/content.php?...irst-Time-User but you will still need to tweak it with the right file name and put the text file in the right place.
    If you go down this route, come back and tell me how you fare.

    Solution 2. We could do a remote assistance session with the likes of TeamViewer, perhaps along with a 'phone call, to show you how to do it by pressing buttons on the toolbar.

  5. #5
    Ok, I think I've created a macro (I've saved it as an excel macro-enabled worksheet with a .xlsm file extension). I get a Run Time error '53' when I run it. I assume this is due to the file name/file location issue you mention? How do I resolve this?

    Many thanks in advance

  6. #6
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    The line:
    Open "data.txt" For Input As #FileNumber ' Open file.
    needs to become something along the lines of:
    Open "C:\Users\barb666\Documents\excelguru3265\data.txt" For Input As #FileNumber ' Open file.
    that is, the full path to the file. You can get this by opening an instance of Windows Explorer and navigating to the file, then Shift and right-click on the file icon and choose Copy as path, which will put the whole path and the file name into the clipboard, including quote marks. You need to paste this into the code replacing:
    "data.txt"

    Don't forget the code still assumes a space-delimited file; if it's not, tell me and I will adjust. (Where does the file come from?)
    Last edited by p45cal; 2014-07-20 at 04:20 PM.

  7. #7
    I've inserted the correct filepath into the code. As far as I know the data are space delimited - it comes in a text file opened with notepad. When I copy and paste it into excel it automatically fits neatly into columns, but it might not be, so just in case can you explain please?

    I run the macro and get 'Run time error 64 Input past end of file' message. When I press debug the following line of code is highlighted in yellow:

    Line Input #FileNumber, TextLine ' Read line into variable.

    The macro has imported the data into a spreadsheet but instead of reading 21:00 1 it reads 21:001

    Also, it has not inserted the rows that don't have data

  8. #8
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    Then it's likely to be tab delimited. There are two lines like:
    x = Split(Application.Trim(TextLine))

    change them both to:
    x = Split(Application.Trim(TextLine), vbTab)

  9. #9
    Wow, that did the trick! Now in Column A I have consecutive minutes from my first data point to my last, and in column B I have 1's from my data and 0's to fill the gaps - just what I wanted.

    1. I've noticed that some of the 1's from my data have been inserted into the following minute rows, so they are a 1 minute late. Why would this be?
    2. My data sometimes contain other numbers besides 1, usually no more than 6. What line of code would Insert to make the macro interpret any number as 1?
    3. The coded spreadsheet starts and ends with my first and last data points, eg 21:30 and 04:45. I would like the spreadsheet to start at say 21:00 and end at 05:00, adding 0's either side of my data . What code would insert to achieve this?

    Really making progress - many thanks

  10. #10
    Super Moderator p45cal's Avatar
    Join Date
    Dec 2012
    Posts
    1,479
    Articles
    0
    Excel Version
    365
    Quote Originally Posted by barb666 View Post
    1. I've noticed that some of the 1's from my data have been inserted into the following minute rows, so they are a 1 minute late. Why would this be?
    No idea, attach (Go Advanced when replying and click Manage attachments) or send me a sample .txt file where this happens.



    Quote Originally Posted by barb666 View Post
    2. My data sometimes contain other numbers besides 1, usually no more than 6. What line of code would Insert to make the macro interpret any number as 1?
    Change both instances of:
    Cells(DestRow, 2).Value = x(1)
    to:
    Cells(DestRow, 2).Value = IIf(x(1) >= 1, 1, 0)
    which will convert any number greater or equal to 1 into a 1, anything else, a zero.



    Quote Originally Posted by barb666 View Post
    3. The coded spreadsheet starts and ends with my first and last data points, eg 21:30 and 04:45. I would like the spreadsheet to start at say 21:00 and end at 05:00, adding 0's either side of my data . What code would insert to achieve this?
    Try this (after adjustment for file name):
    Code:
    Sub blah()
    Dim TextLine, FileNumber As Integer, DestRow As Long, x, mytime As Date, NextTime As Date
    FileNumber = FreeFile
    DestRow = 2
    Open "data.txt" For Input As #FileNumber    ' Open file.
    Do  'loop in case there are text headers
      Line Input #FileNumber, TextLine    ' Read line into variable.
      x = Split(Application.Trim(TextLine), vbTab)
      If Not IsDate(x(0)) Then
        Cells(DestRow, 1).Resize(, 2).Value = x
        DestRow = DestRow + 1
      End If
    Loop Until IsDate(x(0))
    mytime = TimeValue(x(0))
    Cells(DestRow, 1).Value = mytime
    'Cells(DestRow, 2).Value = x(1)
    Cells(DestRow, 2).Value = IIf(x(1) >= 1, 1, 0)
    DestRow = DestRow + 1
    mytime = mytime + TimeValue("00:01:00")
      mytime = mytime - Int(mytime)
    
    Do While Not EOF(FileNumber)    ' Loop until end of file.
      Line Input #1, TextLine
      x = Split(Application.Trim(TextLine), vbTab)
      NextTime = TimeValue(x(0))
      Do Until Round(NextTime, 5) = Round(mytime, 5)
        Cells(DestRow, 1).Value = mytime
        Cells(DestRow, 2).Value = 0
        Cells(DestRow, 1).Resize(, 2).Font.Bold = True  'include to make inserted values bold
        DestRow = DestRow + 1
        mytime = mytime + TimeValue("00:01:0")
      mytime = mytime - Int(mytime)
      Loop
      Cells(DestRow, 1).Value = mytime
      'Cells(DestRow, 2).Value = x(1)
      Cells(DestRow, 2).Value = IIf(x(1) >= 1, 1, 0)
      mytime = mytime + TimeValue("00:01:00")
      mytime = mytime - Int(mytime)
      DestRow = DestRow + 1
    Loop
    Close #FileNumber    ' Close file.
    End Sub
    Sub blah2()
    Dim TextLine, FileNumber As Integer, DestRow As Long, x, mytime As Date, NextTime As Date
    FileNumber = FreeFile
    DestRow = 2
    'Open "data.txt" For Input As #FileNumber    ' Open file.
    Open "C:\Users\Pascal\Documents\excelguru3265\datatab.txt" For Input As #FileNumber    ' Open file.
    
    Do  'loop in case there are text headers
      Line Input #FileNumber, TextLine    ' Read line into variable.
      x = Split(Application.Trim(TextLine), vbTab)
      If Not IsDate(x(0)) Then
        Cells(DestRow, 1).Resize(, 2).Value = x
        DestRow = DestRow + 1
      End If
    Loop Until IsDate(x(0))
    'fill in from 9pm until first time in file:
    mytime = TimeValue("21:00:00")
    Do Until Round(TimeValue(x(0)), 5) = Round(mytime, 5)
      Cells(DestRow, 1).Value = mytime
      Cells(DestRow, 2).Value = 0
      Cells(DestRow, 1).Resize(, 2).Font.Bold = True  'include to make inserted values bold
      DestRow = DestRow + 1
      mytime = mytime + TimeValue("00:01:0")
      mytime = mytime - Int(mytime)
    Loop
    'print the first file time into the sheet:
    Cells(DestRow, 1).Value = mytime
    Cells(DestRow, 2).Value = IIf(x(1) >= 1, 1, 0)
    DestRow = DestRow + 1
    mytime = mytime + TimeValue("00:01:0")
    mytime = mytime - Int(mytime)
    'process the rest of the file times:
    Do While Not EOF(FileNumber)    ' Loop until end of file…
      Line Input #1, TextLine
      x = Split(Application.Trim(TextLine), vbTab)
      NextTime = TimeValue(x(0))
      Do Until Round(NextTime, 5) = Round(mytime, 5) '…filling in missing times:
        Cells(DestRow, 1).Value = mytime
        Cells(DestRow, 2).Value = 0
        Cells(DestRow, 1).Resize(, 2).Font.Bold = True  'include to make inserted values bold
        DestRow = DestRow + 1
        mytime = mytime + TimeValue("00:01:0")
        mytime = mytime - Int(mytime)
      Loop
      
      Cells(DestRow, 1).Value = mytime
      Cells(DestRow, 2).Value = IIf(x(1) >= 1, 1, 0)
      mytime = mytime + TimeValue("00:01:00")
      mytime = mytime - Int(mytime)
      DestRow = DestRow + 1
    Loop
    Close #FileNumber    ' Close file.
    'fill in after last time to 5am
    Do Until Round(TimeValue("05:01:00"), 5) = Round(mytime, 5)
      Cells(DestRow, 1).Value = mytime
      Cells(DestRow, 2).Value = 0
      Cells(DestRow, 1).Resize(, 2).Font.Bold = True  'include to make inserted values bold
      DestRow = DestRow + 1
      mytime = mytime + TimeValue("00:01:0")
      mytime = mytime - Int(mytime)
    Loop
    
    End Sub
    I've been lazy and used several loops when with bit more logic I could probably have done it in one loop, but it seems to work.

Page 1 of 2 1 2 LastLast

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •