Editing multiple .csv files a certain way

[Pixel1191]

Hello!

I am in need of a bit of automation help once again. Figured AutoIT's the way go. However, I haven't the foggiest where even to start.

I have a whole bunch of .csv files (165, to be exact) containing data from a power meter. Problem is, the original power meter program had a bug, which caused false data in those files. They now need to be edited.

This is the first 5 lines of one the files, to showcase what they look like (all in all, it's about 6000 lines per file)

timestamp;C0 Power;C0 Energy;C1 Power;C1 Energy;C2 Power;C2 Energy;C3 Power;C3 Energy;C4 Power;C4 Energy;C5 Power;C5 Energy;C6 Power;C6 Energy;C7 Power;C7 Energy
04.02.2016 00:00;1256;143256;1434;592536;507;845898;327;86607;552;573108;1037;1017895;9;57407;33;57755
04.02.2016 00:00;1256;143256;1438;592539;513;845902;327;86607;563;573110;1052;1017899;9;57407;33;57755
04.02.2016 00:01;1388;143257;1439;592542;526;845907;327;86607;557;573112;1062;1017904;9;57407;33;57755
04.02.2016 00:01;1388;143257;1435;592545;506;845911;325;86608;557;573115;1036;1017908;9;57407;33;57755

All the Cx Energy columns need to be edited; they need to be multiplied with specific values, a different one for each column. I can do it manually in Excel, however, that's gonna take forever and probably drive me insane in the process.

Is there a quicker way to do this with AutoIT and if so, how to go about it?

 

Greetings

[Melba23]

Pixel1191,

Read the file into an array and then modify the correct elements. This example shows what I mean - I have saved the data into file and then changed the elements that you say need modification:

expandcollapse popup

#include <Array.au3>
#include <File.au3>

$sFileName = "Test.csv"

; Create file for test
$sText = "timestamp;C0 Power;C0 Energy;C1 Power;C1 Energy;C2 Power;C2 Energy;C3 Power;C3 Energy;C4 Power;C4 Energy;C5 Power;C5 Energy;C6 Power;C6 Energy;C7 Power;C7 Energy" & @CRLF & _
"04.02.2016 00:00;1256;143256;1434;592536;507;845898;327;86607;552;573108;1037;1017895;9;57407;33;57755" & @CRLF & _
"04.02.2016 00:00;1256;143256;1438;592539;513;845902;327;86607;563;573110;1052;1017899;9;57407;33;57755" & @CRLF & _
"04.02.2016 00:01;1388;143257;1439;592542;526;845907;327;86607;557;573112;1062;1017904;9;57407;33;57755" & @CRLF & _
"04.02.2016 00:01;1388;143257;1435;592545;506;845911;325;86608;557;573115;1036;1017908;9;57407;33;57755"

$hFile = FileOpen($sFileName, $FO_OVERWRITE)
FileWrite($hFile, $sText)
FileClose($hFile)

; Multiplication values for each column
Local $aMod[] = [2, 3, 4, 5, 6, 7, 8, 9]

; Read file into array
Local $aArray
_FileReadToArray($sFileName, $aArray, $FRTA_NOCOUNT, ";")

_ArrayDisplay($aArray, "Original", Default, 8)

$iMaxRow = UBound($aArray, $UBOUND_ROWS) - 1

; Search for columns to modify
For $i = 0 To UBound($aArray, $UBOUND_COLUMNS) - 1
    ; Check column title
    If StringInStr($aArray[0][$i], "Energy") Then
        ; Get column index from title
        $iIndex = StringReplace(StringReplace($aArray[0][$i], "C", ""), " Energy", "")

        ConsoleWrite("Multiplying Column " & $aArray[0][$i] & " by " & $aMod[$iIndex] & @CRLF)

        ; And then modify each row with the correct value
        For $j = 1 To $iMaxRow
            $aArray[$j][$i] *= $aMod[$iIndex]
        Next
    EndIf
Next

_ArrayDisplay($aArray, "Modified", Default, 8)

You will then obviously need to rewrite the file using _FileWriteFrom Array. Please ask if you have any questions.

M23

 

Edited July 4, 2016 by Melba23
Added rewrite comment for clarity

[jchd]

Here again, merging 165 files containing 6000 lines on average is going to produce something circa 990,000 lines. Throwing that load to Excel may not be very practical. I'd make an SQLite database table from the original files, updating columns on the fly as needed. You may need an identifier to store where each row comes from, for instance if you gather data from 165 distinct energy units. Extracting meaningful raw or cooked data from the resulting SQL table is going to be a piece of cake. Also timestamps in this format are hard to work with; prefer ISO format (YYYY-MM-DD hh:mm).

That is iff you don't further need individual .CSV files. OTOH once everything loaded in an SQL table, it's pretty easy to extract exactly what you want in the format you need.

[Pixel1191]

Ah, but unfortunately I need all the single .csv

 

They need to be transferred back onto the powermeter after being corrected, because they're being read out by a script to be put into neat diagrams on a website for the customer.

 

The entire situation now changed somewhat. After telling me what would need to be done, they took things into their own hand. They corrected all the values, with Excel apparently, before sending the rest of the files to me. However, in that process, Excel messed up the formatting of the timestamp.

 

The datestamp is now DD.MM.YYYY (as in the example above) but it needs to be YYYY-MM-DD, that needs to be changed back to enable proper readouts later. Unfortunately, they didn't notice until it was too late, so no more access to the original files (unless I wanted to crawl back to where the darn meter is mounted and take out the SD card that holds the files, not interested in that) While that wouldn't be a problem for the numerous daily files (simple search and replace, since they only contain one date) but for the monthly and yearly files it becomes a bit of problem, as they contain a month/year worth of dates that need to be corrected....

[czardas]

Maybe this:

#include <Array.au3>

Local $aTest = [['timestamp','col1','col2'],['17.05.1853 time','',''],['01.02.2016 00000000 :P', '','']]
_ArrayDisplay($aTest, 'Before')

Local $iDateCol = 0

; the magic...
For $iRow = 1 To UBound($aTest) -1 ; don't bother modifying row 0
    $aTest[$iRow][$iDateCol] =  StringRegExpReplace($aTest[$iRow][$iDateCol], '(\d+)(\D)(\d+)(\D)(\d+)', '$5-$3-$1')
Next

_ArrayDisplay($aTest, 'After')

If you run this process again, it will swap the day and year back again.

Edited July 6, 2016 by czardas