Need a script that will cleanup a CSV file

I have a CSV file that I need to cleanup prior to an import every day. Basically, I need to do the following:

1. Truncate field 2 to the first 5 characters
2. Remove Field 3 altogether
3. Take the last field from every second record (which should be a pure Alpha Value) and add it to the last field in the row above (and delete the second record).
4. Take the first 8 numeric characters from and insert them as Field 1
5. Merge Fields 2 onwards into a single field separated by dashes

My original CSV file looks like this:

09/06/13,21:24:15,03,12345678 FILE FOR OFFICE
09/06/13,21:24:22,03,JUDY JONES
09/06/13,21:24:27,03,34567890 ANY TEXT
09/06/13,21:24:15,03,98765432 HELLO KITTY
09/06/13,21:24:22,03,FRED MIRK
09/06/13,21:24:27,03,29212302 NOTE TO FILE
09/06/13,21:24:34,03,JIM BROWN

After the script runs, I need it to look like this:

12345678,09/06/13 - 21:24 - FILE FOR OFFICE - JUDY JONES
34567890,09/06/13 - 21:24 - ANY TEXT- ACCOUNTING
98765432,09/06/13 - 21:24 - HELLO KITTY - FRED MIRK
29212302,09/06/13 - 21:24 - NOTE TO FILE - JIM BROWN
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

How do you want to specify the import and export file name, or are they static? This script currently has them hard-coded, but that can easily be changed so that they're passed at the command line:
@echo off
setlocal enabledelayedexpansion
set ImportFile=import.csv
set ExportFile=export.csv
if exist "%ExportFile%" del "%ExportFile%"
set Add=True
for /f "tokens=1-4 delims=," %%a in ('type "%ImportFile%"') do (
	if "!Add!"=="True" (
		set OldCol1=%%~a
		set OldCol2=%%~b
		set OldCol3=%%~c
		set OldCol4a=%%~d
		set Add=False
	) else (
		set OldCol4b=%%~d
		set Add=True
		set NewCol1=!OldCol4a:~0,8!
		set NewCol2=!OldCol1! - !OldCol2:~0,5! - !OldCol4a:~9! - !OldCol4b!
		echo !NewCol1!,!NewCol2!
		>>"%ExportFile%" echo !NewCol1!,!NewCol2!

Open in new window

Bill PrewCommented:
Similar, but some differences in how I would approach it, maybe a small amount more streamlined. Also handles any length sequence number.

@echo off
setlocal EnableDelayedExpansion

set FileIn=in.csv
set FileOut=out.csv

set Odd=1
  for /f "usebackq tokens=1,2,4 delims=," %%a in ("%FileIn%") do (
    if !Odd!==1 (
      set Date1=%%~a
      set Time1=%%~b
      for /f "tokens=1* delims= " %%A in ("%%~c") do (
        set Seq1=%%~A
        set Data1=%%~B
    ) else (
      set Data2=%%~c
      echo !Seq1!,!Date1! - !Time1:~0,5! - !Data1! - !Data2!
    set /a "Odd=!Odd! ^^ 1"
) >"%FileOut%"

Open in new window


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DebbieFostAuthor Commented:
Both solutions work nicely - thanks gang!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft DOS

From novice to tech pro — start learning today.