How to fill blank cells on a csv

November 25, 2019 at 02:30:04
Specs: Windows Server 2003
I have csv file (total.csv) with 25 columns.
I want to fill the blank cells of 22th and 24th column with the value "-" and save it as a different csv (total_new.csv)

Example.

total.csv

Col 1 Col 2 ... Col 22 Col 23 Col 24 Col 25
1 ST ... BLANK 6961 BLANK 11105
2 ST ... ABCAB 6961 CBABC 11105
3 ST ... BBBCC 6860 CCBBB 11106


total_new

Col 1 Col 2 ... Col 22 Col 23 Col 24 Col 25
1 ST ... - 6961 - 11105
2 ST ... ABCAB 6961 CBABC 11105
3 ST ... BBBCC 6860 CCBBB 11106


See More: How to fill blank cells on a csv

Reply ↓  Report •

#1
November 25, 2019 at 17:41:53
Before we address your question...

Please click on the How-To link at the end of this post and read the instructions on how to format example data so that it is easier for us to read. Then edit/repost your data so that the columns line up correctly. Don't forget to use Column letters and Row numbers as shown in the example.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code


Reply ↓  Report •

#2
November 25, 2019 at 23:39:22
Sorry, fixed.

Edit by DerbyDad03. Now, really fixed. ;-)

total.csv

   A ...   V     W    X     Y
1 ST ... blank 6961 blank 11105
2 ST ... ABCAB 6961 CBABC 11105
3 ST ... BBBCC 6860 CCBBB 11106


total_new.csv

   A ...   V     W    X     Y
1 ST ... -     6961 -     11105
2 ST ... ABCAB 6961 CBABC 11105
3 ST ... BBBCC 6860 CCBBB 11106


Reply ↓  Report •

#3
November 26, 2019 at 01:18:42
I assume the values are comma seperated as .csv stands for "comma separated values"

save this as test.bat& place it in the same folder as total.csv:

@echo off&setlocal enabledelayedexpansion
for /f "tokens=1-25 delims=," %%a in (total.csv) do (
	if "%%v"=="blank" (set "var1=-") else set "var1=BLANK"
	if "%%x"=="blank" (set "var2=-") else set "var2=BLANK"
	echo %%a,%%b,%%c,%%d,%%e,%%f,%%g,%%h,%%i,%%j,%%k,%%l,%%m,%%n,%%o,%%p,%%q,%%r,%%s,%%t,%%u,!var1!,%%w,!var2!,%%y > total_new.csv
)

i5-6600K[delid]@4.828GHz Core/4.627 Cache@1.456v | 2x4GB Crucial-DDR4-2400MHzCL18@3018MHzCL12@1.465v | Sapphire Nitro+ SE RX 590 8GB@1660Mhz core@1.2v/2236MHz


Reply ↓  Report •

Related Solutions

#4
November 26, 2019 at 06:34:22
Excel Method:

1 - Copy your data into a new Excel workbook
1a - Check your data to ensure that it is in the correct columns
2 - Save it as total.csv
3 - Close, reopen. Sheet1 should now be named total
4 - Add a new sheet

On the new sheet:

5 - Place this formula in A1:

=IF(total!A1<>"",total!A1,"-")

6 - Drag the formula down and right as far as you need to include all of the data on the total sheet. On the new sheet, all corresponding cells that were empty on the total sheet should now display the dash -
7 - Click the triangle above the Row numbers to select the entire sheet (or just select all of the cells that contain the formula)
8 - Copy...PasteSpecial...Values
8a - Check to ensure that the formulas have been replaced with the values

9 - Delete the total sheet
10 - SaveAs total_new.csv

Worked for me.

How To Post Data or Code ---> Click Here Before Posting Data or VBA Code

message edited by DerbyDad03


Reply ↓  Report •

#5
November 27, 2019 at 17:32:55
Might work. My original solution did the essentially same thing, I think. anyway:
@echo off & setlocal
for /f "tokens=*" %%a in (total.csv) do (
set g=%%a
call :xx
)
goto :eof

:xx
rem echo incoming: %g%
set g=,%g%,
:: note it takes two reps to get the result
set g=%g:,,=,-,%
set g=%g:,,=,-,%
echo %g:~1,-1%
::--------- end script - output is stdout. hardwire or redirect output to new file.

message edited by nbrane


Reply ↓  Report •

#6
November 28, 2019 at 07:11:11
TO: hidde663

I get an export file but with only one line, the last line.

In last line there was no blank/empty cell in 22th and 24th column but it did some changes on that line.

Col21 was empty/blank -> in export file this Col was skipped and the next Col got -1 in column numbering of the export csv.
So Col22 became Col21.
In Col23(Col22 in export file) it puted the value "blank" where there was already a value.
In Col25(Col24 in export file) it puted the value "blank" where there was already a value.
Also it added an extra line (Col25 in exportfile) with value " "

In the export file i need all the lines and collumns as it is. If there is a blank/empty cell somewhere else than 22th and 24th column it must remain as it is.
The empty/blank cells of 22th and 24th column must get the value "-" not the value "blank"

Thank you and sorry for missunderstand

message edited by bitatos


Reply ↓  Report •

#7
November 28, 2019 at 07:19:29

TO: nbrane

I get the following error on cmd
The system cannot find the file total.csv.


Reply ↓  Report •

#8
November 28, 2019 at 08:07:52
RE #6

ouch thats what i get for not testing it, this needs to be changed;

> total_new.csv

to:

>> total_new.csv

i5-6600K[delid]@4.828GHz Core/4.627 Cache@1.456v | 2x4GB Crucial-DDR4-2400MHzCL18@3018MHzCL12@1.465v | Sapphire Nitro+ SE RX 590 8GB@1660Mhz core@1.2v/2236MHz


Reply ↓  Report •

#9
November 29, 2019 at 06:56:13
RE #8

total.csv

U       V       W         X       Y
TITLEU  TITLEV  TITLEW    TITLEX  TITLEY
ANTALAK	ANTALAK	CARB-LOR  MC837	  5200002122
ANTALAK	ANTALAK		  ANTALAK	
DAK				  1098478-T2
PARJ12				  1098478-J12
PARJ3				  1098478-J3
KAMP	7960	PERF	  ANTALAK	


total_new.csv

U       V       W         X       Y
TITLEU  BLANK   TITLEW    BLANK   TITLEY
ANTALAK	BLANK	CARB-LOR  BLANK	  5200002122
ANTALAK	BLANK	ANTALAK	  BLANK	 
DAK     BLANK		  BLANK	 
PARJ12  BLANK		  BLANK	 
PARJ3   BLANK		  BLANK	 
KAMP    BLANK	PERF      BLANK	 

 

It puts the value BLANK entirelly in 22th and 24th Column even if it is a non-empty cell even in column headers.
And also it erases some values of other columns and maybe transfers.

Is there a way to export total_new.csv with the only modification in total.csv to be in ColV and COLX where empty cells take the value "-". And also headers be as they are.

Thank you

message edited by bitatos


Reply ↓  Report •

Ask Question