How to extract content of a CSV file into individual files with folder and file name based on the first two data columns?

Question

I have the following request for help with CSV file extracts:

C:\TestDirectory\Parse.csv contains:

User, FileName, HTMLContent
Tom, Doc1, <HTML etc etc etc
Tom, Doc2, <HTML etc etc>
Sue, Other, <HTML etc etc>

I would like a script to create for the example above:

D:\ResultsDirectory\Tom\Doc1.html
D:\ResultsDirectory\Tom\Doc2.html
D:\ResultsDirectory\Sue\Other.html

So far I have:

@echo off
Set file= c:\TestDirectory\Parse.csv

REM get header:
set /p header=<%file%

REM ignore header Skip 1:
REM Process Filename Tokens 2:
for /f "skip=1 delims=" %%A in (%file%) do (
    for /f "tokens=2 delims=," %%B in ("%%A") do (
        if not exist "%%B.html" echo %header%>"%%B.html"
        >>"%%B.html" echo %%A
    )
)

pause

Problems: These are my searching limits.

I get the filenames without the correct path.
The file is populated by the whole line from the CSV, and I don't know how to get just what I require.

You don't need two `for /F` loops, you may specify `skip` and `tokens` options together. You don't read the first token although you'd need it for building the paths… — aschipfl, Jun 20 '22 at 06:15
Do you absolutely *need* to do this in pure dos ? A free tool such as [AutoIT3](https://www.autoitscript.com/site/autoit/) is perfect for simple things like this. The syntax is similar to basic. — MyICQ, Jun 20 '22 at 06:15
The single command line necessary for this task in the batch file is: `@for /F "usebackq skip=1 tokens=1,2* delims=," %%G in ("C:\TestDirectory\Parse.csv") do @for /F "tokens=*" %%J in ("%%H") do @for /F "tokens=*" %%K in ("%%I") do @md "D:\ResultsDirectory\%%G" 2>nul & echo %%K>"D:\ResultsDirectory\%%G\%%J.html"` The two inner `for /F` are just for removing leading spaces which the data have in the CSV file in second and third data column according to the posted sample for unknown reason. — Mofi, Jun 20 '22 at 06:28
@MyICQ, it's *not* DOS, it's Windows CMD, which is [something completely different](https://superuser.com/q/451816)! — aschipfl, Jun 20 '22 at 09:50

score 1 · Answer 1 · answered Jun 20 '22 at 06:22

@ECHO Off
SETLOCAL ENABLEDELAYEDEXPANSION
rem The following settings for the directories and filename are names
rem that I use for testing and deliberately include names which include 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%\q72682391.txt"

SET "header="

FOR /f "usebackqdelims=" %%b IN ("%filename1%") DO (
 IF DEFINED header (
  FOR /f "tokens=1,2,*delims=," %%u IN ("%%b") DO (
   FOR /f "tokens=*" %%e IN ("%%v") DO (
    MD "%destdir%\%%u" 2>nul
    (
     ECHO !header!
     FOR /f "tokens=*" %%y IN ("%%w") DO ECHO %%y
    )>"%destdir%\%%u\%%e.html
   )
  )
 ) ELSE SET "header=%%b"
)
GOTO :EOF

Always verify against a test directory before applying to real data.

Outer loop: reads each line of file into %%b. As header is initialised to nothing, on reading the first line, header is not defined, so it is set by the else clause of the if statement to the header line. For every other line, header will be defined, so processing continues:

Next inner loop: %%u is set to the first token (name), %%v to the second (filename) and %%w to the remainder of the line (html).

Next loop: "tokens=*" will strip leading spaces from the string %%v, which includes the space after the comma-delimiter from the previous forso%%e` will contain the filename without the leading space.

Create the destination (name) directory from %%u. The 2>nul suppresses the error reports (like directory already exists)

Enclosing a sequence of lines in parentheses enables us to redirect all of the echoed output to a new file using > rather than having to create using a > and then append using >>.

So, output the contents of header using delayedexpansion syntax Stephan's DELAYEDEXPANSION link

then use the tokens=* mechanism again to output the contents of %%w with the leading spaces suppressed.

Personally, I can't see the point of including the header in the file generated, but that's what you appear to have expected to do with your original code. If you don't want it, simply remove that line.

HI, Thank you for this. 99% there. I do not want the header in the file. I removed Delayed expansion and reference to headers and it seems to be 100% - SETLOCAL ENABLEDELAYEDEXPANSION SET "header=" ECHO !header! DO we foresee any problems with this? — Graeme White, Jun 20 '22 at 22:02
Can't see why there would be any other problems. The `delayedexpansion` is not necessary, since you're not using any `!variable!`s, but a `setlocal` is advisable since it means that the environment does not get changed by running the routine. — Magoo, Jun 21 '22 at 00:37

How to extract content of a CSV file into individual files with folder and file name based on the first two data columns?

1 Answers1