open file with multiple types of delimiters

T

tuli

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
 
I

isabelle

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 :
 
G

GS

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top