Unione di file 
Monday, 3 November 2014, 16:04 - Informatica, Programmazione, VB Script
Dalla seguente richiesta:

Ho bisogno di aiuto su come eseguire delle operazioni su file CSV.
Scarico dal PLC i log di diversi sensori (e anche da diversi PLC).
Per ogni sensore ho un file appunto CSV strutturato come segue:
  VarName,TimeString,VarValue,Validity,Time_ms 

Di tutti questi campi mi servono unicamente: TimeString, VarValue 

Ho bisogno di creare un file unico dove vengono aggiunte le colonne dei valori di ogni sensore. 
Praticamente:
  TimeString,VarValue(VarName),VarValue(VarName),....


Soluzione in VBScript:

Option Explicit

Dim fso 'As FileSystemObject

Set fso = CreateObject("Scripting.FileSystemObject")
Call main()

Public Sub main()
Dim inFiles() 'As String
Dim outFN 'As String
Dim tCol 'As Integer
Dim oCol 'As Integer
Dim tDelta 'As Integer
Dim i 'As Integer
If Wscript.Arguments.Count > 1 Then
tCol = Wscript.Arguments(0) - 1
oCol = Wscript.Arguments(1) - 1
tDelta = Wscript.Arguments(2) + 0
outFN = Wscript.Arguments(3)
Redim inFiles(Wscript.Arguments.Count - 5)
For i = 4 To Wscript.Arguments.Count - 1
inFiles(i - 4) = Wscript.Arguments(i)
Next 'i
Call joinCsvH(inFiles, outFN, tCol, oCol, tDelta)
Else
Call wscript.echo("Sintassi:")
Call wscript.echo(" cscript /nologo h_merge.vbs tCol oCol tDelta output input1 input2...")
Call wscript.echo("")
Call wscript.echo("Parametri:")
Call wscript.echo(" tCol numero della colonna che contiene il tempo")
Call wscript.echo(" oCol numero della colonna di output")
Call wscript.echo(" tDelta intervallo in secondi nel quale raggruppare le righe")
Call wscript.echo(" output nome del file di output")
Call wscript.echo(" input* nome dei file di input")
Call wscript.echo("")
Call wscript.echo("Esempio:")
Call wscript.echo(" cscript /nologo h_merge.vbs 2 3 1 out.txt 1.txt 2.txt")
End If
End Sub

Private Sub joinCsvH(inFiles(), outFN, tCol, oCol, tDelta)
Dim i 'As Integer
Dim iUB 'As Integer 'Input UBound
Dim lowest 'As Integer 'Lowest time in buffer
Dim lowestI 'As Integer 'Lowest time file index
Dim iEmpt 'As Boolean 'Input buffer empty flag
Dim iFull() 'As Boolean 'Input buffer status
Dim iFile() 'As Object 'Input files
Dim iRow 'As String 'Input row
Dim iBuf() 'As Variant 'Input buffer
Dim oRow() 'As String 'Output buffer
Dim oFile 'As Object 'Output file
Dim oTime 'As Long 'Output time
iUB = UBound(inFiles)
ReDim iBuf(iUB) 'Prepare the input buffer
ReDim iFull(iUB) 'Prepare the input buffer status
For i = 0 To iUB
iFull(i) = False
Next 'i
ReDim oRow(iUB) 'Prepare the output buffer
For i = 0 To iUB
oRow(i) = ""
Next 'i
Call openFiles(inFiles, iFile) 'Open input files
Set oFile = fso.OpenTextFile(outFN, 2, True) 'Open the output file
oTime = "01/01/1970 00:00:00"
Do While True
iEmpt = True
lowestI = -1
lowest = ""
'Search for the lowest time in the input buffer
For i = 0 To iUB 'For each element in the input buffer
If Not iFull(i) Then 'If the element is empty
If Not iFile(i).AtEndOfStream Then 'If the file is not at the end
iRow = iFile(i).ReadLine() 'Read a row from the file
iBuf(i) = Split(iRow, ";") 'Put the row into the buffer
iFull(i) = True 'Mark this item as available
End If
End If
If iFull(i) Then 'If this item is available
iEmpt = False 'The input buffer is not empty
If lowestI < 0 Or iBuf(i)(tCol) < lowest Then
lowest = iBuf(i)(tCol)
lowestI = i
End If
End If
Next 'i
If iEmpt Then Exit Do 'Leave the loop if the input buffer is empty
'If elapsed is greater than threshold or the output overlaps
If UDateDiff(lowest, oTime) > tDelta Or oRow(lowestI) <> "" Then
If oTime <> "01/01/1970 00:00:00" Then 'Discard the first output
Call oWrite(oFile, oTime, oRow) 'Write the output
End If
oTime = lowest 'Update the output time
End If
oRow(lowestI) = iBuf(lowestI)(oCol) 'Peek the lowest item into the buffer
iFull(lowestI) = False 'Mark the peeked item as no longer available
Loop
If Not stringsEmpty(oRow) Then 'If the output buffer isn't empty
Call oWrite(oFile, oTime, oRow) 'Write the output
End If
Call oFile.close() 'Close the output file
Call closeFiles(iFile) 'Close input files
End Sub

Private Function stringsEmpty(a)
Dim i 'As Integer
For i = 0 To UBound(a)
If a(i) <> "" Then
stringsEmpty = False
Exit Function
End If
Next 'i
stringsEmpty = True
End Function

Private Sub oWrite(oFile, oTime, oRow)
Dim i 'As Integer
oFile.WriteLine(oTime & ";" & Join(oRow, ";")) 'Write time and columns
For i = 0 To UBound(oRow)
oRow(i) = "" 'Reset the output buffer
Next 'i
End Sub

Private Sub openFiles(inFiles(), iFiles)
Dim ub 'As Integer
Dim i 'As Integer
ub = UBound(inFiles)
ReDim iFiles(ub)
For i = 0 To ub
Set iFiles(i) = fso.OpenTextFile(inFiles(i), 1, False)
Next 'i
End Sub

Private Sub closeFiles(iFile())
Dim i 'As Integer
For i = 0 To UBound(iFile)
Call iFile(i).close()
Next 'i
End Sub

Private Function UDateDiff(d1, d2)
UDateDiff = DateDiff("s", d2, d1)
End Function

Esempio di utilizzo:
cscript /nologo h_merge.vbs 2 3 1 out.txt 1.txt 2.txt


File 1.txt:
xxx;31/10/2014 21:30:00;1A;xxx;xxx
xxx;31/10/2014 21:30:05;1B;xxx;xxx
xxx;31/10/2014 21:30:10;1C;xxx;xxx
xxx;31/10/2014 21:30:11;1D;xxx;xxx
xxx;31/10/2014 21:30:15;1E;xxx;xxx
xxx;31/10/2014 21:30:20;1F;xxx;xxx

File 2.txt:
xxx;31/10/2014 21:30:00;2A;xxx;xxx
xxx;31/10/2014 21:30:05;2B;xxx;xxx
xxx;31/10/2014 21:30:10;2C;xxx;xxx
xxx;31/10/2014 21:30:15;2D;xxx;xxx
xxx;31/10/2014 21:30:20;2E;xxx;xxx
xxx;31/10/2014 21:30:25;2F;xxx;xxx

File out.txt:
31/10/2014 21:30:00;1A;2A
31/10/2014 21:30:05;1B;2B
31/10/2014 21:30:10;1C;2C
31/10/2014 21:30:11;1D;
31/10/2014 21:30:15;1E;2D
31/10/2014 21:30:20;1F;2E
31/10/2014 21:30:25;;2F


Commenti

Aggiungi commento
Commento non disponibile per questa notizia.