asked on

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.

Bill Prew

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 Diaz

ASKER

Hello,
Yes ex if role have 2 comma delim Id and name will always have 2 comma delim

Bill Prew

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 Diaz

ASKER

Thank you very much. I will test it tomorrow as I don't have windows at home.

Tony Pitt

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

Tony Pitt

I took too long, Bill :-(

Luis Diaz

ASKER

@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?

aikimark

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 Diaz

ASKER

Yes, I made a mistake in the initial spec.

aikimark

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

NVIT

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 Diaz

ASKER

Thank your all for those solutions. I will test its tomorrow the three solutions.

NVIT

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 Prew

@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

ASKER CERTIFIED SOLUTION

Bill Prew

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Luis Diaz

ASKER

@Bill: Your second code works perfectly, thank you!
@Aikimark: I got an error message in line 31 char 17

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 Diaz

ASKER

Excellent!

NVIT

.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 Diaz

ASKER

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