Query txt file

[sirrox]

I have a data logger text file I would like to import into excel. This is one of 6 Data Loggers we have. So as you can imagine data adds up quickly.

The text file has comma delimited entries for a date and time, but the problem is that there are 5 logged data entries every 5 minutes. But each data entry is logged on a new line with the same date/time. This creates a problem when importing into excel as we end up with 5 times the lines when importing a textfile than we want.

Given the 5 minute recording interval and quarterly reporting, we are looking at 130,000 lin entries in a quarter. Double Excel's limitation of 65,000 lines. Yes, we're using Office 2000.

The File looks like this ... Date,Time,Tag,Value; (there are no column headers in the file)...

11/11/2010,12:00:00,41,12.767
11/11/2010,12:00:00,31,0.122
11/11/2010,12:00:00,1,0.4365
11/11/2010,12:00:00,21,1.3439
11/11/2010,12:00:00,11,0.230

Tag numbers would ideally be changed to a label.
41 = FLVOLTAGE
31 = FLTEMP
1 = FCDEPTH
21 = FLDEPTH
11 = FCTEMP

What I would like in excel is one row based on DATETIME (combined) with a seperate column for tag value.

DATETIME FCDEPTH FCTEMP FLDEPTH FLTEMP FLVOLTAGE

This would take us down to 26000 lines per quarter.

Ideally i would take this data from excel and import into an Access database. I was thinking one table per NODE. I would then use excel or access to generate the reports. Daily averages, min/max, monthly averages etc. for various tags.

Am I going about this the right way? I am thinking of Excel's line limitation and we'll be over it within 3 quarters if we store the data in excel.

Should Access even be used? Again, we're currently using Office 2000.

 

[Tinbendr]

Should Access even be used?

I would say, "Most definitely!"

There are numerous ways to use external data and only pull the data you need into Excel. Access, ADO, DAO, and even the text files.

How you want to use, manipulate, display the data is more important than where it's stored.

 

[Ruddles]

You can run an SQL crosstab query - like an Excel pivot table - directly against your CSV file and this will group the entries by date and time.

First make a copy of your CSV file and then create a new workbook. In VBA, create reference to the Microsoft ActiveX Data Objects Library (Tools > References).

Finally run this code. It will prompt you for the location of your datalogger file and then it will run a crosstab query which will - hopefully! - import your data grouped by date and time.

[FONT=Courier New][SIZE=1]Option Explicit[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]Public Sub DataLoggerCrossTabQuery()[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]  Dim sFilePath As String
  Dim sFileName As String
  Dim iSlash As Integer
  Dim bKillFile As Boolean
  
  Dim inFH As Integer
  Dim outFH As Integer
  Dim sRecord As String
  
  Dim ws As Worksheet
  
  Dim rsConn As ADODB.Connection
  Dim strConnect As String
  Dim rsData As ADODB.Recordset
  Dim strSQL As String
  Dim cResults As Range
  
  Dim dtStart As Date
  
  Set ws = ThisWorkbook.Sheets("Sheet1")
 
  sFileName = Application.GetOpenFilename( _
              FileFilter:="Comma-separated (CSV) files (*.csv), *.csv,Text files (*.txt), *.txt")
  If sFileName = "False" Then Exit Sub[/SIZE][/FONT]
[FONT=Courier New][SIZE=1]  dtStart = Now()
  
  iSlash = InStrRev(sFileName, "\")
  sFilePath = Left(sFileName, iSlash)
  sFileName = Mid(sFileName, iSlash + 1)
  bKillFile = False
  
[COLOR=green]  ' first make sure there are field names at the top of the input file
[/COLOR]  Close
  inFH = FreeFile()
  Open sFilePath & sFileName For Input As #inFH
  Line Input #inFH, sRecord
  If sRecord = "xDate,xTime,xType,xValue" Then
[COLOR=green]    ' it's okay - we have field names so we can use the file as it is
[/COLOR]  Else
[COLOR=green]    ' no field names so we need to make a copy of the file with field names
[/COLOR]    outFH = FreeFile()
    Open Environ("TEMP") & "\tempfile.csv" For Output As #outFH
    Print #outFH, "xDate,xTime,xType,xValue"
    Do Until EOF(inFH)
      Print #outFH, sRecord
      Line Input #inFH, sRecord
    Loop
    Print #outFH, sRecord
    Close outFH
[COLOR=green]    ' point to the location of our new input file and set the flag to delete it when finished
[/COLOR]    sFilePath = Environ("TEMP") & "\"
    sFileName = "tempfile.csv"
    bKillFile = True
  End If
  Close #inFH
    
  Set rsConn = New ADODB.Connection
  strConnect = "Provider=Microsoft.Jet.OLEDB.4.0;" _
             & "Data Source=" & sFilePath & ";" _
             & "Extended Properties=Text;"
  rsConn.Open strConnect
  
  If rsConn.State <> 1 Then
    MsgBox "Connection failed!" & Space(15), vbOKOnly + vbExclamation
    Exit Sub
  End If
 
[COLOR=green]  ' clear the worksheet and create column titles
[/COLOR]  ws.UsedRange.ClearContents
  ws.Range("A1:G1") = Array("Date", "Time", "FCDEPTH", "FCTEMP", "FLDEPTH", "FLTEMP", "FLVOLTAGE")
  ws.Range("A1:G1").Font.Bold = True
      
[COLOR=green]  ' this is our SQL cross-tab query
[/COLOR]  strSQL = "TRANSFORM First(xValue) AS TheValue " _
         & "SELECT xDate, xTime FROM " & sFileName & " " _
         & "GROUP BY xDate, xTime PIVOT xType;"[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1][COLOR=green]  ' connect to the CSV file and execute the SQL, then copy the recordset to the worksheet
[/COLOR]  Set rsData = New ADODB.Recordset
  rsData.Open strSQL, rsConn, 3, 1, 1
  Set cResults = ws.Range("A2")
  If Not rsData.EOF Then
    rsData.MoveLast
    rsData.MoveFirst
    cResults.CopyFromRecordset rsData
  Else
    MsgBox "No records found!" & Space(15), vbOKOnly + vbExclamation
    Exit Sub
  End If
  
[COLOR=green]  ' set the date & time formats
[/COLOR]  ws.Columns("A").NumberFormat = "dd/mm/yyyy"
  ws.Columns("B").NumberFormat = "hh:mm:ss"
  ws.Columns("A:G").EntireColumn.AutoFit
  ws.Activate
  ActiveWindow.ScrollRow = 1
  
  MsgBox "Done: " & CStr(rsData.RecordCount) & " records found" & Space(15) & vbCrLf & vbCrLf _
       & "Run time: " & Format(Now() - dtStart, "hh:nn:ss"), vbOKOnly + vbInformation
  
  rsData.Close
  
[COLOR=green]  ' only do this if we needed to make a copy of the input file
[/COLOR]  If bKillFile = True Then Kill sFilePath & sFileName
  
  Set rsData = Nothing
  Set rsConn = Nothing[/SIZE][/FONT]
[FONT=Courier New][SIZE=1][/SIZE][/FONT] 
[FONT=Courier New][SIZE=1]End Sub[/SIZE][/FONT]

If there are any problems, let me know.

 

[Ruddles]

I'd advise starting with a cut-down input file to begin with - maybe just a few dozen lines - just to check it's working okay.

Obviously, once you've got the data in Excel you can import it into Access fairly easily.

I believe some very similar VBA could be made to work directly from Access, skipping the Excel step completely.