CODEWITHSUNDEEP
csvparsingvbscript
Ecinev
Ecinev

Reputation: 13

Splitting CSV column data into new CSV file using VBScript

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.

Original file

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.

New file

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

Upvotes: 1

Views: 2472

Answers (1)

dbmitch
dbmitch

Reputation: 5386

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

Upvotes: 1

Related Questions