Page 1 of 1
Simple batch script with text editing
Posted: Sunday 19 December 2021 13:18
by 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
Re: Simple batch script with text editing
Posted: Sunday 19 December 2021 16:39
by user4563
You would be better off doing this with a script in PowerShell
Re: Simple batch script with text editing
Posted: Sunday 19 December 2021 17:55
by Droll
user4563 wrote: ↑Sunday 19 December 2021 16:39
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
Re: Simple batch script with text editing
Posted: Sunday 19 December 2021 18:09
by user4563
What formats can you get that data in?
Re: Simple batch script with text editing
Posted: Sunday 19 December 2021 18:19
by Droll
user4563 wrote: ↑Sunday 19 December 2021 18:09
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
Re: Simple batch script with text editing
Posted: Sunday 19 December 2021 19:22
by 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 4146 times
Re: Simple batch script with text editing
Posted: Sunday 19 December 2021 21:30
by Droll
siklosi wrote: ↑Sunday 19 December 2021 19:20
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 4126 times
Not familiare with phyton, but gave it a try
Re: Simple batch script with text editing
Posted: Sunday 19 December 2021 21:33
by Droll
user4563 wrote: ↑Sunday 19 December 2021 19:22
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
Re: Simple batch script with text editing
Posted: Sunday 19 December 2021 22:14
by 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
Re: Simple batch script with text editing
Posted: Sunday 19 December 2021 22:31
by Droll
user4563 wrote: ↑Sunday 19 December 2021 22:14
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
Well it doesn't work here, forward or backward \/
Re: Simple batch script with text editing
Posted: Monday 20 December 2021 1:20
by user4563
Can you post the error you are now getting?
Re: Simple batch script with text editing
Posted: Monday 20 December 2021 22:40
by Droll
siklosi wrote: ↑Monday 20 December 2021 7:46
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 4056 times
Re: Simple batch script with text editing
Posted: Tuesday 21 December 2021 1:45
by user4563
Run the script in CMD or PowerShell like this:
If you want to run in the Python shell window:
Code: Select all
exec(open('C:/test/script.py').read())
Re: Simple batch script with text editing
Posted: Tuesday 21 December 2021 7:35
by Droll
user4563 wrote: ↑Tuesday 21 December 2021 1:45
Run the script in CMD or PowerShell like this:
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
Re: Simple batch script with text editing
Posted: Tuesday 21 December 2021 10:07
by 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
Re: Simple batch script with text editing
Posted: Tuesday 21 December 2021 18:06
by 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]
Arne Kjetil
Re: Simple batch script with text editing
Posted: Tuesday 21 December 2021 20:18
by Droll
siklosi wrote: ↑Tuesday 21 December 2021 18:39
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
Re: Simple batch script with text editing
Posted: Tuesday 21 December 2021 21:18
by Droll
siklosi wrote: ↑Tuesday 21 December 2021 20:46
yes, just add for example line[0].replace("." , ",")
May I ask where
, not very good with scrips
Arne Ketill
Re: Simple batch script with text editing
Posted: Tuesday 21 December 2021 21:32
by 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