VB Script : split data

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.
LVL 1
LD16Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bill PrewCommented:
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
0
LD16Author Commented:
Hello,
Yes ex if role have 2 comma delim Id and name will always have 2 comma delim
0
Bill PrewCommented:
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
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

LD16Author Commented:
Thank you very much. I will test it tomorrow as I don't have windows at home.
0
Tony PittCommented:
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
0
Tony PittCommented:
I took too long, Bill :-(
0
LD16Author 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?
0
aikimarkCommented:
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

0
LD16Author Commented:
Yes, I made a mistake in the initial spec.
0
aikimarkCommented:
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

0
NVITCommented:
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

0
LD16Author Commented:
Thank your all for those solutions. I will test its tomorrow the three solutions.
0
NVITCommented:
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

0
Bill PrewCommented:
@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
0
Bill PrewCommented:
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
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
LD16Author 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!
0
LD16Author Commented:
Excellent!
0
NVITCommented:
.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

0
LD16Author Commented:
@NewVillageIT: I will test it tomorrow, thank you for this code.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
VB Script

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.