Need a script that will cleanup a CSV file

Posted on 2013-09-06
Medium Priority
Last Modified: 2013-09-09
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
Question by:DebbieFost
LVL 86

Assisted Solution

oBdA earned 1000 total points
ID: 39470977
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

LVL 60

Accepted Solution

Bill Prew earned 1000 total points
ID: 39472352
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


Author Closing Comment

ID: 39477394
Both solutions work nicely - thanks gang!

Featured Post

Train for your Pen Testing Engineer Certification

Enroll today in this bundle of courses to gain experience in the logistics of pen testing, Linux fundamentals, vulnerability assessments, detecting live systems, and more! This series, valued at $3,000, is free for Premium members, Team Accounts, and Qualified Experts.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
How to fix display issue, screen flickering issue when I plug in power cord to the machine. Before I start explaining the solution lets check out once the issue how it looks like after I connect the power cord. most of you also have faced this…
In this video I will demonstrate how to set up Nine, which I now consider the best alternative email app to Touchdown.

607 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question