AboutPricingCommunityTeamsStart Free TrialLog in
Avatar of Luis Diaz
Luis Diaz
Flag 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!
VB Script
Avatar of undefined
Last Comment
Luis Diaz
8/22/2022 - Mon
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
Luis Diaz
ASKER
Hello Bill,

Thank you for your feedback, the lines in Files2_revised.csv can be spread apart.
SOLUTION
Bill Prew
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Luis Diaz
ASKER
Thank you so much. I will test it on Monday as I don't have Windows at home.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Luis Diaz
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.
Luis Diaz
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.
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Luis Diaz
ASKER
Hello Bill,
Thank you for this code. I will test it tomorrow.
Luis Diaz
ASKER
Hello Bill,

Very strange. I got an error message in line 12 "Expected end of statement".
Thank you again for your help.
Luis Diaz
ASKER
The error message concerns the code ID: 40669451, the code ID: 40663827 works perfectly.
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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
Luis Diaz
ASKER
Ok, I will send you all the info tomorrow.
Luis Diaz
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:
Capture.GIF
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
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
Luis Diaz
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
Bill Prew
THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Luis Diaz
ASKER
Perfect! It works!
Your help has saved me hundreds of hours of internet surfing.
fblack61
Plans and Pricing
Resources
Product
Podcasts
Careers
Contact Us
Teams
Certified Expert Program
Credly Partnership
Udemy Partnership
Privacy Policy
Terms of Use

© 1996-2023 Experts Exchange, LLC. All rights reserved. Covered by US Patent