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

Discussion forum for all Windows batch related topics.

Moderator: DosItHelp

Post Reply
Message
Author
Ghaty
Posts: 4
Joined: 18 Jan 2017 21:29

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

#1 Post by Ghaty » 18 Jan 2017 21:59

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
Attachments
Capture.JPG
Capture.JPG (71.15 KiB) Viewed 6370 times
Last edited by Ghaty on 19 Jan 2017 20:25, edited 1 time in total.

aGerman
Expert
Posts: 4654
Joined: 22 Jan 2010 18:01
Location: Germany

Re: add a string or any character in excel used batch file

#2 Post by aGerman » 19 Jan 2017 03:01

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
Expert
Posts: 4465
Joined: 23 Dec 2011 13:59

Re: add a string or any character in excel used batch file

#3 Post by Squashman » 19 Jan 2017 07:31

You can do that in Vbscript, Jscript and Powershell.

aGerman
Expert
Posts: 4654
Joined: 22 Jan 2010 18:01
Location: Germany

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

#4 Post by aGerman » 20 Jan 2017 11:09

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
Posts: 4
Joined: 18 Jan 2017 21:29

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

#5 Post by Ghaty » 26 Jan 2017 03:09

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.
Attachments
sample.7z
SAL2101C0A1.csv - with data
_ID1001.csv - without data
(1.32 KiB) Downloaded 239 times

aGerman
Expert
Posts: 4654
Joined: 22 Jan 2010 18:01
Location: Germany

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

#6 Post by aGerman » 27 Jan 2017 09:11

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
Posts: 4
Joined: 18 Jan 2017 21:29

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

#7 Post by Ghaty » 31 Jan 2017 22:17

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
Expert
Posts: 4654
Joined: 22 Jan 2010 18:01
Location: Germany

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

#8 Post by aGerman » 01 Feb 2017 08:30

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
Expert
Posts: 4465
Joined: 23 Dec 2011 13:59

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

#9 Post by Squashman » 01 Feb 2017 08:49

Ghaty wrote: Because my CSV file is very huge (3000 KB)

That's barely over 2 floppy disks. :lol:

Ghaty
Posts: 4
Joined: 18 Jan 2017 21:29

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

#10 Post by Ghaty » 07 Feb 2017 19:14

Hi Steffen, Thanks a lot.

Post Reply