0

I'm having trouble with a kinda specific problem.

The monitoring software (used for robots in the manufacturing halls) used by the company i am working for, generates a log file (.sdat) every 15 minutes. The content of a log file looks like this:

The syntax: Time;machine;status

13:53:23;KP85;ms:9999

13:53:49;KP85;ms:3

13:54:54;KP85;ms:4

14:06:04;KP85;ms:9999

13:51:38;Robot1;ms:9999

etc...

I've managed to concatenate all the log files into one big file, including the filename at the start of each new row, like this:

The syntax: Filename:Time;Machine;Status

01-03-2016-00-20.sdat:0:07:40;KP65;ms:3

01-03-2016-00-20.sdat:0:09:09;KP65;ms:4

01-03-2016-00-20.sdat:0:09:11;KP65;ms:3

01-03-2016-00-20.sdat:0:09:13;KP65;ms:4

etc..

The reason I did this is because i need the time as well as the date(which is included in the filename) a certain status for a machine has been logged. However, if i import this into SQL management studio, it recognizes 3 columns instead of 4, because the filename and first column(time) are separated by a ':' instead of a ';'. I tried solving it with an SQL Query, separating the date and time with a LEFT() and RIGHT(), but guess what: the time field format changes when the time switches from 9:59:59 to 10:00:00, creating an extra character for the time field (so the data in the column would look like this ':9:59:59', which isn't a valid time field). Perhaps it could be done with SQL but it just seems to me like it would take too much complexity in the SQL code for such a small problem.

So at this point, i thought it would be better to tackle this problem early on; within the batch file which generates the large file, so Management Studio does recognize 4 instead of 3 columns. This is how my .bat file looks like at the moment:

@echo off findstr "^" *.sdat >output.txt

What do i have to do to get this right?

Thanks in advance,

Mike Sohns

Mike
  • 19
  • 6

2 Answers2

0

Given that all the log files you are concatenating have the sdat extension, you have to replace sdat: for sdat; in order to have a ; separated CSV.

To achieve this, you can use the batch script in this other answer(How to replace substrings in windows batch file), that replaces substrings in a text file using a batch script.

Community
  • 1
  • 1
Doliveras
  • 1,794
  • 2
  • 14
  • 30
  • is it possible to search and replace in text that is being generated(the filename at the start of each row) by the same batch file? – Mike Apr 08 '16 at 14:57
0
@echo off
(for %%f in (*.sdat) do (
  for /f "delims=" %%a in (%%f) do @echo %%f;%%a
))>output.txt

NOTE: this will swallow empty lines, but I think in your case that's not a problem. If yes, the code can be adapted.

Stephan
  • 53,940
  • 10
  • 58
  • 91
  • empty lines don't matter, but it cannot generate output because it can't find %%i – Mike Apr 08 '16 at 14:58
  • Alright, thanks. will try it on monday when i'm back at work! – Mike Apr 08 '16 at 15:56
  • Thanks Stephan, your code works! It now generates lines like this: 01-03-2016-00-20.sdat;0:09:13;KP65;ms:4 which is exactly what i was looking for. However, it does take a lot more time to generate the file, but thanks! – Mike Apr 11 '16 at 06:45