Insert text into column b csv file

[Ken]

Can someone help me... Is there a way using a batch file to input text into column B in a csv file. I have dates in column A and I want the inserted text to goto column B.

Thank you
Ken

[trebor68]

You can see this in following link

http://www.dostips.com/forum/viewtopic.php?f=3&t=2156

The row with letter A will overwrite a file.
The other echo commands are writing a new row at the end of the file.

I will hope that will help you.

[Ken]

I looked at the Link Posted Output to excel/csv file but The date will always be in column A and my input text needs to start on the same line as the date in column A.
This is what it is doing.
8/29/2011
1 2 3 4 5 6 etc...
8/29/2011
1 2 3 4 5 6

This is the way I want it to look.
8/29/2011 1 2 3 4 5 6
8/29/2011 1 2 3 4 5 etc.

Thanks again for your help
Ken

[ghostmachine4]

Use something better to process files instead of batch.. If you want native solution, you can use vbscript (or powershell),

Code: Select all

Set objFS = CreateObject( "Scripting.FileSystemObject" )
strFile = WScript.Arguments(0)
Set objFile = objFS.OpenTextFile(strFile)
Do Until objFile.AtEndOfStream
   strLine= objFile.ReadLine
        WScript.Echo strLine & " " & "test"
Loop
objFile.Close

Code: Select all

C:\test> cscript //nologo myscript.vbs file

[Ken]

Thanks for the reply... I need it in a batch format. Other people will be accessing the file placed on there desktops. The CSV file has changing dates in column A. When they input a line it needs to line up with the dates already in file on the network (line B1, B2, B3 etc...) They may not have powershell installed. Thanks again

[ghostmachine4]

Thanks for the reply... I need it in a batch format. Other people will be accessing the file placed on there desktops. The CSV file has changing dates in column A. When they input a line it needs to line up with the dates already in file on the network (line B1, B2, B3 etc...) They may not have powershell installed. Thanks again

It is not powershell, it is vbscript. vbscript is present on Win9x and above, so there is not need to download anything.... just put the command inside a text file and name it whatever.bat (its called a batch file after that).

[Ken]

Ok... I did not understand, does this script need to be inserted before the current commands are run?

I guess I am not understanding this script. When I execute it as a batch file I get several errors... Could you give me a little example of how I should be using this.

Thank you very much for your time.

Errors:
C:\Documents and Settings\Ken\Desktop\Test Batch>Set objFS = CreateObject( "Scripting.FileSystemObject" )

C:\Documents and Settings\Ken\Desktop\Test Batch>strFile = WScript.Arguments(0)
'strFile' is not recognized as an internal or external command,
operable program or batch file.

C:\Documents and Settings\Ken\Desktop\Test Batch>Set objFile = objFS.OpenTextFile(test.csv)

C:\Documents and Settings\Ken\Desktop\Test Batch>Do Until objFile.AtEndOfStream
'Do' is not recognized as an internal or external command,
operable program or batch file.

C:\Documents and Settings\Ken\Desktop\Test Batch>strLine= objFile.ReadLine
'strLine' is not recognized as an internal or external command,
operable program or batch file.

C:\Documents and Settings\Ken\Desktop\Test Batch>WScript.Echo strLine & " " & "test"
'WScript.Echo' is not recognized as an internal or external command,
operable program or batch file.
'" "' is not recognized as an internal or external command,
operable program or batch file.
'"test"' is not recognized as an internal or external command,
operable program or batch file.

C:\Documents and Settings\Ken\Desktop\Test Batch>Loop
'Loop' is not recognized as an internal or external command,
operable program or batch file.

C:\Documents and Settings\Ken\Desktop\Test Batch>objFile.Close
'objFile.Close' is not recognized as an internal or external command,
operable program or batch file.

C:\Documents and Settings\Ken\Desktop\Test Batch>pause

[A_Bobby]

Try this

echo ,CellB1 > test2.csv

I know it looks absurdly simple. Hope I am not missing anything!

[trebor68]

Please excuse my first post, where I had a misconception.

Here is the correct posting.

The file Test.bat

Code: Select all

@echo off
setlocal ENABLEEXTENSIONS ENABLEDELAYEDEXPANSION
if not exist Test1.csv for %%a in (1 2 3 4) do echo "A%%a">>Test1.csv
::
if exist Test2.csv del Test2.csv
set row=0
for /f "tokens=*" %%d in (Test1.csv) do (
  set /a row+=1
  call :eingabe !row!
  echo %%d,"!newcell!">>Test2.csv
)
:: (del Test1.csv) & (ren Test2.csv Test1.csv)
goto :eof
:eingabe
set "newcell="
if not %1#==2# (
  set /p newcell=Value for row %1: 
  if !newcell!#==# set newcell=B%1
)
goto :eof

The row 3 in the code will make the first file with cell A1 to A4.
With the other rows make input value for the cell B1 to B4, with reading the first file.
The block ":eingabe" is for making the value for the cell.

[Ken]

Wow... You are GREAT! This is the direction I want to go. Great Job Thank You!!
Question:
It starts in Cell B1 and skips Cell B2 and then continues without issue.
Example 8/28/2011 B1
8/29/2011 <-- Blank cell
8/30/2011 B2
8/31/2011 B3
Can I correct this (where in code) or is this a drawback?
And may I ask one more question? If I use an array for input using 10 fields going across what and where can I add to your code to extend these fields?

Example of what I am trying to do: each () is Cell A1, B1, C1, D1 etc...

(8/29/2011),(8:30am),(Persons Name),(address),(zipcode) etc ...

Would I use newcell then add another set /p name=value for row %1:
if !name!#==# set newcell=C%1 ) - Just guessing

Thank you again for the help

[ghostmachine4]

Ok... I did not understand, does this script need to be inserted before the current commands are run?

I guess I am not understanding this script. When I execute it as a batch file I get several errors... Could you give me a little example of how I should be using this.

Thank you very much for your time.

you put these vbscript statements inside a file and save it as myscript.vbs (for example)

Code: Select all

Set objFS = CreateObject( "Scripting.FileSystemObject" )
strFile = WScript.Arguments(0)
Set objFile = objFS.OpenTextFile(strFile)
Do Until objFile.AtEndOfStream
   strLine= objFile.ReadLine
        WScript.Echo strLine & " " & "test"
Loop
objFile.Close

They are not batch commands/statements ok.?

then on the command line : just type

Code: Select all

c:\> cscript //nologo myscipt.vbs file

If you want to put the command into a batch file, just open another new file using wordpad or your favourite editor, type the cscript command in and save as mybatch.bat. Simple as that.

[trebor68]

In the block ":eingabe" you can define routines as you want to do it. I've done through the command if input for B2 as an empty field.

Here for all cell B1 to B4

Code: Select all

 ....

:eingabe
set "newcell="

set /p newcell=Value for row %1:
if !newcell!#==# set newcell=B%1

goto :eof

To understand the word "Eingabe" is German in English "Input".

[trebor68]

Here the code for input 10 value for a row.

Code: Select all

@echo off
setlocal ENABLEEXTENSIONS ENABLEDELAYEDEXPANSION
set dat=TestFile.csv
::
cls
echo.
echo  Input the value for ten fields for any row.
echo.
:input
echo.
for /l %%a in (1,1,10) do set "cell%%a="

set /p cell1= Input the date: 
if %cell1%#==t# set cell1=%date%
if %cell1%#==T# set cell1=%date%

set /p cell2= Input the time: 

set /p cell3= Input the persons name: 
set /p cell4= Input the address: 
set /p cell5= Input the zip-code: 
set /p cell6= Input field 6: 
set /p cell7= Input field 7: 
set /p cell8= Input field 8: 
set /p cell9= Input field 9: 
set /p cell10= Input field 10: 

echo "%cell1%","%cell2%","%cell3%","%cell4%","%cell5%","%cell6%","%cell7%","%cell8%","%cell9%","%cell10%">>%dat%

echo.
set /p andnow= Add a line to enter? (Y/N) 
if %andnow%#==y# goto :input
if %andnow%#==Y# goto :input

But, why you not input this directly in a excel file.

[Ken]

Thank you Trebor68 for all your help... The code for input 10 value is GREAT!!!, but what I need is to skip Columns A and B which already have a (Date - Column A) and a (Time - Column B) in them. So in (Column C) other people would enter Name, (Column D) address, (Column E) zipcode etc... Up to 10 fields going across the columns. They only need to enter one set of (fields) at a time then be asked if they want to enter another using the next date.

Example of what I am trying to do
(col A) , (col B) <-- Already there
8/29/2011 08:30
(col C - Name),(col D - Address),(col E - City),(col F - Zipcode) etc...
Ken , 1234 Smith St , Dallas , 75235 etc...

From Batch -- Input another Y or N - If they choose Y it would fall on the next line in (Column C) Enter Name... etc

Thank You again.

[trebor68]

Bevor you will start this batch, make a file "TestFile.csv" with value in column A and B.
Here a example:

Code: Select all

"8/29/2011","08:30" 
"8/29/2011","09:30" 
"8/29/2011","10:30" 

Here the solution:

Code: Select all

@echo off
cls
setlocal ENABLEEXTENSIONS ENABLEDELAYEDEXPANSION
set dat=TestFile.csv
set tmpdat=TestFile2.csv
if not exist %dat% goto :error1
set row=0
set eing=n
::
for /f "tokens=1,2* delims=," %%d in (%dat%) do (
  for /l %%s in (3,1,10) do set cell%%s=""
  set cell1=%%d
  set cell2=%%e
  set /a row+=1
  set help=0
  if %%f#==# set /a help+=1
  if %%f=="" set /a help+=1
  if !eing!==y set help=0
  if not !help!==0 (
    call :input !row! %%d %%e
    echo %%d,%%e,"!cell3!","!cell4!","!cell5!","!cell6!","!cell7!","!cell8!","!cell9!","!cell10!">>%tmpdat%
    set eing=y
  ) else echo %%d,%%e,%%f>>%tmpdat%
)
del %dat%
ren %tmpdat% %dat%
goto :eof
::
:error1
echo.
echo ERROR - File %dat% is not exist.
goto :eof
:input
echo  Date: %~2    Time: %~3
set /p "cell3=Name    : "
set /p "cell4=Address : "
set /p "cell5=City    : "
set /p "cell6=ZIP-code: "
set /p "cell7= value7 : "
set /p "cell8= value8 : "
set /p "cell9= value9 : "
set /p "cell10= value10 : "
for /l %%t in (3,1,10) do if !cell%%t!=="" set "cell%%t="
goto :eof