Jump to content

Editing multiple .csv files a certain way

Recommended Posts


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?



Link to comment
Share on other sites

  • Moderators


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:

#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)

; 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]

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

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



Edited by Melba23
Added rewrite comment for clarity

Public_Domain.png.2d871819fcb9957cf44f4514551a2935.png Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind

Open spoiler to see my UDFs:


ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area


Link to comment
Share on other sites

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.

This wonderful site allows debugging and testing regular expressions (many flavors available). An absolute must have in your bookmarks.
Another excellent RegExp tutorial. Don't forget downloading your copy of up-to-date pcretest.exe and pcregrep.exe here
RegExp tutorial: enough to get started
PCRE v8.33 regexp documentation latest available release and currently implemented in AutoIt beta.

SQLitespeed is another feature-rich premier SQLite manager (includes import/export). Well worth a try.
SQLite Expert (freeware Personal Edition or payware Pro version) is a very useful SQLite database manager.
An excellent eBook covering almost every aspect of SQLite3: a must-read for anyone doing serious work.
SQL tutorial (covers "generic" SQL, but most of it applies to SQLite as well)
A work-in-progress SQLite3 tutorial. Don't miss other LxyzTHW pages!
SQLite official website with full documentation (may be newer than the SQLite library that comes standard with AutoIt)

Link to comment
Share on other sites

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....

Link to comment
Share on other sites

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')

_ArrayDisplay($aTest, 'After')

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

Edited by czardas
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

  • Recently Browsing   0 members

    • No registered users viewing this page.
  • Create New...