extract columns from .csv with no blanks

November 13, 2019 at 03:08:33
Specs: Windows 10
I have a .csv file with 25 columns.
I want to export the 24th and 22th column to a new .csv file but with no blank rows

See More: extract columns from .csv with no blanks

Reply ↓  Report •

#1
November 13, 2019 at 19:20:16
A before and after sample of various lines of the file would help. I could not "read" enough from the problem description to do any coding.

Reply ↓  Report •

#2
November 13, 2019 at 23:46:29
total.csv is like that
Col 1 Col 2 ..... Col 22 Col 23 Col 24 Col 25
1 ST ..... NULL 6961 NULL 11105
2 ST ..… ABC 6961 MC837 11105
3 ST ..... BCA 1914 A2510 13404
4 ST ..... CBA 1914 10984 13404


export.csv is like that:

Col 22 Col 24
ABC MC837
BCA A2510
CBA 10984


NULL= blank cell
Thank you.

message edited by bitatos


Reply ↓  Report •

#3
November 14, 2019 at 20:15:33
Well, this is pretty crappy but best I could do tonight. (Consider it a crude prototype!)
:: -------- begin script
@echo off & setlocal enabledelayedexpansion
for /f "tokens=*" %%a in (total.csv) do call :aa "%%a"
goto :eof

:aa
set out=
set k=%1
:: null fields are SKIPPED as tokens by batch, hence this dance
set k=%k:,=_,%
for /f "tokens=22,24 delims=," %%b in (%k%) do (
if "%%b%%c" equ "__" goto :eof
call :bb %%b %%c
)
goto :eof

:bb
set m=%1
set m=%m:~0,-1%
set out=%out%%m%,
shift
if "%1" neq "" goto :bb
echo %out:~0,-1%
goto :eof

::-------- end of script.

message edited by nbrane


Reply ↓  Report •

Related Solutions

#4
November 15, 2019 at 05:43:41
Thank you.
I runned it and i got the following


C:\Users\bitatos>:: -------- begin script
C:\Users\bitatos>
C:\Users\bitatos>@echo off & setlocal enabledelayedexpansion
for /f "tokens=*" %%a in (total.csv) do call :aa "%%a"
%%a was unexpected at this time.
goto :eof

:aa
set out=
set k=%1
:: null fields are SKIPPED as tokens by batch, hence this dance
set k=%k:,=_,%
for /f "tokens=22,24 delims=," %%b in (%k%) do (
%%b was unexpected at this time.
if "%%b%%c" equ "__" goto :eof
call :bb %%b %%c
Invalid attempt to call batch label outside of batch script.
)
goto :eof

:bb
set m=%1
set m=%m:~0,-1%
set out=%out%%m%,
shift
if "%1" neq "" goto :bb
echo %out:~0,-1%
%out%%
goto :eof

::-------- end of script.


Reply ↓  Report •

#5
November 15, 2019 at 16:44:20
It looks to me like you entered the commands at the commandline instead of saving the above as a .bat script and running it. Cut/paste or otherwise save the script, f/e
xxx.bat
then at command prompt, run the script IN the same directory as the csv file. I just kind of assumed you knew to do all this... My bad! :(

Reply ↓  Report •

#6
November 17, 2019 at 23:48:45
Thank you.

bat file created in the same directory as the csv and i runned it through cmd.
But no export.csv file created


Reply ↓  Report •

#7
November 18, 2019 at 19:37:31
Sorry, forgot to direct the output. If you want flexibility of output, you can run script like this:
xxx > export.csv
If you want the output "hardwired" to output.csv, modify this line (about two lines from the bottom):
echo %out:~0,-1%

to:
>>export.csv echo %out:~0,1%

I'm not sure where this bogey came in:
%out%%
it should be removed. Here's a recap of the script hard-coded to "export.csv":

:: -------- begin script
@echo off & setlocal enabledelayedexpansion
for /f "tokens=*" %%a in (total.csv) do call :aa "%%a"
goto :eof

:aa
set out=
set k=%1
:: null fields are SKIPPED as tokens by batch, hence this dance
set k=%k:,=_,%
for /f "tokens=22,24 delims=," %%b in (%k%) do (
if "%%b%%c" equ "__" goto :eof
call :bb %%b %%c
)
goto :eof

:bb
set m=%1
set m=%m:~0,-1%
set out=%out%%m%,
shift
if "%1" neq "" goto :bb
>>export.csv echo %out:~0,-1%
goto :eof
::-------- end of script.


Here's the test data, "total.csv" I used based on your description:
,,,,,,,,,,,,,,,,,,,,,,6961,,11105
,,,,,,,,,,,,,,,,,,,,,ABC,6961,MC837,11105
,,,,,,,,,,,,,,,,,,,,,BCA,1914,A2510,13404
,,,,,,,,,,,,,,,,,,,,,CBA,1914,10984,13404
,,,,,,,,,,,,,,,,,,,,,,skip,,this

This was the output (export.csv):
ABC,MC837
BCA,A2510
CBA,10984


ps: I really need to go with powershell! probly one line. vbscript four or five.

message edited by nbrane


Reply ↓  Report •

#8
November 19, 2019 at 06:18:00
Thank you.

Still i do not get an export file.
As the bat runs it throw lines :
The system cannot find the file <Col1> <Col2>.....
The system cannot find the file <Col1> <Col2>.....


Reply ↓  Report •

#9
November 19, 2019 at 20:06:04
Please post back with the exact script you're running - use cut/paste so there can be no question of content. I can't get this error running my script. Also the exact input you're testing with, not just the summary as given so far. At this point, I'm "flying blind with no instruments".

Reply ↓  Report •

#10
November 20, 2019 at 00:28:24
i created a file(new) and put the test.bat and total.csv there.

i run through cmd: C:\Users\bitatos\Desktop\new\test.bat
The system cannot find the file total.csv


:: -------- begin script
@echo off & setlocal enabledelayedexpansion
for /f "tokens=*" %%a in (total.csv) do call :aa "%%a"
goto :eof

:aa
set out=
set k=%1
:: null fields are SKIPPED as tokens by batch, hence this dance
set k=%k:,=_,%
for /f "tokens=22,24 delims=," %%b in (%k%) do (
if "%%b%%c" equ "__" goto :eof
call :bb %%b %%c
)
goto :eof

:bb
set m=%1
set m=%m:~0,-1%
set out=%out%%m%,
shift
if "%1" neq "" goto :bb
>>export.csv echo %out:~0,-1%
goto :eof
::-------- end of script


Reply ↓  Report •

#11
November 20, 2019 at 17:14:49
My apologies, the batch script fails to address the path, which it needs to do. Please add this line as the second line (after "echo off..."):
pushd C:\Users\bitatos\Desktop\new
this should fix the problem, and the script no longer needs to reside in the same location as the total.csv file. The output will be in same location as total.csv.

Reply ↓  Report •

#12
November 21, 2019 at 06:10:41
Thank you but still i do not get any export file in folder new

I run through cdm: C:\Users\bitatos\Desktop\new\test.bat

total.csv
https://ibb.co/VLVWPD5

export.csv
https://ibb.co/nwc22TH

:: -------- begin script
@echo off & setlocal enabledelayedexpansion
pushd C:\Users\bitatos\Desktop\new
for /f "tokens=*" %%a in (total.csv) do call :aa "%%a"
goto :eof

:aa
set out=
set k=%1
:: null fields are SKIPPED as tokens by batch, hence this dance
set k=%k:,=_,%
for /f "tokens=22,24 delims=," %%b in (%k%) do (
if "%%b%%c" equ "__" goto :eof
call :bb %%b %%c
)
goto :eof

:bb
set m=%1
set m=%m:~0,-1%
set out=%out%%m%,
shift
if "%1" neq "" goto :bb
>>export.csv echo %out:~0,-1%
goto :eof
::-------- end of script

message edited by bitatos


Reply ↓  Report •

#13
November 21, 2019 at 17:45:13
It worked on my end, using what I think is your source material. (photo screen-shots of spreadsheet does not qualify as legitimate source material for CSV format). I got the export.csv in my target directory where the total.csv is stored. PLEASE give me a copy of the CSV, NOT a screen-shot and NOT edited. That's all I ask. I THINK quotes may be the problem, either/or source CSV is Unicode. I'll give up after this to give other helpers a go at it.

Reply ↓  Report •

#14
November 25, 2019 at 01:38:26
I PMed you the download link of the .csv

Also i will start a new post about an additional edit that can be done to the .csv, except than deleting blank cells, though it is not the most preffered

Thank you.

message edited by bitatos


Reply ↓  Report •

Ask Question