Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
open file with multiple types of delimiters
I have a file with the following format (n rows of):
Freq, Real+jImag which sometimes looks like Freq, Real-jImag I can tell excel to consider the coma as delimiter and only ONE option for an arbitrary delimiter, say "+j". I would need another option for an arbitrary delimiter for "-j". Am I missing something, or this is a real limitation? Thanks Tuli |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
open file with multiple types of delimiters
hi Tuli,
this is a real limitation excerpt from EXCEL 2002 Help expression.OpenText(FileName, Origin, StartRow, DataType, TextQualifier, ConsecutiveDelimiter, Tab, Semicolon, Comma, Space, Other, OtherChar, FieldInfo, TextVisualLayout, DecimalSeparator, ThousandsSeparator, TrailingMinusNumbers, Local) Other is True. If multiple characters are specified, only the first character in the string is used, the other characters are ignored. Another possibility is to do it in 2 steps -- isabelle Le 2012-01-10 08:37, tuli a écrit : I have a file with the following format (n rows of): Freq, Real+jImag which sometimes looks like Freq, Real-jImag I can tell excel to consider the coma as delimiter and only ONE option for an arbitrary delimiter, say "+j". I would need another option for an arbitrary delimiter for "-j". Am I missing something, or this is a real limitation? Thanks Tuli |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
open file with multiple types of delimiters
tuli brought next idea :
I have a file with the following format (n rows of): Freq, Real+jImag which sometimes looks like Freq, Real-jImag I can tell excel to consider the coma as delimiter and only ONE option for an arbitrary delimiter, say "+j". I would need another option for an arbitrary delimiter for "-j". Am I missing something, or this is a real limitation? Thanks Tuli Actually, the first delimiter is a comma and a space; the second delimiter may be '+j' or '-j'. I recommend using standard VBA file I/O to read the file into an array and parse the array as if the lines contained value pairs. This means you could check each line for the '+j' or the '-j' delimiter and take appropriate action to split the pair. For example: Sub ParseFileText() Dim i As Long Dim vTextIn As Variant Const sFilename As String = "C:\MyFile" '//use actual file & path vTextIn = Split(GetTextFromFile(sFilename), vbCrLf) 'Replace the 2nd delimiter with ", " For i = LBound(vTextIn) To UBound(vTextIn) If InStr(vTextIn(i), "+j") 0 Then vTextIn(i) = Replace(vTextIn(i), "+j", ", ") ElseIf InStr(vTextIn(i), "-j") 0 Then vTextIn(i) = Replace(vTextIn(i), "-j", ", ") End If Next 'i 'Parse the lines into separate cells For i = LBound(vTextIn) To UBound(vTextIn) Cells(i + 1, 1).Resize(1, 3) = Split(vTextIn(i), ", ") Next 'i End Sub Helper function... Function GetTextFromFile(sFileName As String) As String ' Opens and reads the contents of a text file Dim iNum As Integer, bOpen As Boolean On Error GoTo ErrHandler iNum = FreeFile() 'Get the next file number 'Read the entire file Open sFileName For Binary Access Read As #iNum bOpen = True '//if we got here then file opened successfully GetTextFromFile = Space$(LOF(iNum)): Get iNum, , GetTextFromFile ErrHandler: If bOpen Then Close #iNum End Function '//GetTextFromFile() HTH -- Garry Free usenet access at http://www.eternal-september.org ClassicVB Users Regroup! comp.lang.basic.visual.misc |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I hide unused file types from file types list in save dial | Excel Discussion (Misc queries) | |||
Multiple Delimiters | New Users to Excel | |||
Multiple Delimiters | Excel Discussion (Misc queries) | |||
using multiple delimiters at the same time | Excel Discussion (Misc queries) | |||
delimiting w/multiple delimiters? | Excel Discussion (Misc queries) |