Splitting CSV column data into new CSV file using VBScript

Question

I have a CSV file where 2 columns contain several different text values e.g.

Column 1: Reptiles, Health, Hygiene  
Column 2: Purity

I need to use VBscript to split these columns into a new CSV file without changing the current file, expected output in new CSV file shown below:

Column 1    Column 2
Reptiles        Reptiles
Health          Health
Hygiene         Hygiene
Purity          Purity

Unfortunately(?) it must be done with VB Script and nothing else.

Here is an example of how the data looks (of course the data consistently repeats with some extra entries through the same columns in file 1.

And here is an example of how it needs to look but it needs to repeat down until all unique entries from Column 1 and 2 in the original file have been input as a single entry to Column 1 in the new file and copied to Column 2 in the same new file. e.g.

Examples in text format as requested:

Original file:

Column 1,Column 2
"Reptiles, Health, Hygiene",Purity

New File:

Column 1,Column 2
Reptiles,Reptiles
Health,Health
Hygiene,Hygiene
Purity,Purity

"I need to use VBscript" Are you sure? Why? Who says that? There are better options available on Windows. Powershell for example. I have a hard time imagining a reason why a new program must be VBScript in 2018. Also, don't describe your CSV file. Show your CSV file, and show precisely what the output should look like. — Tomalak
– Tomalak, Commented May 4, 2018 at 10:46
I need to use Vb Script as that is what has been requested. I understand there may be other options but as VB script is one of those options I'd like to stick to the 'plan'. Essentially what I have explained above is what is required, there is column X ad Column Y that contain the data as shown with X having some comma separated values and Y having single text values that need to be split and input into a new CSV file on Column A and B with each unique value from X and Y in the original spread sheet being a new row in column A & B in the new spread sheet. — Ecinev
– Ecinev, Commented May 4, 2018 at 11:17
I have added screenshot examples above. I cannot get around using VBScript for this unfortunately, it is a specific request and, though I agree there are other newer more convenient options I am unable to get around using Vb Script in this instance rather than it be a "I wanna". — Ecinev
– Ecinev, Commented May 4, 2018 at 11:38
CSV is a text format, so please open the file(s) in a text editor and copy/paste the (sample) content. — Ansgar Wiechers
– Ansgar Wiechers, Commented May 4, 2018 at 12:04
Do you need me to add further formats of my examples? I have provided 2 different formats of the data and required changes above. Does this provide a clear enough picture of what is required? — Ecinev
– Ecinev, Commented May 4, 2018 at 12:06

dbmitch · Accepted Answer · 2018-05-06 04:22:34Z

I think this is a simple matter of using the FileSystemObject with Split function. Assuming each input line is just one set of data you can remove the double quotes and process from there

Try this VB script out (edited to process header line separately):

Const Overwrite = True
Set ObjFso = CreateObject("Scripting.FileSystemObject")

Set ObjOutFile = ObjFso.CreateTextFile("My New File Path", Overwrite)
Set ObjInFile = ObjFso.OpenTextFile("My Old File Path")

' Skip processing first header line and just write it out as is
strLine = ObjInFile.ReadLine
ObjOutFile.WriteLine strLine

Do Until ObjInFile.AtEndOfStream
    ' Remove all double quotes to treat this as one set of data
    strLine = Replace(ObjInFile.ReadLine, """","")
    varData = Split(strLine,",")
    ' Write out each element twice into its own line
    For i = 0 to uBound(varData)
        ObjOutFile.WriteLine varData(i) & "," & varData(i) 
    Next i
Loop

ObjInFile.Close
ObjOutFile.Close

Collectives™ on Stack Overflow

Splitting CSV column data into new CSV file using VBScript

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related