We help IT Professionals succeed at work.

VB Script : split data

Luis Diaz
Luis Diaz asked
on
Hello Experts I have one csv file (test.csv) with the following information:

ID;Pr name;Role;User name; User ID
144;sixi;MP,PR,TT;Thomas,Oliver,Pedro;1,2,3
555;titi;PR, PR,TT;Alain,Pascal,Javier;8,5,6

As you can see Role, User name and User ID have concatenated values with ","separator.

The  objective of is generate a test_v2.csv in which all concatenated data is splitted in multiple lines as following:

ID;Pr name;Role;User name; User ID
144;sixi;MP;Thomas;1
144;sixi;PR,Oliver;2
144;sixi;TT;3
555;titi;PR;Alain;8
555;titi;PRPascal;5
555;titi;TT;Javier;6


Thank you in advance for your help.
Comment
Watch Question

Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
So, Role, Name and ID will always have the same number of comma delim'ed elements in them, and you want to match up the first set of values on one output line, then the second, etc?

~bp
Luis DiazIT consultant

Author

Commented:
Hello,
Yes ex if role have 2 comma delim Id and name will always have 2 comma delim
Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
Okay, give this a try, should do what you described.  Save it as a VBS and then run like:

cscript EE28627498.vbs test.csv test_v2.csv

or

cscript EE28627498.vbs test.csv

The first version will read from a file and write to a new one, the second version will read from a file, and then overwrite it with the new output.

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
 
' Get input file name from command line parm
If (WScript.Arguments.Count > 0) Then
   strInFile = WScript.Arguments(0)
Else
   WScript.Echo "No input filename specified."
   WScript.Quit
End If

' Get output file name from command line parm
If (WScript.Arguments.Count > 1) Then
   strOutFile = WScript.Arguments(1)
Else
   strOutFile = strInFile
End If

' Read file into an array
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
arrLines = Split(objFile.ReadAll, VbCrLf)
objFile.Close

' Open output file
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)

' Loop through all lines of the file, create new rows
For i = 0 To UBound(arrLines)
   If arrLines(i) <> "" Then
      arrFields = Split(arrLines(i), ";")
      strID = arrFields(0)
      strPrName = arrFields(1)
      arrRole = Split(arrFields(2), ",")
      arrUserName = Split(arrFields(3), ",")
      arrUserID = Split(arrFields(4), ",")
      If UBound(arrRole) = UBound(arrUserName) And UBound(arrRole) = UBound(arrUserID) Then
         For j = 0 To UBound(arrRole)
            objFile.WriteLine strID & ";" & strPrName & ";" & arrRole(j) & ";" & arrUserName(j) & ";" & arrUserID(j)
         Next
      Else
         Wscript.Echo "ERROR: Invalid data """ & arrLines(i) & """"
      End If
   End If
Next

' Write output file
objFile.Close

Open in new window

~bp
Luis DiazIT consultant

Author

Commented:
Thank you very much. I will test it tomorrow as I don't have windows at home.
I don't claim this is elegant, but I think it does what you want...

Run the Macro called Macro1 to carry out the conversion.  It assumes that the file is called C:\Temp\test.csv.  You will probably need to make the user interface more elegant!!!

/T
Split-values.xlsm
I took too long, Bill :-(
Luis DiazIT consultant

Author

Commented:
@bill,
I am looking the code and I was wondering if we can directly set up a folder and a filename variables within the code and in case of not overwriting an output file name?
Top Expert 2014

Commented:
Shouldn't that result be this?
ID;Pr name;Role;User name; User ID
144;sixi;MP;Thomas;1
144;sixi;PR,Oliver;2
144;sixi;TT;Pedro;3
555;titi;PR;Alain;8
555;titi;PRPascal;5
555;titi;TT;Javier;6

Open in new window

Luis DiazIT consultant

Author

Commented:
Yes, I made a mistake in the initial spec.
Top Expert 2014

Commented:
here is a VBScript that uses regular expressions to do the parsing
    Const ForReading = 1, ForWriting = 2, ForAppending = 8
    Dim oFS, oTS
    Dim strFileData, strHeader
    
    Dim oRE
    Dim oMatches
    Dim oM
    Dim oRE2
    Dim oMatches2
    Dim lngSM2
    
    Set oFS = CreateObject("Scripting.FileSystemObject")
    Set oTS = oFS.OpenTextFile("c:\users\mark\downloads\test.csv", ForReading, False)
    strHeader = oTS.readline
    strFileData = oTS.readall
    oTS.Close

    Set oRE = CreateObject("vbscript.regexp")
    oRE.Global = True
    oRE.Pattern = "([^;]*);([^;]*);(.*?)(?:\r\n|$)"
    Set oRE2 = CreateObject("vbscript.regexp")
    oRE2.Global = True
    oRE2.Pattern = "(\w+)\b"

    If oRE.test(strFileData) Then
        Set oTS = oFS.OpenTextFile("c:\users\mark\downloads\test_v2.csv", ForWriting, True, False)
        Set oMatches = oRE.Execute(strFileData)
        For Each oM In oMatches
            Set oMatches2 = oRE2.Execute(oM.submatches(2))
            For lngSM2 = 0 To (oMatches2.Count / 3) - 1
                oTS.writeline oM.submatches(0) & ";" & oM.submatches(1) & ";" & oMatches2(lngSM2).submatches(0) & ";" & oMatches2(lngSM2 + 3).submatches(0) & ";" & oMatches2(lngSM2 + 6).submatches(0)
            Next
        Next
    End If
    
    oTS.Close

Open in new window

NVITEnd-user support

Commented:
I know the request is for vbs but more as a study for myself, here's a .bat method:
@echo off
set FNSrc=test.csv
set FNTgt=test_v2.csv
for /f "tokens=1-26 delims=;," %%a in (%FNSrc%) do (
   if /i "%%a" neq "ID" (
      echo %%a;%%b;%%c;%%f;%%i>>%FNTgt%
      echo %%a;%%b;%%d;%%g;%%j>>%FNTgt%
      echo %%a;%%b;%%e;%%h;%%k>>%FNTgt%
   )
)
goto :eof

Open in new window

Luis DiazIT consultant

Author

Commented:
Thank your all for those solutions. I will test its tomorrow the three solutions.
NVITEnd-user support

Commented:
Updated version with header:
@echo off
set FNSrc=test.csv
set FNTgt=test_v2.csv
for /f "tokens=1-26 delims=;," %%a in (%FNSrc%) do (
   if /i "%%a" neq "ID" (
      echo %%a;%%b;%%c;%%f;%%i>>%FNTgt%
      echo %%a;%%b;%%d;%%g;%%j>>%FNTgt%
      echo %%a;%%b;%%e;%%h;%%k>>%FNTgt%
   ) else (
      echo %%a;%%b;%%c;%%d;%%e>>%FNTgt%
   )
)
goto :eof

Open in new window

Bill PrewTest your restores, not your backups...
Expert of the Year 2019
Top Expert 2016

Commented:
@nvIT,

The problem I see with thatis you are assuming there are always 3 elements in the subfields, but I think that can be variable.

~bp
Test your restores, not your backups...
Expert of the Year 2019
Top Expert 2016
Commented:
Certainly can hard code, maybe something like this:

' Define needed constants
Const ForReading = 1
Const ForWriting = 2
Const TriStateUseDefault = -2
 
' Define input and output files
strInFile = "C:\temp\in.csv"
strOutFile = "C:\temp\out.csv"

' Read file into an array
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile(strInFile, ForReading, False, TriStateUseDefault)
arrLines = Split(objFile.ReadAll, VbCrLf)
objFile.Close

' Open output file
Set objFile = objFSO.OpenTextFile(strOutFile, ForWriting, True)

' Loop through all lines of the file, create new rows
For i = 0 To UBound(arrLines)
   If arrLines(i) <> "" Then
      arrFields = Split(arrLines(i), ";")
      strID = arrFields(0)
      strPrName = arrFields(1)
      arrRole = Split(arrFields(2), ",")
      arrUserName = Split(arrFields(3), ",")
      arrUserID = Split(arrFields(4), ",")
      If UBound(arrRole) = UBound(arrUserName) And UBound(arrRole) = UBound(arrUserID) Then
         For j = 0 To UBound(arrRole)
            objFile.WriteLine strID & ";" & strPrName & ";" & arrRole(j) & ";" & arrUserName(j) & ";" & arrUserID(j)
         Next
      Else
         Wscript.Echo "ERROR: Invalid data """ & arrLines(i) & """"
      End If
   End If
Next

' Write output file
objFile.Close

Open in new window

~bp
Luis DiazIT consultant

Author

Commented:
@Bill: Your second code works perfectly, thank you!
@Aikimark: I got an error message in line 31 char 17
Capture.GIF and the split is done only for the first line.
@NewVillageIT we can have more or less than three fields to split so the bat doesn't meet the requirements.
@Tony Pitt: I cannot launch this through VBA as I need to launch this action through a Schedule job without using Excel, thank you anyway
Thank you all!
Luis DiazIT consultant

Author

Commented:
Excellent!
NVITEnd-user support

Commented:
.bat version revised for variable columns:
@echo off
SETLOCAL ENABLEDELAYEDEXPANSION
set FNSrc=RearrTbl.txt
set FNTgt=RearrTbl_v2.csv
for /f "tokens=1-5 delims=;" %%a in (%FNSrc%) do (
   set chk=%%a
   if /i "!chk:~0,2!" neq "ID" (
      set /a count=0
      for /d %%A in (%%c) do (set /a count=count+1 & set CVar!count!=%%A)
      set /a count=0
      for /d %%A in (%%d) do (set /a count=count+1 & set DVar!count!=%%A)
      set /a count=0
      for /d %%A in (%%e) do (set /a count=count+1 & set EVar!count!=%%A
         call echo %%a;%%b;%%CVar!count!%%;%%DVar!count!%%;%%EVar!count!%%>>%FNTgt%)
   ) else (
      echo %%a;%%b;%%c;%%d;%%e>%FNTgt%
   )
)
goto :eof

Open in new window

Luis DiazIT consultant

Author

Commented:
@NewVillageIT: I will test it tomorrow, thank you for this code.