vb script to read csv file and put each value (column) in separate text file

[Christian Ort]

Hello,
I'm looking for a small script that is able to split a csv file into multiple text files.

e.g. I've a file "Input.csv" that contains this data:
LA2405w,CN12345678
L2245w,CNU9876543
etc,etc

The output should be 4 or more files, each file containing one column of the csv.
e.g.:
Output1.txt
LA2405w

Output2.txt
CN12345678

Output3.txt
L2245w

Output4.txt
CNU9876543


I've so far this script that is working will for the first line in the csv, but all following lines getting ignored.


Option Explicit
'On error resume next
Dim goFS : Set goFS = CreateObject( "Scripting.FileSystemObject" )
Dim aParts : aParts = split(goFS.OpenTextFile("./Input.txt").ReadLine(), ",")
Dim nF
For nF = 0 To UBound(aParts)
goFS.CreateTextFile(".\Output" & (NF + 1) &".txt").Write aParts(nF)
Next


How can I modify that script to also parse the other lines from Input.csv?

Thanks for any ideas.

[Dave "Crash" Dummy]

Here's how I'd do it. Nothing fancy, just one step at a time.

set fso=CreateObject("Scripting.FileSystemObject")
set inFile=fso.OpenTextFile("input.txt")
csv=inFile.readAll
inFile.close
set inFile=nothing

rows=split(csv,vbCRLF)
cols=split(rows(0),",")
dim heads()
redim heads(ubound(cols))

for n=0 to ubound(cols)
set heads(n)=fso.CreateTextFile("Output" & n & ".txt")
next

for m=0 to ubound(rows)
if len(rows(m)) then
for n=0 to ubound(cols)
heads(n).writeLine split(rows(m),",")(n)
next
end if
next

for n=0 to ubound(cols)
heads(n).close
set heads(n)=nothing
next

--
Crash

To understand evolution, study statistics, not biology.

[Christian Ort]

Crash, thank you for your quick reply and your script.

I've tried it on my example data and I get only 2 output files.
File 1 contains column A and file 2 contains column B.

I'd need on my example an output of total 4 files, each text file should only contain one single value (of a cell).

Any idea?

[R.Wieser]

Christian,

> The output should be 4 or more files, each file containing one
> column of the csv.

Problem: Your example shows two rows, each only having two columns. Where
do you get the "4 files" for "each column" from ?

Either you mean that each *cell* (from your example, exactly four of them)
should be saved seperatily, or you have forgotten a number of columns in
your example ....

Regards,
Rudy Wieser

Quote of the day:
"An answer can only be as good as the question asked."


-- Origional message:
Christian Ort <christ...@gmail.com> schreef in berichtnieuws
6ad41c22-4e5c-4633...@googlegroups.com...

[Dave "Crash" Dummy]

I guess I misunderstood. Based on your example, you want a file for each
cell, although I don't know why. This will work, if I understand what
you want.

Example.csv:
W000,X000,Y000,Z000
W001,X001,Y001,Z001
W002,X002,Y002,Z002
W003,X003,Y003,Z003
W004,X004,Y004,Z004

Script:
set fso=CreateObject("Scripting.FileSystemObject")
set inFile=fso.OpenTextFile("example.csv")

csv=inFile.readAll
inFile.close
set inFile=nothing

rows=split(csv,vbCRLF)

strng=replace(trim(join(rows))," ",",")
cells=split(strng,",")

for n=0 to ubound(cells)
set oFile=fso.CreateTextFile("Output" & n & ".txt")
oFile.writeLine cells(n)
oFile.close
set oFile=nothing
next

--
Crash

"Never underestimate the power of the Dark Side."
~ Obi-Wan Kenobi ~

[Dave "Crash" Dummy]

Here. One size fits all. This script will take a CSV file and spit out
files for rows, columns, and cells. Take your pick. Comment out the
ones you don't want.

set fso=CreateObject("Scripting.FileSystemObject")
set inFile=fso.OpenTextFile("example.csv")
csv=inFile.readAll
inFile.close
set inFile=nothing

rows=split(csv,vbCRLF)

cols=split(rows(0),",")
dim heads()
redim heads(ubound(cols))

for n=0 to ubound(cols)

set heads(n)=fso.CreateTextFile("Col_" & n & ".txt")

next

for m=0 to ubound(rows)
if len(rows(m)) then

set row=fso.CreateTextFile("Row_" & m & ".txt")
row.writeLine rows(m)
row.close
set row=nothing
for n=0 to ubound(cols)
set cel=fso.CreateTextFile("Cell_" & m & "x" &n&".txt")
cel.writeLine split(rows(m),",")(n)
cel.close
set cel=nothing

heads(n).writeLine split(rows(m),",")(n)
next
end if
next

for n=0 to ubound(cols)
heads(n).close
set heads(n)=nothing
next

--
Crash

[Christian Ort]

Thank you for this script,
that is doing exactly what I'm looking for.

I'm trying to learn vbs scripting, but sometimes confuses me :)