Simple batch script with text editing Topic is solved

[Droll]

I got an output file from a testing machine that I need to clean up a bit before importing into some spreadsheet..

Code: Select all

"VarName"   "TimeString"    "VarValue"  "Validity"  "Time_ms"
"VALUES_A"  "2021-10-25 09:57:32"   10500   1   44494414962.465279
"VALUES_B"  "2021-10-25 09:57:32"   59.282589   1   44494414962.488426
"VALUES_C"  "2021-10-25 09:57:32"   7.522789    1   44494414962.488426
"VALUES_D"  "2021-10-25 09:57:32"   7.880400    1   44494414962.500000
"VALUES_E"  "2021-10-25 09:57:32"   8.337399    1   44494414962.500000
"VALUES_F"  "2021-10-25 09:57:32"   9.523390    1   44494414962.534721
"VALUES_G"  "2021-10-25 09:57:32"   16.065742   1   44494414962.534721
"VALUES_H"  "2021-10-25 09:57:32"   18.799999   1   44494414962.546295
"VALUES_I"  "2021-10-25 09:57:32"   "2013-03-14 21:30:32"   1   44494414962.546295
"VALUES_A"  "2021-10-25 09:57:52"   10501   1   44494414962.465279
"VALUES_B"  "2021-10-25 09:57:52"   60.282589   1   44494414962.488426
"VALUES_C"  "2021-10-25 09:57:52"   7.622789    1   44494414962.488426
"VALUES_D"  "2021-10-25 09:57:52"   7.890400    1   44494414962.500000
"VALUES_E"  "2021-10-25 09:57:52"   7.337399    1   44494414962.500000
"VALUES_F"  "2021-10-25 09:57:52"   10.523390   1   44494414962.534721
"VALUES_G"  "2021-10-25 09:57:52"   17.065742   1   44494414962.534721
"VALUES_H"  "2021-10-25 09:57:52"   19.799999   1   44494414962.546295
"VALUES_I"  "2021-10-25 09:57:52"   "2013-03-14 21:30:32"   1   44494414962.546295

And I want it to be something like this

Code: Select all

2021-10-25 09:57:32, 10500, 59.282589, 7.522789, 7.880400, 8.337399, 9.523390, 16.065742, 18.799999
2021-10-25 09:57:32, 10501, 60.282589, 7.622789, 7.890400, 7.337399, 10.523390, 17.065742, 19.799999

Found sommething simmulair searching the net, here should be possible to edit the parameters to fit my text ? ..

Code: Select all

@echo off
setlocal enableDelayedExpansion
for %%F in (*.txt) do (
  set "out="
  set "i=x"
  > "%%~nF.csv" (
    for /f usebackq^ tokens1^= delims^="*"^ "*"^  %%A in ("%%F") do (
      if %%A neq !i! (
        if defined out echo !out!"
        set /a "i=%%A, j=0"
        set "out=!i! 
      ) else (
        set /a j+=1
        if !j! leq 3 (set "out=!out!.%%B j") else set "out=!out!.%%C"
      )
    )
    if defined out echo !out!"
  )
)

And of coarse I can't make it work, carpenter not a programmer

AND I know ther's pleny of script people here

Arne Kjetil

[user4563]

You would be better off doing this with a script in PowerShell

[Droll]

You would be better off doing this with a script in PowerShell

Maybe, got any exampels ?.
I has to be run on a regulary basis to convert files..

Arne Kjetil

[user4563]

What formats can you get that data in?

[Droll]

What formats can you get that data in?

It comes in a simple TXT file, see example at the top..

Code: Select all

"VarName"   "TimeString"    "VarValue"  "Validity"  "Time_ms"
"VALUES_A"  "2021-10-25 09:57:32"   10500   1   44494414962.465279
"VALUES_B"  "2021-10-25 09:57:32"   59.282589   1   44494414962.488426
"VALUES_C"  "2021-10-25 09:57:32"   7.522789    1   44494414962.488426
"VALUES_D"  "2021-10-25 09:57:32"   7.880400    1   44494414962.500000
"VALUES_E"  "2021-10-25 09:57:32"   8.337399    1   44494414962.500000
"VALUES_F"  "2021-10-25 09:57:32"   9.523390    1   44494414962.534721
"VALUES_G"  "2021-10-25 09:57:32"   16.065742   1   44494414962.534721
"VALUES_H"  "2021-10-25 09:57:32"   18.799999   1   44494414962.546295
"VALUES_I"  "2021-10-25 09:57:32"   "2013-03-14 21:30:32"   1   44494414962.546295

This is ONE reading, it will continue with the same pattern ( Values_A > I, then back to A > .... )

Output can be txt,csv, ??

Arne Kjetil

[user4563]

Since you can only get the raw data in txt and it's not a uniform delimiter (quotes as well as tab here), you are going to have to parse through it. I found a script online so will this work?

Screenshot 2021-12-19 131500.jpg (234.1 KiB) Viewed 2849 times

[Droll]

Here is python3 script that should do the job.
***

save as script.py, put data in indata.txt and run with python3 script.py... you will get outdata.txt with formatted text

error.jpg (199.93 KiB) Viewed 2829 times

Not familiare with phyton, but gave it a try

[Droll]

Since you can only get the raw data in txt and it's not a uniform delimiter (quotes as well as tab here), you are going to have to parse through it. I found a script online so will this work?
**

Maybe, maybe it can be changed to be even more precise ?, what kind of script ?

Arne Kjetil

[user4563]

I tried the python script @siklosi posted above and it gives you your comma delimited output you are looking for. In the script with python on windows with the path you have to use forward slashes ie

Code: Select all

C:/Path/Path/sample.txt

[Droll]

I tried the python script @siklosi posted above and it gives you your comma delimited output you are looking for. In the script with python on windows with the path you have to use forward slashes ie

Code: Select all

C:/Path/Path/sample.txt

Well it doesn't work here, forward or backward \/

[user4563]

Can you post the error you are now getting?

[Droll]

why don't you try putting script in same folder C:/test and using it without path... open('indata.txt') as f:

backslash is escape character so when you need real backslash you have to use double like c:\\test\\indata.txt

Everything are in the same folder...

And for some reason it works, and in the next moment it doesn't work...
Got this error "SyntaxError: unexpected character after line continuation character"

Tested on 2 different machines, one Win7 and one Win10 ..

error_1.jpg (78.78 KiB) Viewed 2759 times

[user4563]

Run the script in CMD or PowerShell like this:

Code: Select all

python.exe "C:\test\script.py"

If you want to run in the Python shell window:

Code: Select all

exec(open('C:/test/script.py').read())

[Droll]

Run the script in CMD or PowerShell like this:

Code: Select all

python.exe "C:\test\script.py"

If you want to run in the Python shell window:

Code: Select all

exec(open('C:/test/script.py').read())

Still the same, somtimes it works, somtime it doesn't ...

But when it works, it's almost good , just missing the most important parameter .. the first ...

Code: Select all

2021-10-25 09:57:32, 59.282589, 7.522789, 7.880400, 8.337399, 9.523390, 16.065742, 18.799999
2021-10-25 09:57:52, 60.282589, 7.622789, 7.890400, 7.337399, 10.523390, 17.065742, 19.799999

Should have been

Code: Select all

2021-10-25 09:57:32, 10500, 59.282589, 7.522789, 7.880400, 8.337399, 9.523390, 16.065742, 18.799999
2021-10-25 09:57:52, 10501, 60.282589, 7.622789, 7.890400, 7.337399, 10.523390, 17.065742, 19.799999

Below is an actual example of my start file, at the moment 3300 lines and growing...
Only difference are the first line, was simplyfied in my first example..

Code: Select all

"VarName"	"TimeString"	"VarValue"	"Validity"	"Time_ms"
"FLASHVALUES_S/N"	"2021-10-25 09:57:32"	10500	1	44494414962.465279
"FLASHVALUES_PMPP"	"2021-10-25 09:57:32"	59.282589	1	44494414962.488426
"FLASHVALUES_VMPP"	"2021-10-25 09:57:32"	7.522789	1	44494414962.488426
"FLASHVALUES_IMPP"	"2021-10-25 09:57:32"	7.880400	1	44494414962.500000
"FLASHVALUES_ISC"	"2021-10-25 09:57:32"	8.337399	1	44494414962.500000
"FLASHVALUES_VOC"	"2021-10-25 09:57:32"	9.523390	1	44494414962.534721
"FLASHVALUES_EFF"	"2021-10-25 09:57:32"	16.065742	1	44494414962.534721
"FLASHVALUES_TC"	"2021-10-25 09:57:32"	18.799999	1	44494414962.546295
"FLASHVALUES_DATE"	"2021-10-25 09:57:32"	"2013-03-14 21:30:32"	1	44494414962.546295
"FLASHVALUES_S/N"	"2021-10-25 10:00:05"	10050	1	44494416727.615738
"FLASHVALUES_PMPP"	"2021-10-25 10:00:05"	57.828953	1	44494416727.638893
"FLASHVALUES_VMPP"	"2021-10-25 10:00:05"	7.338327	1	44494416727.638893
"FLASHVALUES_IMPP"	"2021-10-25 10:00:05"	7.880400	1	44494416727.685188
"FLASHVALUES_ISC"	"2021-10-25 10:00:05"	8.337399	1	44494416727.696754
"FLASHVALUES_VOC"	"2021-10-25 10:00:05"	9.338927	1	44494416727.696754
"FLASHVALUES_EFF"	"2021-10-25 10:00:05"	15.671803	1	44494416727.696754
"FLASHVALUES_TC"	"2021-10-25 10:00:05"	18.799999	1	44494416727.708336
"FLASHVALUES_DATE"	"2021-10-25 10:00:05"	"2013-03-14 21:33:05"	1	44494416727.708336

[Droll]

Code: Select all

"FLASHVALUES_S/N"	"2021-10-25 09:57:32"	10500	1	44494414962.465279

"FLASHVALUES_S/N" TAB "2021-10-25 09:57:32" TAB 10500 TAB 1 TAB 44494414962.465279

10500 missing ...

The original file uses tabs, not space... could this have something to do with the code ?

Code: Select all

with open('data.txt','r') as f:
    lines = f.readlines()
data=''
newline=''
for line in lines:
    line=line.replace("\"","").split()
    if line[0]=='FLASHVALUES_S/N':
        newline=line[1]+" "+line[2]
    elif line[0].startswith('FLASHVALUES') and line[0] !='FLASHVALUES_DATE':
        newline=newline+", "+line[3]
    if line[0]=='FLASHVALUES_DATE':
        data=data+newline+"\n"
print (data)
with open('Flash.txt', 'w') as f:
    f.write(data)

On a test file with spaces this one works fine.... 40000 lines , BUT there's one field missing, and it doesn't work on the real file

Recap:

• Missing the value from the first line "FLASHVALUE_S/N".
  Doesn't work on the real file, as it uses TAB instead of space.

Arne Kjetil

[Droll]

Still no go, so have attached a sample of the file itself..

As for the missing part I figured out that part
newline=line[3]+", "+line[1]+" "+line[2]

data2.txt

(5.23 KiB) Downloaded 85 times

Arne Kjetil

[Droll]

this is utf-16 text file and that is why it was not possible to parse it.

Code: Select all

with open('data2.txt','r', encoding="utf-16") as f:
    lines = f.readlines()
data=''
newline=''
for line in lines:
    line=line.replace("\"","").split()
    if line:
        if line[0]=='FLASHVALUES_S/N':
            newline=line[1]+" "+line[2]+", "+line[3]
        elif line[0].startswith('FLASHVALUES') and line[0] !='FLASHVALUES_DATE':
            newline=newline+", "+line[3]
        if line[0]=='FLASHVALUES_DATE':
            data=data+newline+"\n"
print (data)
with open('Flash.txt', 'w') as f:
    f.write(data)

Now it works , thank you for the good help

Just as a sidenote... is it possible to get the script to change the dots (.) to comas (,) ??
From 3.14 to 3,14 ?

Easier to to use in Ecxel beacuse we uses , here..

Arne Kjetil

[Droll]

yes, just add for example line[0].replace("." , ",")

May I ask where , not very good with scrips


Arne Ketill

[Droll]

Code: Select all

with open('Flahlist0.txt','r', encoding="utf-16") as f:
    lines = f.readlines()
    
data=''
newline=''
for line in lines:
    line=line.replace("." , ",")
    line=line.replace("\"","").split()
    
    if line:
        if line[0]=='FLASHVALUES_S/N':
            
            newline=line[3]+"; "+line[1]+" "+line[2]
        elif line[0].startswith('FLASHVALUES') and line[0] !='FLASHVALUES_DATE':
            newline=newline+"; "+line[3]
        if line[0]=='FLASHVALUES_DATE':
            data=data+newline+"\n"
print (data)
with open('Flash.csv', 'w') as f:
    f.write(data)

Figured it out, but it cased some other problems for Excel, so I had to make another change too
Change the "," to ";" in the output file.... so now it works as intended, at least for now


Arne Kjetil