add a string or any character in excel row used batch file

[Ghaty]

sorry for the inconvenience caused. actually it is csv file.

I've configured to read the column 3,5 and 7 to extract the Date,E.Time and Job from the files. The files without SN will cause the row shifted by one column. The parser cannot handle both.

Is it possible to add a string (or any character) in the barcode column for the file without the barcode? The samples as attached. Please advise.

this is the script but it's not working :
cd D:\FlexSPC\uScore
rename _*.csv
FOR /f "delims=" %%F IN ('DIR /a-d /b _*.csv') DO (RENAME "%%F" "Line12T%%F")
for /f "tokens=2 delims=:" %%a not in ('findstr "sal*"') do ( set str1=SALDUMMYSN ) echo.%str1%
move L*.CSV D:\FlexSPC\uScore\Linecd D:\FlexSPC\uScore

[aGerman]

Batch is not able to change Excel documents (only plain text files such as csv) and is also not able to remote control the Excel application.

Steffen

[Squashman]

You can do that in Vbscript, Jscript and Powershell.

[aGerman]

It doesn't seem that the first two lines have any "column". What I mean is that normally CSV data is separated by commas (maybe semi colons) if you open it in a text editor.
In order to know how to split the first two lines into several tokens we need to know how they look like in a text editor. Values may have a fix number of characters (padded by spaces). But this is something we can't discover from a screen shot.

Steffen

[Ghaty]

Below is the requirement:

The script will be placed in the folder with CSV files. These CSV files are supposed to contain string (with the prefix “SAL”) on the first column of the 2nd row. If the string is not present, the script will add in a Dummy string (“SALDUMMYSNN”) to the new file.

I have attached with the CSV sample file. Please help me to write a script. Thanks.

[aGerman]

Code: Select all

@echo off &setlocal
set "spc13=             "
for %%i in (*.csv) do (
  set "file=%%~i"
  for /f %%j in ('type "%%~i"^|find /c /v ""') do if %%j gtr 3 call :procfile %%j
)
exit /b


:procfile
setlocal EnableDelayedExpansion
<"!file!" (
  set /p "ln1=" &set /p "ln2=" &set /p "ln3="
  if "!ln2:~,14!" neq "!spc13! " (endlocal &exit /b)
  >"!file!~" echo(!ln1!
  >>"!file!~" echo(!spc13!SALDUMMYSN!ln2:~23!
  >>"!file!~" echo(!ln3!

  for /l %%k in (4 1 %1) do (
    set "ln=" &set /p "ln="
    if not defined ln (
      echo(
    ) else (
      for /f "tokens=1-7* delims=," %%l in ("!ln!") do >>"!file!~" echo(%%l,%%m,%%n,%%o,%%p,%%q,%%r,SALDUMMYSN,%%s
    )
  )
)
>nul move /y "!file!~" "!file!"
endlocal

The code will process every csv file in the current directory. It checks the absence any other characters than spaces in the first 14 characters of row2. If so, SALDUMMYSN will be included in the 2nd row as well as in the csv data beginning with row 4. The original file will be overwritten.

Steffen

[Ghaty]

Hi Steffen,

Thanks for the solution. It's working fine!!
Can set the 'SALDUMMYSN' value only in row 2 and ignore other rows. Because my CSV file is very huge (3000 KB)
and it's taking some time to generate the file. Thanks.

[aGerman]

Either way you have to write the file new. But yes it's possible to leave the rest of the file unchanged.

Code: Select all

@echo off &setlocal
set "spc13=             "
for %%i in (*.csv) do (
  set "file=%%~i"
  for /f %%j in ('type "%%~i"^|find /c /v ""') do if %%j gtr 3 call :procfile %%j
)
exit /b


:procfile
setlocal EnableDelayedExpansion
<"!file!" (
  set /p "ln1=" &set /p "ln2="
  if "!ln2:~,14!" neq "!spc13! " (endlocal &exit /b)
  >"!file!~" echo(!ln1!
  >>"!file!~" echo(!spc13!SALDUMMYSN!ln2:~23!
  >>"!file!~" more +2
)
>nul move /y "!file!~" "!file!"
endlocal

Steffen

[Squashman]

Because my CSV file is very huge (3000 KB)

That's barely over 2 floppy disks.

[Ghaty]

Hi Steffen, Thanks a lot.