How to Transform the Values in Excel Files to a Column of Values?

I was given a few Excel CSV files that contain quite a lot of values, like this:

excel-values

The task is to copy all the valid numbers and store them in a new Excel CSV file row by row – so basically one column of values.

VBScript to Read CSV Files

The Excel has inbuilt Macro that allows you to write VBScript, but I find it easier to write a VBScript that runs in Windows Scripting Host environment. The VBScript following (e.g. named oneliner.vbs) reads the CSV Files (as they are plain text), split them by lines (so read string row by row), and split each row by delimiter comma. Print the value to console which you can redirect to the file.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27

Option Explicit
Dim fso, file, content, lines, line, i, j, k
 
if WScript.Arguments.Count = 0 then
    WScript.Echo "Usage 1.csv 2.csv ..."
    WScript.Quit 1
end If
 
Set fso = CreateObject("Scripting.FileSystemObject")
 
For k = 0 To WScript.Arguments.Count - 1
    Set file = fso.OpenTextFile(WScript.Arguments(k), 1)
    content = file.ReadAll  
    lines = Split(content, Chr(13) & Chr(10))
    For i = 0 To UBound(lines)
        line = Split(lines(i), ",")
        For j = 0 To UBound(line)
            If (IsNumeric(line(j))) Then
                WScript.Echo line(j)    
            End If 
        Next
    Next
Next 
 
file.Close
Set fso = Nothing
Set file = Nothing 

Option Explicit
Dim fso, file, content, lines, line, i, j, k

if WScript.Arguments.Count = 0 then
    WScript.Echo "Usage 1.csv 2.csv ..."
    WScript.Quit 1
end If

Set fso = CreateObject("Scripting.FileSystemObject")

For k = 0 To WScript.Arguments.Count - 1
	Set file = fso.OpenTextFile(WScript.Arguments(k), 1)
	content = file.ReadAll	
	lines = Split(content, Chr(13) & Chr(10))
	For i = 0 To UBound(lines)
		line = Split(lines(i), ",")
		For j = 0 To UBound(line)
			If (IsNumeric(line(j))) Then
				WScript.Echo line(j)	
			End If 
		Next
	Next
Next 

file.Close
Set fso = Nothing
Set file = Nothing 

To use the VBScript, you can run:

cscript.exe /Nologo oneliner.vbs data.csv

The script will read the data.csv and print the values to console line by line, you can add “> data-new.csv” to redirect to a new CSV file. If you have several input CSV files, you can run the following batch command:

for /r %f in (*.csv) do ( cscript.exe /nologo  oneliner.vbs  %f >  %f-new.csv )

This command will loop over all csv files and convert them to -new.csv (one column).

–EOF (The Ultimate Computing & Technology Blog) —