I have a csv file with data in 8 columns. In column 2 it is a number and I want to move that number to a new column in position 3 if the value on the post is six digits or less (below 1000000). Is there anyone that could come with some suggestion how to solve this with a batch command?
It looks like this:
First Last,28000843,Test1,20110503,1630,1930,Test 2,Book. nr. 53655
First Last,47110833,Test1,20110503,1630,1930,Test 2,Book. nr. 53655
First Last,29836545,Test1,20110503,1630,1930,Test 2,Book. nr. 53655
First Last,1155,Test1,20110503,1630,1930,Test 2,Book. nr. 53655
First Last,54963713,Test1,20110503,1630,1930,Test 2,Book. nr. 53655
First Last,14798,Test1,20110503,1630,1930,Test 2,Book. nr. 53655
First Last,582666,Test1,20110503,1630,1930,Test 2,Book. nr. 53655
I want it to be like this:
First Last,28000843,,Test1,20110503,1630,1930,Test 2,Book. nr. 53655
First Last,47110833,,Test1,20110503,1630,1930,Test 2,Book. nr. 53655
First Last,29836545,,Test1,20110503,1630,1930,Test 2,Book. nr. 53655
First Last,,1155,Test1,20110503,1630,1930,Test 2,Book. nr. 53655
First Last,54963713,,Test1,20110503,1630,1930,Test 2,Book. nr. 53655
First Last,,14798,Test1,20110503,1630,1930,Test 2,Book. nr. 53655
First Last,,582666,Test1,20110503,1630,1930,Test 2,Book. nr. 53655
I have looked thru a lot of examples of similar posts here but can't find any that do anything similar, and that could help me with ideas.
@ECHO OFF
SETLOCAL
rem The following settings for the directories and filenames are names
rem that I use for testing and deliberately includes spaces to make sure
rem that the process works using such names. These will need to be changed to suit your situation.
SET "sourcedir=u:\your files"
SET "destdir=u:\your results"
SET "filename1=%sourcedir%\q78671339.txt"
SET "outfile=%destdir%\outfile.txt"
(
FOR /f "usebackqtokens=1,2,*delims=," %%g IN ("%filename1%") DO (
IF %%h lss 1000000 (ECHO %%g,,%%h,%%i) ELSE (ECHO %%g,%%h,,%%i)
)
)>"%outfile%"
GOTO :EOF
Always verify against a test directory before applying to real data.
Note that if the filename does not contain separators like spaces, then both usebackq and the quotes around %filename1% can be omitted.
I used a file named q78671339.txt containing your data for my testing.
Produces the file defined as %outfile%
Assumes that there is always a value in each column, separated by commas and that no column contains a comma. Also, the value in column 2 is always leading-zero suppressed and numeric.