Page 1 of 2
Insert text into column b csv file
Posted: 29 Aug 2011 06:10
by 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
Re: Insert text into column b csv file
Posted: 29 Aug 2011 13:38
by trebor68
You can see this in following link
http://www.dostips.com/forum/viewtopic.php?f=3&t=2156The 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.
Re: Insert text into column b csv file
Posted: 29 Aug 2011 15:25
by 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
Re: Insert text into column b csv file
Posted: 29 Aug 2011 20:05
by 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
Re: Insert text into column b csv file
Posted: 30 Aug 2011 04:20
by 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
Re: Insert text into column b csv file
Posted: 30 Aug 2011 07:22
by ghostmachine4
Ken wrote: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).
Re: Insert text into column b csv file
Posted: 30 Aug 2011 09:55
by 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
Re: Insert text into column b csv file
Posted: 30 Aug 2011 11:32
by A_Bobby
Try this
echo ,CellB1 > test2.csv
I know it looks absurdly simple. Hope I am not missing anything!
Re: Insert text into column b csv file
Posted: 30 Aug 2011 11:48
by 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.
Re: Insert text into column b csv file
Posted: 30 Aug 2011 14:04
by 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
Re: Insert text into column b csv file
Posted: 30 Aug 2011 20:41
by ghostmachine4
Ken wrote: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.
Re: Insert text into column b csv file
Posted: 31 Aug 2011 01:27
by 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".
Re: Insert text into column b csv file
Posted: 31 Aug 2011 02:43
by 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.
Re: Insert text into column b csv file
Posted: 31 Aug 2011 06:37
by 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.
Re: Insert text into column b csv file
Posted: 01 Sep 2011 05:31
by 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