Help in writing a batch script to parse CSV file a

问题:

I am struggling to write a batch script which can read a CSV file such as below

Name:, City:, Country:
Mark, London, UK
Ben, Paris, France
Tom, Athens, Greece

There will be a heading row in the CSV file. It should output to a text file as below:

Name:Mark
City:London
Country:UK

Name:Ben
City:Paris
Country:France

Name:Tom
City:Athens
Country:Greece

The field separator (:) in the above output is expected to be provided in the header row itself. So all that I need to do is concatenate the field heading and its value.

The number of columns in this CSV file is not fixed, so the script should not limit to 3 tokens. Kindly help!

回答1:

@ECHO OFF
IF "%~1"=="" GOTO :EOF
SET "filename=%~1"
SET fcount=0
SET linenum=0
FOR /F "usebackq tokens=1-10 delims=," %%a IN ("%filename%") DO ^
CALL :process "%%a" "%%b" "%%c" "%%d" "%%e" "%%f" "%%g" "%%h" "%%i" "%%j"
GOTO :EOF

:trim
SET "tmp=%~1"
:trimlead
IF NOT "%tmp:~0,1%"==" " GOTO :EOF
SET "tmp=%tmp:~1%"
GOTO trimlead

:process
SET /A linenum+=1
IF "%linenum%"=="1" GOTO picknames

SET ind=0
:display
IF "%fcount%"=="%ind%" (ECHO.&GOTO :EOF)
SET /A ind+=1
CALL :trim %1
SETLOCAL ENABLEDELAYEDEXPANSION
ECHO !f%ind%!!tmp!
ENDLOCAL
SHIFT
GOTO display

:picknames
IF %1=="" GOTO :EOF
CALL :trim %1
SET /a fcount+=1
SET "f%fcount%=%tmp%"
SHIFT
GOTO picknames

This batch scipt:

• accepts one parameter, the name of the file to process;

• does not verify the presence of : at the end of a header token, and when the values are displayed they are placed immediately after the corresponding header tokens;

• trims all the leading spaces (but not the trailing ones);

• considers the first row to be the header row, which also defines the number of tokens to process in subsequent rows;

• supports up to 10 tokens, and the two areas highlighted in bold italics are responsible for that (so when you need to change the maximum number, modify both areas: if you increase the number, you must expand the "%%a" "%%b" "%%c" … list, and, likewise, if you decrease the number, then shrink the list).

回答2:

I know this is an old question, but this type of question is my favorite one so here it is my answer:

@echo off
setlocal EnableDelayedExpansion

rem Create heading array:
set /P headingRow=< %1
set i=0
for %%h in (%headingRow%) do (
    set /A i+=1
    set heading[!i!]=%%~h
)

rem Process the file:
call :ProcessFile < %1
exit /B

:ProcessFile
set /P line=
:nextLine
    set line=:EOF
    set /P line=
    if "!line!" == ":EOF" goto :EOF
    set i=0
    for %%e in (%line%) do (
        set /A i+=1
        for %%i in (!i!) do echo !heading[%%i]!%%~e
    )
goto nextLine
exit /B

This program have not any limit in the number of fields. This version requires to enclose in quotes the elements that may have spaces or other Batch delimiters, but this restriction may be easily fixed.

回答3:

Python makes this so easy it should be regulated by the government.

from csv import DictReader

with open('file', 'rb') as file:
    reader = DictReader(file)

    for line in reader:
        for field in reader.fieldnames:
            print '{0}{1}'.format(field.strip(), line[field].strip())

         print '\n'

Edit: I guess you need something native to the Windows command shell. Oh well.

回答4:

  Function CSVArray(CSVFile)

  Dim comma, quote
  comma = ","
  quote = Chr(34)

  Dim charPos, charVal

  Dim cellPos, colMax, colNum
  colMax  = -1

  Dim cellArray(), cellComplete, cellQuoted, csvRecord

  Dim inCsvSys, inCsv, inRow(), rowCount
  rowCount     = -1
  Set inCsvSys = CreateObject("Scripting.FileSystemObject")
  Set inCsv    = inCsvSys.OpenTextFile(CSVFile,"1",True)
  Do While Not inCsv.AtEndOfStream
    rowCount = rowCount + 1
    Redim Preserve inRow(rowCount)
    inRow(rowCount) = inCsv.ReadLine
  Loop
  inCsv.Close

  For r = 0 to rowCount

    csvRecord = inRow(r)
    colNum = -1
    charPos = 0
    cellComplete = True

    Do While charPos < Len(csvRecord)

      If (cellComplete = True) Then
        colNum       = colNum + 1
        cellPos      = 0
        cellQuoted   = False
        cellComplete = False
        If colNum > colMax Then
          colMax = colNum
          Redim Preserve cellArray(rowCount,colMax)
        End If              
      End If

      charPos = charPos + 1
      cellPos = cellPos + 1
      charVal = Mid(csvRecord, charPos, 1)
      If (charVal = quote) Then
        If (cellPos = 1) Then
          cellQuoted = True
          charVal    = ""
        Else
          Select Case Mid(csvRecord, charPos+1, 1)
          Case quote
            charPos = charPos + 1
          Case comma
            charPos = charPos + 1
            cellComplete = True
          End Select
        End If
      ElseIf (charVal = comma) And (cellQuoted = False) Then
        cellComplete = True
      End If
      If (cellComplete = False) Then
        cellArray(r,colNum) = cellArray(r,colNum)&charVal
      End If

    Loop

  Next
  CSVArray = cellArray
End Function

Dim StdOut
Set StdOut = WScript.StdOut
Dim csv

If Wscript.Arguments.Count = 0 Then
    Wscript.StdOut.WriteLine "Invalid Arguments"
Else
    csv = CSVArray(Wscript.Arguments(0))
End If


For r = 1 to UBound(csv,1)
  For c = 0 to UBound(csv,2)
    Wscript.StdOut.WriteLine csv(0,c) & csv(r,c)
  Next
Next