How to remove blank rows from csv file using batch?

Question

It's my first time programming with batch files and I was asked to do a program capable of converting .xlsm files into .csv, without having to open Excel to do so. To do so, I use this .bat file:

extoc.vbs integration.xlsm integration.csv 
Taskkill /IM EXCEL.EXE /F

@echo off
SETLOCAL ENABLEDELAYEDEXPANSION


call :StripBlankLines "integration.csv"

goto :eof
:StripBlankLines
For %%x in ("%~1") do set OutF=integration_er.csv
if exist "%OutF%" del "%OutF%"
set FirstLine=1
for /F "usebackq delims=" %%B in (%*) do (
    call :TrimWS %%B
    if not "!Line!"=="" (
        if "!FirstLine!"=="1" (
            set FirstLine=0
        ) else (
            >>"%OutF%" echo.
        )
        call :write !Line!
    )
)
goto :eof

:TrimWS
set Line=%*
goto :eof 

:write
>>"%OutF%"<NUL set /p Dummy=%*
goto :eof

Together with the .vbs file:

if WScript.Arguments.Count < 2 Then
WScript.Echo "Please specify the source and the destination files. Usage: ExcelToCsv <xls/xlsx source file> <csv destination file>"
Wscript.Quit
End If

csv_format = 6

Set objFSO = CreateObject("Scripting.FileSystemObject")

src_file = objFSO.GetAbsolutePathName(Wscript.Arguments.Item(0))
dest_file = objFSO.GetAbsolutePathName(WScript.Arguments.Item(1))

Dim oExcel
Set oExcel = CreateObject("Excel.Application")

Dim oBook
Set oBook = oExcel.Workbooks.Open(src_file)

oBook.SaveAs dest_file, csv_format

oExcel.Quit

oBook.Close False

The problem in here is that I get, as a final result, a giant CSV containing all the information from the original .xlsm file, but also several lines filled with nothing but commas, as you can see bellow:

data, data, data,, data, data, data, data, data, data
data, data, data,, data, data, data, data, data, data
data, data, data,, data, data, data, data, data, data
data, data, data,, data, data, data, data, data, data
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,
,,,,,,,,,

While I need to have something like this:

data, data, data,, data, data, data, data, data, data
data, data, data,, data, data, data, data, data, data
data, data, data,, data, data, data, data, data, data
data, data, data,, data, data, data, data, data, data

I know these commas come from blank rows in excel, but I can't use a VBA Macro to remove all the blank rows in excel because it is a HUGE sheet and my PC crashes everytime I try to do it. So, if there's some way to solve this through VBS or BATCH I would be REALLY glad!

Replace(<your csv>,",,,,,,,,," & vbCrLf,"")?

cybernetic.nomad
– cybernetic.nomad

2018-03-20 17:12:06 +00:00
Commented Mar 20, 2018 at 17:12 — cybernetic.nomad
– cybernetic.nomad, Commented Mar 20, 2018 at 17:12
Didn't work. But stil, thanks man!

souza
– souza

2018-03-20 17:22:23 +00:00
Commented Mar 20, 2018 at 17:22 — souza
– souza, Commented Mar 20, 2018 at 17:22

Magoo · Accepted Answer · 2018-03-20 17:56:14Z

1

:TrimWS
set Line=%*
set "line=%line:,=%"
if defined line set "line=%*"
goto :eof

Replace each , in line with nothing. If the result is nothing then line will be undefined, ie. empty. If it isn't re-load it with the original value.

BTW - If you set FirstLine to nothing (set firstline=") or something (anything, just not nothing) then you can use if defined firstline to detect its current state so you don't need delayedexpansion.

(rewritten)

@echo off
SETLOCAL


call :StripBlankLines "integration.csv"

goto :eof
:StripBlankLines
For %%x in ("%~1") do set OutF=integration_er.csv
if exist "%OutF%" del "%OutF%"
set FirstLine=1
for /F "usebackq delims=" %%B in (%*) do (
    call :TrimWS %%B
    if defined line (
        if defined firstline (
            set "FirstLine="
        ) else (
            >>"%OutF%" echo.
        )
        call :write %%B
    )
)
goto :eof

:TrimWS
set Line=%*
set "line=%line:,=%"
goto :eof 

:write
>>"%OutF%"<NUL set /p Dummy=%*
goto :eof

Since line will contain the same as %%B, you can use %%B provided it doesn't consist of just-commas. Hence you can despatch %%B to your :trimws routine, and line will be set to either nothing (in the case of all-commas) or something (anything) otherwise.

You can then simply interpret line being either defined or not to output %%B through your :write routine, as :write will only be invoked when %%B does not contain all-commas but also isn't empty (the for/f will take care of that).

Setting firstline to something at the start means if defined firstline will be true initially, so you set it to nothing and thereafter if defined firstline will be false.

The syntax SET "var=value" (where value may be empty) is used to ensure that any stray trailing spaces are NOT included in the value assigned.

The setlocal should remain in order that the cmd environment does not accumulate values as batch routines run. It ensures that any changes made to the environment variables are discarded when the routine terminates

edited Mar 20, 2018 at 17:56

answered Mar 20, 2018 at 17:14

Magoo

80.8k8 gold badges68 silver badges92 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

souza Over a year ago

Thanks man! It worked perfectly in my code! Also, thanks for the tip ;)

souza Over a year ago

Also, how could I adapt the code to remove the delayedexpansion? I got this part of the code online

Sancarn Over a year ago

@PedroMartins You're going to have to explain what delayedexpansion is...

Sancarn Over a year ago

@PedroMartins oh woops! I didn't realised delayed expansion was a standard batch thing... My bad!

souza Over a year ago

No problems, @Sancarn!

Compo · Accepted Answer · 2018-03-20 18:43:29Z

1

Unless I'm misunderstanding, why not just use a single line:

FindStr "[^,]" "integration.csv">"integration_er.csv"

answered Mar 20, 2018 at 18:43

Compo

39k5 gold badges32 silver badges46 bronze badges

Collectives™ on Stack Overflow

How to remove blank rows from csv file using batch?

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related