Groups | Search | Server Info | Keyboard shortcuts | Login | Register [http] [https] [nntp] [nntps]

Groups > microsoft.public.scripting.vbscript > #11456

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

Show all headers | View raw

Christian Ort wrote:
> Am Dienstag, 29. November 2016 17:20:16 UTC+1 schrieb Dave "Crash" Dummy:
>> Christian Ort wrote:
>>> 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.
>> 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

-- 
Crash

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

Back to microsoft.public.scripting.vbscript | Previous | Next — Previous in thread | Next in thread | Find similar

Thread

vb script to read csv file and put each value (column) in separate text file Christian Ort <christian.ort@gmail.com> - 2016-11-29 07:09 -0800
  Re: vb script to read csv file and put each value (column) in separate text file "Dave \"Crash\" Dummy" <invalid@invalid.invalid> - 2016-11-29 11:20 -0500
    Re: vb script to read csv file and put each value (column) in separate text file Christian Ort <christian.ort@gmail.com> - 2016-11-29 09:03 -0800
      Re: vb script to read csv file and put each value (column) in separate text file "Dave \"Crash\" Dummy" <invalid@invalid.invalid> - 2016-11-29 13:40 -0500
        Re: vb script to read csv file and put each value (column) in separate text file "Dave \"Crash\" Dummy" <invalid@invalid.invalid> - 2016-11-29 16:52 -0500
          Re: vb script to read csv file and put each value (column) in separate text file Christian Ort <christian.ort@gmail.com> - 2016-11-30 02:37 -0800
  Re: vb script to read csv file and put each value (column) in separatetext file "R.Wieser" <address@not.available> - 2016-11-29 18:35 +0100

csiph-web