Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delimiter(?)
How can I separate data preceded by '<<' (without the quotes) into different columns?
Here's sample of my text file: << NOT a relative << Anyone get a Letter from Robert? << A <<E-mail addresses <<What's up? << Visit << << <<Sorry <<Photos from the front << New Address for Robert << FW: (no subject) << FW: <<FW: (no subject) <<FW: <<Matt <<Matt <<Jeremy 's Interview << Video of 's i << Clemency Letter for S/Sgt Matthew << Clemency Letter << Matthew <<Jess's letter << Matthew << M <<Fwd: <<Fwd: Fw: Beth Rubin & Jersey Tomatoes on TV <<The other a << Sorry << <<Sorry <<Photos from the front << New Addre <<From Merrilea <<Fwd: Picture of the year <<FW: Karen G's newest G << and he's off << and he's off <<and he's off <<More Info << << << <<FW: << <<Untitled <<From Merrilea << Matthew << Matthew << Matthew << <<Google Alert - matthew- -minnesota -rugby <<Itinerary - MATT << Mars..Historical event coming <<Fwd: Mars..Historical event com <<In cognito << local Cotterill mini-reunion <<Whiskey Locker is << << New Address for Robert << New Address for Robert |
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delimiter(?)
First thing I see is the << seems to be a common delimiter.
Next thing I see is the rest of the file contents have no consistent structure! I suggest the following steps (in the order given): Step1 Replace all " <<" with "|" (the pipe character) so you have a simple delimiter; Step2 Replace all "" and "<" with "" (IOW delete all); Step3 Specify "|" as the column delimiter. A VBA solution follows... Sub ParseTextFile() Dim sFile$, sTextIn$, n&, vData, vTmp(), rng As Range On Error GoTo Cleanup 'Get the text from file sFile = Get_FileToOpen: If sFile = "" Then Exit Sub sTextIn = ReadTextFile(sFile) 'Edit file contents sTextIn = Replace(sTextIn, " <<", "|") sTextIn = Replace(sTextIn, "", "") sTextIn = Replace(sTextIn, "", "") 'Parse the contents into a 2D array vData = Split(sTextIn, vbCrLf): ReDim vTmp(UBound(vData)) For n = LBound(vData) To UBound(vData) vTmp(n) = vData(n) Next 'n Xform_1DimArrayTo2D vTmp, "|" Set rng = Cells(1, 1).Resize(UBound(vTmp), UBound(vTmp, 2)) With rng .value = vTmp: .Columns.AutoFit End With Cleanup: Set rng = Nothing End Sub Function Get_FileToOpen$(Optional FileTypes$ = "All Files ""*.*"", (*.*)") Dim vFile vFile = Application.GetOpenFilename(FileTypes) Get_FileToOpen = IIf(vFile = False, "", vFile) End Function Function ReadTextFile$(Filename$) ' Reads large amounts of data from a text file in one single step. Dim iNum% On Error GoTo ErrHandler iNum = FreeFile(): Open Filename For Input As #iNum ReadTextFile = Space$(LOF(iNum)) ReadTextFile = Input(LOF(iNum), iNum) ErrHandler: Close #iNum: If Err Then Err.Raise Err.Number, , Err.Description End Function 'ReadTextFile() Sub Xform_1DimArrayTo2D(Arr(), Delimiter$) ' Restructures a 1D dynamic 0-based array to a fixed 2D 1-based array ' Arguments: ' Arr$() array of delimited strings to be converted ' Delimiter$ arg for Split() function ' Dim v1, vTmp(), lMaxCols&, lMaxRows&, n&, K& If (VarType(Arr) < vbArray) Or (Delimiter = "") Then Exit Sub lMaxRows = UBound(Arr) + 1: vTmp = Arr: Erase Arr 'Get size of Dim2 For n = LBound(vTmp) To UBound(vTmp) K = UBound(Split(vTmp(n), Delimiter)) lMaxCols = IIf(K + 1 lMaxCols, K + 1, lMaxCols) Next 'n ReDim Arr(1 To lMaxRows, 1 To lMaxCols) For n = LBound(vTmp) To UBound(vTmp) v1 = Split(vTmp(n), Delimiter) For K = LBound(v1) To UBound(v1) Arr(n + 1, K + 1) = v1(K) Next 'k Next 'n End Sub 'Xform_1DimArrayTo2D -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delimiter(?)
Your non-VBA solution worked. Thanks!
I'm not familiar/comfortable with VBA or macros |
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delimiter(?)
Copy/paste error...
'Edit file contents sTextIn = Replace(sTextIn, " <<", "|") sTextIn = Replace(sTextIn, "", "") sTextIn = Replace(sTextIn, "<", "") -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
#5
Posted to microsoft.public.excel.programming
|
|||
|
|||
Delimiter(?)
Your non-VBA solution worked. Thanks!
I'm not familiar/comfortable with VBA or macros You're welcome! I appreciate the feedback... -- Garry Free usenet access at http://www.eternal-september.org Classic VB Users Regroup! comp.lang.basic.visual.misc microsoft.public.vb.general.discussion |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Using TAB as CSV delimiter | Excel Programming | |||
point as delimiter | Excel Discussion (Misc queries) | |||
delimiter | Excel Programming | |||
Text to Row by Delimiter | Excel Programming |