Creating macro to automatically insert large amounts of data from text file then format data (long post)

[random_noise]

Hi there,

I have absolutely no VBA / Macro programming experience altough am quite computer literate and can do basic / pascal etc programming. I am really just wondering if it’s possible to implement a macro which will import data from a text file and split the data at the appropriate points either into separate worksheets/ tabs or just in several columns. There may be plug-ins which could be modified or it might simply be completely unfeasible. I would appreciate the advice of an expert to let me know if I’m wasting my time.

I’m sure to an expert this may be (reasonably) simple. If someone could let me know how much of a task is & if they think it’s something that would be worth me speaking to a programmer to have implemented I’d appreciate it.

A bit of background:
Using Excel 2003.

I have to produce reports which contain graphs weekly. These graphs are made from data which is sent to me in a text file with data set in rows with tabs between each column which is obtained by bespoke software attached to a sensor.

The text files are around 10MB in size are quite a large amount of rows (indeed too many for the 65K limit imposed by Excel which means I cant just load the text file directly into excel.

An example of the data I’m importing is shown below:

# Exported using {Software name}
# Export time: Tue Dec 11 16:03:09 GMT 2007
# Site: {site name}
# Well: {well name}
# Sensor: {sensor name}
# Data set: 220605 frames
# Start time: 1196726400000 (Tue Dec 04 00:00:00 GMT 2007)
# header legend: dT = Delta time since Start time
# NL = Noise level (raw sensor value)
# SR = Sand rate
# SM = Sand mass
# SandAlm = Sand alarm (0 = no alarm, 1 = alarm)
# v = Velocity
# c = Choke position
# p = Well pressure
# t = Temperature
# QOil = Oil flow rate
# QGas = Gas flow rate
# QWater = Water flow rate
dT NL SR SM SandAlm v c t p QOil QGas QWater
206 46048 0.0 0.0 0 0.0 0.0 0.0 0.0 0 0 0
3210 43456 0.0 0.0 0 0.0 0.0 0.0 0.0 0 0 0
6205 43840 0.0 0.0 0 0.0 0.0 0.0 0.0 0 0 0
9209 45376 0.0 0.0 0 0.0 0.0 0.0 0.0 0 0 0
12213 45728 0.0 0.0 0 0.0 0.0 0.0 0.0 0 0 0
15218 43040 0.0 0.0 0 0.0 0.0 0.0 0.0 0 0 0
18212 45472 0.0 0.0 0 0.0 0.0 0.0 0.0 0 0 0
21216 46336 0.0 0.0 0 0.0 0.0 0.0 0.0 0 0 0
24221 46688 0.0 0.0 0 0.0 0.0 0.0 0.0 0 0 0
27215 44672 0.0 0.0 0 0.0 0.0 0.0 0.0 0 0 0

….. And so on for many many more rows

At the moment what I do is manually cut & paste in the rows by highlighting the text all the way down to a row which begins with a value in the first column with the value 86938451 (which is 1 day from the start time listed at the top). This is 1 days worth of data. This all has to be done by highlighting in notepad then pasting into Ecxel. Next I remove all columns except for A & B as there is no useful data there.
Then I add in a column between A & B (which obviously becomes B with the NL data moving now to C) in this column (B) I use the following formula which then gives me the correct date & time of the reading.

=PRODUCT(A1/86400000+DATE(2007,12,4))

(Basically column A contains the amount of milliseconds after the ‘start time’ listed at the top of the document & the date I input is the date in 'start time')

This column is then formatted to show dd/mm/yyyy hh:mm:ss

Once this has been done I can then create a chart using columns B&C.

I then need to obtain the next days data which involves me copying from the next row in the text file down to a row starting with data which is 896398451 + 896398451 (approximately but the figure is not always exact)
This is then pasted into a new sheet and the above process repeated.

Now as you can see it’s a bit fiddly & what I really need to know is if I can automate any of the process to speed things along.

In an ideal world Id like to create a template which you load, it prompts you for the text file, retrieves the correct start date (either by user input or by reading it from the text at the start of the file) & then automatically pastes in 1 days worth of readings, including the actual date /time of reading by using my formula) It would then repeat the process for the rest of the data separating each into a new sheet.

Sorry for the length of this post but if anyone could let me know if I’m completely dreaming or if this is something I could either a) attempt to put together myself (by you kindly pointing me in the direction of anything which could get me started or b) pay a programmer to do (and give me estimates on roughly how long / much work it would be for a pro.

Thanks in advance

Dave

 

[Andrew Poulsom]

Can you explain why 86938451 (00:08:58.58) is one day from start time? Do you mean start again when it exceeds 86400000?

 

[random_noise]

Can you explain why 86938451 (00:08:58.58) is one day from start time? Do you mean start again when it exceeds 86400000?

Hi Andrew,

86938451 was the last value (when converted to the actual date & time using the formula) of that day - ie after the calculation it came out as 04/12/2007 23:59:58
The next value was in the next day after being converted.
Sorry I should have made it clearer that these values are only rough just how I work it out manually when cutting & pasting.

The main thing is that the sheets contain a days worth of data with the days determined only by the product of my formula (not necessarily always a value of 86938451)

I'm sure there is a better way of doing it? Possibly it by performing the calculation while importing the data & using a counter which says 'when current_date=(prev_date)+1 then create new sheet & start writing data there.' (if that makes sense?)

Hope that makes it clearer.

 

[Andrew Poulsom]

Sorry I still don't understand the 86938451. Using the formula:

=A1/86400000+DATE(2007,12,4)

I get the next day - 05/12/2007 00:08:58

 

[random_noise]

Sorry I still don't understand the 86938451. Using the formula:

=A1/86400000+DATE(2007,12,4)

I get the next day - 05/12/2007 00:08:58

Oops my mistake. It should be 86398451. Got the 3 & 9 wrong way round. Then copied it from the one I typo'd.

You're right it makes sense to use the value of 86400000.

 

[Andrew Poulsom]

See if this works for you:

Sub Test()
    Const OneDay As Long = 86400000
    Dim FileName As String
    Dim FileNum As Integer
    Dim ws As Worksheet
    Dim Data As String
    Dim StartDate As Date
    Dim r As Long
    Dim Days As Integer
    Dim Readings As Variant
'   *** Change path and file name to suit ***
    FileName = "C:\Temp\Readings.txt"
    FileNum = FreeFile
    Open FileName For Input As #FileNum
    Do While Not EOF(FileNum)
        Line Input #FileNum, Data
        If Left(Data, 12) = "# Start time" Then
            StartDate = DateValue(Mid(Data, InStr(1, Data, "(") + 5, 6) & Mid(Data, Len(Data) - 5, 5))
            Exit Do
        End If
    Loop
    Set ws = Worksheets.Add
    r = 1
    Days = 1
    Do While Not EOF(FileNum)
        Line Input #FileNum, Data
        If IsNumeric(Left(Data, 1)) Then
            Readings = Split(Data, vbTab)
            If Readings(0) > (OneDay * Days) Then
                With ws
                    .Columns(2).Insert
                    .Range("B1:B" & .Range("A65536").End(xlUp).Row).FormulaR1C1 = "=RC[-1]/86400000+DATE(" & Year(StartDate) & "," & Month(StartDate) & "," & Day(StartDate) & ")"
                End With
                Set ws = Worksheets.Add
                r = 1
                Days = Days + 1
            End If
            With ws
                .Cells(r, 1) = Readings(0)
                .Cells(r, 2) = Readings(1)
            End With
            r = r + 1
        End If
    Loop
    With ws
        .Columns(2).Insert
        .Range("B1:B" & .Range("A65536").End(xlUp).Row).FormulaR1C1 = "=RC[-1]/86400000+DATE(" & Year(StartDate) & "," & Month(StartDate) & "," & Day(StartDate) & ")"
    End With
    Close #FileNum
End Sub

 

[random_noise]

Wow Andrew! Thats brilliant, it works a treat.

Thank you so much for your help. I'm sure from here I may be able to make it into a nice pretty interface but you have no idea how much drudgery & time you have saved me from cutting & pasting!

I didn't expect to get code which would do the job and in such a short space of time as well. I owe you a drink sometime

Have a great Christmas.

Dave