Link to home
Start Free TrialLog in
Avatar of Luis Diaz
Luis DiazFlag for Colombia

asked on

VB Script: retrieve level2.level3 and generated multiple files based on values of column A

Hello experts,

I have the following file.csv which contains the following:

Organization;ID;Name
Tinder.porter.tirter.toto.type;58585;name1
Tinder.porter.tata.toto.tetetati.;58585;name2
Tinder.porter.tata.toto.tetetatu.;58585;name2

I need an script that do the following:

Create a file2_revised.csv by keeping just level2.level3 of Organization (warning: Organization can contains multiple levels).
Create multiple files based on and filter by Organization of file2_revised

Example after I launch the script:

Files2_revised.csv has been created with the following information:

Organization;ID;Name
porter.tirter;58585;name1
porter.tata;58585;name2
porter.tata;58585;name3

and Two additional files

porter.tirter.csv with the following information

Organization;ID;Name;
porter.tirter;58585;name1


porter.tata.csv with the following information

Organization;ID;Name;
porter.tata;58585;name2
porter.tata;58585;name3

Log loop requirements:

If folder of file.csv doesn’t exist logouput = Now variablefolder doesn’t exist.
If file.csv doesn’t exist logouput = Now file doesn’t exist doesn’t exist.
If folder path doesn’t finish with ‘\’ add ‘\'
Log file name = “log-file-split”
File name should be generated at : objFSO.GetAbsolutePathName(".")

Thank you in advance for your help!
Avatar of Bill Prew
Bill Prew
Is the file sorted by level 2.level 3, so all the lines with the same level2 and level 3 will be grouped together?  Or could they be spread apart in the file, like:

Tinder.porter.tata.toto.tetetati.;58585;name2
Tinder.porter.tirter.toto.type;58585;name1
Tinder.porter.tata.toto.tetetatu.;58585;name2

or

Tinder.porter.tirter.toto.type;58585;name1
Tinder.porter.tata.toto.tetetati.;58585;name2
Tinder.porter.tata.toto.tetetatu.;58585;name2
Sinder.porter.tirter.toto.type;58585;name1
Sinder.porter.tata.toto.tetetati.;58585;name2
Sinder.porter.tata.toto.tetetatu.;58585;name2

~bp
Avatar of Luis Diaz
Luis Diaz
Flag of Colombia image

ASKER

Hello Bill,

Thank you for your feedback, the lines in Files2_revised.csv can be spread apart.
SOLUTION
Avatar of Bill Prew
Bill Prew
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Luis Diaz
Luis Diaz
Flag of Colombia image

ASKER

Thank you so much. I will test it on Monday as I don't have Windows at home.
Avatar of Luis Diaz
Luis Diaz
Flag of Colombia image

ASKER

Hello Bill,

Thank you for this script.
I just realized that I don't need to keep just level2 & level3 as A I am already able to recover this information through another sql query.

So could you please help me to simplify the code ID: 40663827 in order to just split the data based on values of column A?
Sorry for this and thank you again for your help.
Avatar of Luis Diaz
Luis Diaz
Flag of Colombia image

ASKER

New specs requirements:

Open file1.csv
Generate each files based on the value of Column A

Log requirement of the previous file can be kept.

Thank you and sorry for this change.
Avatar of Bill Prew
Bill Prew
Give this a try - untested.

' Text file I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

'Create the file system object for creating folders:
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Define folders and files to work with
strScriptDir = objFSO.GetAbsolutePathName(".")
strBaseDir = "B:\EE\EE28634944"
strFileIn = strBaseDir & "\file.csv"
strLogFile = strScriptDir & "\log-file-split.txt"

' Open log file for appending
Set objLog = objFSO.OpenTextFile(strLogFile, ForAppending, True)

' Make sure input folder exists
If Not objFSO.FolderExists(strBaseDir) Then
   objLog.WriteLine Now & " ERROR: Input folder """ & strBaseDir & """ does not exist."
   Wscript.Quit
End If

' Make sure input file exists
If Not objFSO.FileExists(strFileIn) Then
   objLog.WriteLine Now & " ERROR: Input file """ & strFileIn & """ does not exist."
   Wscript.Quit
End If

' Read entire input file into an array
Set objFileIn = objFSO.OpenTextFile(strFileIn, ForReading, False, TriStateUseDefault)
arrLines = Split(objFileIn.ReadAll, VbCrLf)
objFileIn.Close
Set objFileIn = Nothing

' Process all lines from input file
For i = LBound(arrLines) To UBound(arrLines)

   ' Skip blank lines in input file
   If arrLines(i) <> "" Then

      ' Skip header line
      If i > LBound(arrLines) Then
         ' Process data line
         arrFields = Split(arrLines(i), ";")
         If UBound(arrFields) = 2 Then
            ' Write to individual file for just this ORG
            WriteOrgFile arrFields
         Else
            ' WARNING (not 3 fields in data line)
            objLog.WriteLine Now & " WARNING: Processing line """ & i+1 & """, found """ & UBound(arrFields)+1 & """ fields, must be 3."
         End If
      End If

   End If

Next

' Wrap up
objFileOut.Close
objLog.Close


' Subroutine to add a row to the individual file for the ORG
Sub WriteOrgFile(arrFields())
   ' Build name for this ORG file from data in ORG field (node2.node3)
   strFileOrg = strBaseDir & "\" & arrFields(0) & ".csv"

   ' If the file doesn't exist yet we need the header line
   If objFSO.FileExists(strFileOrg) Then 
      blnNeedHeader = False
   Else 
      blnNeedHeader = True
   End If

   ' Open this file for apending
   Set objFileOrg = objFSO.OpenTextFile(strFileOrg, ForAppending, True)

   ' Write header line of needed
   If blnNeedHeader Then
      objFileOrg.WriteLine strHeader
   End If

   ' Write data line
   objFileOrg.WriteLine Join(arrFields, ";")
End Sub

Open in new window

~bp
Avatar of Luis Diaz
Luis Diaz
Flag of Colombia image

ASKER

Hello Bill,
Thank you for this code. I will test it tomorrow.
Avatar of Luis Diaz
Luis Diaz
Flag of Colombia image

ASKER

Hello Bill,

Very strange. I got an error message in line 12 "Expected end of statement".
Thank you again for your help.
Avatar of Luis Diaz
Luis Diaz
Flag of Colombia image

ASKER

The error message concerns the code ID: 40669451, the code ID: 40663827 works perfectly.
Avatar of Bill Prew
Bill Prew
Can you post up the exact script you ran (with any changes you made), as well as a capture of the error?  I don't see anything bad about the line 12 I posted.

~bp
Avatar of Luis Diaz
Luis Diaz
Flag of Colombia image

ASKER

Ok, I will send you all the info tomorrow.
Avatar of Luis Diaz
Luis Diaz
Flag of Colombia image

ASKER

Hello,

The line 12 it was because I dind't copy properly your code I miss ' char in line 1

This is the code that I used:
' Text file I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

'Create the file system object for creating folders:
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Define folders and files to work with
strScriptDir = objFSO.GetAbsolutePathName(".")
strBaseDir = "C:\Users\SESA249410\Desktop\Scripts-revised\test\folder\spot-mstt-match-multiple-generationa"
strFileIn = strBaseDir & "\test.csv"
strLogFile = strScriptDir & "\log-file-split.txt"

' Open log file for appending
Set objLog = objFSO.OpenTextFile(strLogFile, ForAppending, True)

' Make sure input folder exists
If Not objFSO.FolderExists(strBaseDir) Then
   objLog.WriteLine Now & " ERROR: Input folder """ & strBaseDir & """ does not exist."
   Wscript.Quit
End If

' Make sure input file exists
If Not objFSO.FileExists(strFileIn) Then
   objLog.WriteLine Now & " ERROR: Input file """ & strFileIn & """ does not exist."
   Wscript.Quit
End If

' Read entire input file into an array
Set objFileIn = objFSO.OpenTextFile(strFileIn, ForReading, False, TriStateUseDefault)
arrLines = Split(objFileIn.ReadAll, VbCrLf)
objFileIn.Close
Set objFileIn = Nothing

' Process all lines from input file
For i = LBound(arrLines) To UBound(arrLines)

   ' Skip blank lines in input file
   If arrLines(i) <> "" Then

      ' Skip header line
      If i > LBound(arrLines) Then
         ' Process data line
         arrFields = Split(arrLines(i), ";")
         If UBound(arrFields) = 2 Then
            ' Write to individual file for just this ORG
            WriteOrgFile arrFields
         Else
            ' WARNING (not 3 fields in data line)
            objLog.WriteLine Now & " WARNING: Processing line """ & i+1 & """, found """ & UBound(arrFields)+1 & """ fields, must be 3."
         End If
      End If

   End If

Next

' Wrap up
objFileOut.Close
objLog.Close


' Subroutine to add a row to the individual file for the ORG
Sub WriteOrgFile(arrFields())
   ' Build name for this ORG file from data in ORG field (node2.node3)
   strFileOrg = strBaseDir & "\" & arrFields(0) & ".csv"

   ' If the file doesn't exist yet we need the header line
   If objFSO.FileExists(strFileOrg) Then 
      blnNeedHeader = False
   Else 
      blnNeedHeader = True
   End If

   ' Open this file for apending
   Set objFileOrg = objFSO.OpenTextFile(strFileOrg, ForAppending, True)

   ' Write header line of needed
   If blnNeedHeader Then
      objFileOrg.WriteLine strHeader
   End If

   ' Write data line
   objFileOrg.WriteLine Join(arrFields, ";")
End Sub

Open in new window


And I got the following error message:
User generated image
Another remark, in the log file I got for the various lines:
Processing line "2", found "4" fields, must be 3.
The following loop need to be removed as the values in column A should not have any restriction:
  
 If UBound(arrFields) = 2 Then
            ' Write to individual file for just this ORG
            WriteOrgFile arrFields
         Else
            ' WARNING (not 3 fields in data line)
            objLog.WriteLine Now & " WARNING: Processing line """ & i+1 & """, found """ & UBound(arrFields)+1 & """ fields, must be 3."

Open in new window


Thank you in advance for your help.
Avatar of Bill Prew
Bill Prew
Okay, to resolve the error, the line with objFileOut needs to be removed, that was a cut and paste error.

' Text file I/O constants
Const ForReading = 1
Const ForWriting = 2
Const ForAppending = 8

'Create the file system object for creating folders:
Set objFSO = CreateObject("Scripting.FileSystemObject")

' Define folders and files to work with
strScriptDir = objFSO.GetAbsolutePathName(".")
strBaseDir = "C:\Users\SESA249410\Desktop\Scripts-revised\test\folder\spot-mstt-match-multiple-generationa"
strFileIn = strBaseDir & "\test.csv"
strLogFile = strScriptDir & "\log-file-split.txt"

' Open log file for appending
Set objLog = objFSO.OpenTextFile(strLogFile, ForAppending, True)

' Make sure input folder exists
If Not objFSO.FolderExists(strBaseDir) Then
   objLog.WriteLine Now & " ERROR: Input folder """ & strBaseDir & """ does not exist."
   Wscript.Quit
End If

' Make sure input file exists
If Not objFSO.FileExists(strFileIn) Then
   objLog.WriteLine Now & " ERROR: Input file """ & strFileIn & """ does not exist."
   Wscript.Quit
End If

' Read entire input file into an array
Set objFileIn = objFSO.OpenTextFile(strFileIn, ForReading, False, TriStateUseDefault)
arrLines = Split(objFileIn.ReadAll, VbCrLf)
objFileIn.Close
Set objFileIn = Nothing

' Process all lines from input file
For i = LBound(arrLines) To UBound(arrLines)

   ' Skip blank lines in input file
   If arrLines(i) <> "" Then

      ' Skip header line
      If i > LBound(arrLines) Then
         ' Process data line
         arrFields = Split(arrLines(i), ";")
         ' Write to individual file for just this ORG
         WriteOrgFile arrFields
      End If

   End If

Next

' Wrap up
objLog.Close


' Subroutine to add a row to the individual file for the ORG
Sub WriteOrgFile(arrFields())
   ' Build name for this ORG file from data in ORG field (node2.node3)
   strFileOrg = strBaseDir & "\" & arrFields(0) & ".csv"

   ' If the file doesn't exist yet we need the header line
   If objFSO.FileExists(strFileOrg) Then 
      blnNeedHeader = False
   Else 
      blnNeedHeader = True
   End If

   ' Open this file for apending
   Set objFileOrg = objFSO.OpenTextFile(strFileOrg, ForAppending, True)

   ' Write header line of needed
   If blnNeedHeader Then
      objFileOrg.WriteLine strHeader
   End If

   ' Write data line
   objFileOrg.WriteLine Join(arrFields, ";")
End Sub

Open in new window

~bp
Avatar of Luis Diaz
Luis Diaz
Flag of Colombia image

ASKER

Ok I don't get any error but I am still having the log:
Processing line "2", found "4" fields, must be 3.

Can we remove this condition as field A cannot be 3. Thank you in advance for your help.
ASKER CERTIFIED SOLUTION
Avatar of Bill Prew
Bill Prew
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Luis Diaz
Luis Diaz
Flag of Colombia image

ASKER

Perfect! It works!