The CSV averages around 3MB and will contain the following columns:
Case ID
Create Date
Created By
Closed Date
Details (max 500 chars)
Resolution Details
Diary Editor
I then strip out the unwanted fields and make it so it is just column
G and this will then be column A as per my original design and
question.
Each 'Case' has a new entry (and therefore new line) within the CSV
so each row is unique. The identifying text would be 'Diary Editor'.
For info' from checking the records every record does start with a
date as well, however as you can see even within the cells different
dates will appear throughout.
Okay, good. Now, since this is a CSV file, I have assumed that any fields
(the last one in particular) that have commas in them are enclosed in quote
marks. Give the following a try... if I have done everything correctly (my
test case worked fine; but, of course, that was based on a made up file), it
will place the Case ID and Create Date in the first cell (in order for you
to be able to identify which record is which) followed by the parsed out
names in the columns following that.
I chose to initiate the macro from a (Control Toolbox, not Forms)
CommandButton placed on the worksheet (although you can use a UserForm with
a CommandButton on it if you wish), but you can change that if you want.
Here is the code behind the CommandButton's Click event...
Private Sub CommandButton1_Click()
Dim StartCell As Range
Set StartCell = Range("A2")
If StartCell.Text = "" Then ProcessFile "c:\temp\test.csv", StartCell
End Sub
Notice I chose A2 as the starting cell.... that is because I placed the
CommandButton over cell A1. If you want the output to start in a different
cell, just change the Set statement's reference to that cell. The
ProcessFile routine, which is called from this Click event, first checks to
make sure the starting cell is empty (no sense running the code if it has
already been executed earlier); all you do is pass the CSV file (path and
filename), using your path/filename in place of the test one shown above,
that you want to process and the starting cell reference into it as
arguments.
Next, below my signature, is the code that makes everything happen. While
you could put this code in the worksheet's code window, I placed it in a
Module that I added to the project thus making it available to any worksheet
in the workbook (in case you wanted to keep multiple files in one workbook
on separate worksheets). There are three procedures that make up the Module
code... the ProcessFile subroutine and two subroutines to help it out, one
is a modification of the GetAssignments subroutine I posted earlier
(modified to work within the structure I have created here) and the other is
a Split type function that will ignore the delimiter character if it occurs
within a quoted piece of text.
Hopefully, I made no mistakes and you can simply copy/paste everything into
the appropriate locations and go. Let me know if it worked or if you have
any problems with it.
Rick
Sub ProcessFile(PathandFileName As String, Target As Range)
Dim X As Long
Dim FF As Integer
Dim TotalFile As String
Dim Fields() As String
Dim Records() As String
FF = FreeFile
Open PathandFileName For Binary As #FF
TotalFile = Space(LOF(FF))
Get #FF, , TotalFile
Close #FF
On Error GoTo FixEvents
Records = Split(TotalFile, vbNewLine)
For X = 0 To UBound(Records)
Fields = SplitAroundQuotes(Records(X))
Cells(Target.Row + X, Target.Column).Value = _
Fields(0) & " (" & Fields(1) & ")"
Application.EnableEvents = False
GetAssignments Target.Offset(X, 0), Fields(UBound(Fields)), _
"The Assigned Individual has been changed to"
Application.EnableEvents = True
Next
Exit Sub
FixEvents:
Application.EnableEvents = True
End Sub
Sub GetAssignments(StartCell As Range, _
DiaryText As String, _
Phrase As String)
Dim X As Long
Dim Contents As String
Dim Fields() As String
Dim Words() As String
If Not IsNull(Contents) Then
Contents = Replace(DiaryText, vbLf, " ")
End If
Do While InStr(Contents, " ")
Contents = Replace(Contents, " ", " ")
Loop
Fields = Split(Contents, Phrase)
For X = 1 To UBound(Fields)
Words = Split(Trim$(Fields(X)), " ")
StartCell.Offset(0, X).Value = Words(0) & " " & _
Replace(Words(1), """", "")
Next
End Sub
Function SplitAroundQuotes(TextToSplit As String, _
Optional Delimiter As String = ",") As String()
Dim X As Long
Dim QuoteDelimited() As String
Dim WorkingArray() As String
QuoteDelimited = Split(TextToSplit, """")
For X = 1 To UBound(QuoteDelimited) Step 2
QuoteDelimited(X) = Replace$(QuoteDelimited(X), _
Delimiter, Chr$(0))
Next
TextToSplit = Join(QuoteDelimited, """")
TextToSplit = Replace$(TextToSplit, Delimiter, Chr$(1))
TextToSplit = Replace$(TextToSplit, Chr$(0), Delimiter)
WorkingArray = Split(TextToSplit, Chr$(1))
SplitAroundQuotes = WorkingArray
End Function
