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]

Post by Christian Ort
Hello,
I'm looking for a small script that is able to split a csv file into multiple text files.
LA2405w,CN12345678
L2245w,CNU9876543
etc,etc
The output should be 4 or more files, each file containing one column of the csv.
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.

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

[Christian Ort]

Post by Dave "Crash" Dummy

Post by Christian Ort
Hello,
I'm looking for a small script that is able to split a csv file into multiple text files.
LA2405w,CN12345678
L2245w,CNU9876543
etc,etc
The output should be 4 or more files, each file containing one column of the csv.
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.

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.

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?

[Dave "Crash" Dummy]

Post by Christian Ort

Post by Dave "Crash" Dummy

Post by Christian Ort
Hello,
I'm looking for a small script that is able to split a csv file into multiple text files.
LA2405w,CN12345678
L2245w,CNU9876543
etc,etc
The output should be 4 or more files, each file containing one column of the csv.
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.

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.

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?

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

[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

[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 :)

Post by 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
"Never underestimate the power of the Dark Side."
~ Obi-Wan Kenobi ~

[R.Wieser]

Christian,

Post by Christian Ort
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."

Post by Christian Ort
Hello,
I'm looking for a small script that is able to split a csv file into multiple text files.
LA2405w,CN12345678
L2245w,CNU9876543
etc,etc
The output should be 4 or more files, each file containing one column of the csv.
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.

Post by Christian Ort
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.