CODEWITHSUNDEEP
windowscsvbatch-filecmdeol
drumtechjp
drumtechjp

Reputation: 25

Automation to add a LF and CR (EOL) to the end of multiple .csv files

I have a bunch of .csv files that are generated externally and sent to me periodically. they each contain a single row of text with 31 "columns". None of them, however, have any kind of EOL (no LF by itself or with CR), so when I attempt to combine any of these files, I get more columns on the same row, instead of a row for each file.

I would like a way to automatically add this to the end of each of these files in a batch, with the outputs having the same filename as the original file, potentially with the addition of a character at the beginning of the name so I new this process was completed. Ex: originalFile.csv>> 1_originalFile.csv.

I had attempted to create a file called "eol.csv" that was simply (LF and CR), and create a batch that would add that to the end of all of my files, but as I am a novice to writing batch files, I was significantly unsuccessful.

If it were possible for this to execute on each file as it were dropped into a folder, that would be even better.

Thanks for any thoughts on this!

Upvotes: 1

Views: 1933

Answers (4)

aschipfl
aschipfl

Reputation: 34979

There are several methods to append a line-break to (the last line of) a file if not yet present:

  1. findstr:

    findstr /V "$" "data.csv" > nul && echo/>> "data.csv"

    This inverse (/V) search matches the last line only when it is not terminated by a line-break. In such case && lets the following command execute, which just appends a line-break.

    Restrictions:

    • lines must be shorter than 8K characters;

  2. find:

    < "data.csv" find /V "" > "data.csv.tmp" && move /Y "data.csv.tmp" "data.csv" > nul

    This search matches all lines, find appends a line-break to every returned line, even for the last one when there is none. A temporary file is required since it is not possible to read from and write to the same file. If no errors occur, && lets the next command execute, which moves the temporary file onto the original one.

    Restrictions:

    • this requires a temporary file;
    • lines must be shorter than 4K characters;

  3. more:

    more "data.csv" > "data.csv.tmp" && move /Y "data.csv.tmp" "data.csv" > nul

    This just returns all lines; more appends a line-break to every returned line, even for the last one when there is none. A temporary file is required since it is not possible to read from and write to the same file. If no errors occur, && lets the next command execute, which moves the temporary file onto the original one.

    Restrictions:

    • this requires a temporary file;
    • the file must be shorter than 64K lines;
    • lines must be shorter than 64K characters;
    • TABs become expanded to SPACEs;

  4. sort:

    sort "data.csv" /+65535 /REC 65535 | sort /+65535 /REC 65535 /O "data.csv"

    This just returns all lines; sort appends a line-break to every returned line, even for the last one when there is none. Surprisingly, no temporary file is required (I tested with a ~ 30 MB file without data loss due to I/O collisions). Nevertheless, this is likely the slowest method here because of the pipe (|).

    The key is to set a character position for sorting that is beyond the data. In such cases, sort seems to simply revert the whole file; this is the reason for using two sort commands. But I tested it just very quickly with one file on Windows 7, so you should be cautious with this.

    Restrictions:

    • lines must be shorter than 64K characters;

All of the above approaches can easily be implemented in a for loop in order to be applied to multiple files; simply replace data.csv with the for meta-variable then (demonstrated on variant 1. here):

for %I in ("*.csv") do @(findstr /V "$" "%~I" > nul && echo/>> "%~I")

Remember that the %-signs need to be doubled when using this code in a .

Upvotes: 0

Bloodied
Bloodied

Reputation: 975

This will search all .csv file names for the string _fixed, and on any who fail to have it, will insert a blank line and rename it. Of course replace the pathToWherever with the correct path for you, and the /s option can be added to allow searching in subfolders in the named path too. 

@echo off
for /r "C:\pathToWherever\" %%G in (*.csv) do (
    echo %%G | findstr /c:"_fixed" || (
        echo:>>%%G
        ren "%%G" "%%~nG_fixed.csv" 
    )
)

Upvotes: 1

dbenham
dbenham

Reputation: 130919

The FINDSTR regular expression $ recognizes end of line as the position immediately before a carriage return. So findstr /v $ will only match lines that do not contain a carriage return. You can use this fact to append carriage return/linefeed to only files that need it, without having to rename any files.

The following one liner from the command line is all you need:

for /f "eol=: delims=" %F in ('findstr /m /v $ *.csv') do @(echo()>>"%F"

Double up the percents if you put the command within a batch script.

Upvotes: 4

SomethingDark
SomethingDark

Reputation: 14325

Since all echos end in a CRLF, and you can use echo/ to echo a CLRF by itself, you can simply use output redirection to append a CLRF to each of the csv files.

If you want to run this on a bunch of files that you've dragged and dropped onto the script, it would look like this:

@for %%A in (%*) do echo/ >>%%A

That one line is the entire script, by the way.

Upvotes: 1

Related Questions