Parse .txt file

[Nosstech]

I have my code going to a specific folder and writing the path to excel. I want to split up the path into different columns. See below

This is my current output in column A
C:-Documents and Settings-KCBLN00-My Documents-My Sales-Customer 01-01 - John Doe - Peperoni Pizza


This is what my end result to be:
Column A "Sales Number": 1234
Column B "Qty": 01
Column C "Customer Name": John Doe
Column D "Product Purchased": Peperoni Pizza

I can write the column headings (eg "Sales Number"), but I am not sure how to parse the rest of the data.

Is it easier to export as a txt file and then have the script import it with a "-" as the seperator?

[Oorang]

I'm not sure I understand what you are asking/trying to do. Would you please clarify your question with a little more detail (and possibly the code in question)?

[Nosstech]

With my current code (see below), I get the file path in column A (C:-Documents and Settings-KCBLN00-My Documents-My Music-1980's Classic R&B-01-Kool & The Gang - Celebration).

I want to delete part of the path (C:-Documents and Settings-KCBLN00-My Documents-My Music-) and separate the other text into different columns (Artist (Kool & The Gang) , Song (Celebration), Track(01) and Album(1980's Classic R&B)).

The code below is for personal use, but now I want to convert it to something I can use at work for sales auditing. I hope I explained it a little bit better this time.

Option Explicit

Sub TestListFilesInFolder()
Workbooks.Add ' create a new workbook for the file list
' add headers
With Range("A1")
Range("B1").Formula = "Song_Title"
Range("C1").Formula = "Track"
Range("D1").Formula = "Album_Title"
.Formula = "Artist"
.Font.Bold = True
.Font.Size = 12
End With
ListFilesInFolder "C:\Documents and Settings\KCBLN00\My Documents\My Music\", _
True
' list all files included subfolders
End Sub
Sub ListFilesInFolder(SourceFolderName As String, IncludeSubfolders As Boolean)
' lists information about the files in SourceFolder
' example: ListFilesInFolder "file path", True
Dim FSO As Scripting.FileSystemObject
Dim SourceFolder As Scripting.Folder, SubFolder As Scripting.Folder
Dim FileItem As Scripting.File
Dim r As Long
Set FSO = New Scripting.FileSystemObject
Set SourceFolder = FSO.GetFolder(SourceFolderName)
r = Range("A65536").End(xlUp).Row + 1
For Each FileItem In SourceFolder.Files
' display file properties
Cells(r, 1).Formula = FileItem.Path
' use file methods (not proper in this example)
' FileItem.Copy "C:\FolderName\Filename.txt", True
' FileItem.Move "C:\FolderName\Filename.txt"
' FileItem.Delete True
r = r + 1 ' next row number
Next FileItem
If IncludeSubfolders Then
For Each SubFolder In SourceFolder.SubFolders
ListFilesInFolder SubFolder.Path, True
Next SubFolder
End If
Columns("A:H").AutoFit
Set FileItem = Nothing
Set SourceFolder = Nothing
Set FSO = Nothing
ActiveWorkbook.Saved = True
' Remove path
Worksheets("Sheet1").Cells.Replace _
What:="C:\Documents and Settings\KCBLN00\My Documents\My Music\log\", _
Replacement:="", LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
' Remove file extention of .mp3
Worksheets("Sheet1").Cells.Replace What:=".mp3", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
' Remove file extention of .db
Worksheets("Sheet1").Cells.Replace What:="*.db", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
' Remove file extention of .txt
Worksheets("Sheet1").Cells.Replace What:="*.txt", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
' Remove file extention of .ini
Worksheets("Sheet1").Cells.Replace What:="*.ini", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
' Remove the / between Platinum and disc
Worksheets("Sheet1").Cells.Replace What:="Platinum\Disc", _
Replacement:="Platinum Disc", LookAt:=xlPart, SearchOrder:=xlByRows, _
MatchCase:=False
' Relace the / between with -
Worksheets("Sheet1").Cells.Replace What:="\", Replacement:="-", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False
' Removes ablum art
Worksheets("Sheet1").Cells.Replace What:="*.jpg*", Replacement:="", _
LookAt:=xlPart, SearchOrder:=xlByRows, MatchCase:=False

Dim x As Long
With ActiveSheet
For x = .Cells.SpecialCells(xlCellTypeLastCell).Row To 1 Step -1
If WorksheetFunction.CountA(.Rows(x)) = 0 Then
ActiveSheet.Rows(x).Delete
End If
Next
End With
End Sub

[stanl]

I agree with Aaron, but assuming you will be filling in the Column headings for A-C, and the current data in Column A is a string, delimited by "-", this might get you started, or at least isolate the 3 variables of interest.


Sub parse()
s = "C:-Documents and Settings-KCBLN00-My Documents-My Sales-Customer 01-01 - John Doe - Peperoni Pizza"
For i = 1 To 6
s = Mid(s, InStr(s, "-") + 1)
Next i
MsgBox s

End Sub



Stan

[Nosstech]

Stan,

Thanks, however I'm looking for the code to write the data to the worksheet, not a text box. There are about 1,000 different files that need to be logged in a master file.

-Barry

[mdmackillop]

Check out the Split function.