Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 7
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 587
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
external usenet poster
 
Posts: 3,514
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
How can I hide unused file types from file types list in save dial Estra Q Excel Discussion (Misc queries) 1 December 17th 09 01:36 PM
Multiple Delimiters Doug Mc New Users to Excel 9 October 18th 09 03:36 PM
Multiple Delimiters hassanq23 Excel Discussion (Misc queries) 5 April 7th 09 01:16 AM
using multiple delimiters at the same time [email protected] Excel Discussion (Misc queries) 2 August 2nd 06 01:45 AM
delimiting w/multiple delimiters? NTaylor Excel Discussion (Misc queries) 0 January 11th 06 05:22 PM


All times are GMT +1. The time now is 07:09 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"