Importing Text files

[airone]

Dear All,
I am a land surveyor and I need to import coordinates text files for further CAD program. My problem is the non-lenghted colums for the coordinate; Typically it looks like(Name X Y Z):
B302 541000 12566 425
I don't manage to detect the space or the Tab between each number while importing the file with an Open Coordinates.Text For Input As #1 instructions
Is there a special coding for the Tab and the Space character?
How can I store easily these informations in an array?
Thanks in advance
Cheers

 

[bryanbayfield]

May just be easier to open the text file in your application (which I assume is part of the Office Suit) and then manipulate from there. You will have far greater control over how your file is imprted if you go through the Excel interface.

 

[SkipVought]

airone,

If you still have a problem after trying Bryan's suggestion, send me a sample coordinates file. I'll take a look.

Skip,
Skip@TheOfficeExperts.com

http://www.TheOfficeExperts.com

 

[Cresby]

Have I mis-understood but surely if you record an operation like an import does it not define most parameters?

I have found it can assign false values (always look at the code to understand &/or correct) to the parameters but usually it lists all possible options.


I just did this in Excel 2000


Sub Macro1()
'
' Macro1 Macro
' Macro recorded 19/06/2003 by template

ChDir "A:\"
Workbooks.OpenText Filename:="A:\findfiles.txt", Origin:=xlWindows, _
StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=True, Tab:=True, Semicolon:=False, Comma:=False, _
Space:=True, Other:=False, FieldInfo:=Array(Array(1, 2), Array(2, 2), Array(3 _
, 2), Array(4, 2), Array(5, 2), Array(6, 2), Array(7, 2), Array(8, 2), Array(9, 2), Array(10, _
2), Array(11, 2), Array(12, 2), Array(13, 2), Array(14, 2), Array(15, 2), Array(16, 2), _
Array(17, 2))
End Sub

I think Tab:=True, Semicolon:=False, Comma:=False is a cluette. best of luck - BTW I chose all fields as TEXT
Cresby

 

[airone]

Well, thanks for the help: here is precisely the problem. Regarding the code below, I would like to substitute the variable "Sep" by the ascii code for "Tab" and eventually by space character. The program will then detect the space betewenn two string. After all, I will convert the code (starting line 29) for VBA applied to autocad. That is the reason why I don't want to go through excel even if it is working fine.


1 Public Sub ImportTextFile(FName As String, Sep As String)
2
3 Dim RowNdx As Integer
4 Dim ColNdx As Integer
5 Dim TempVal As Variant
6 Dim WholeLine As String
7 Dim Pos As Integer
8 Dim NextPos As Integer
9 Dim SaveColNdx As Integer
10
11 Application.ScreenUpdating = False
12 'On Error GoTo EndMacro:
13
14 SaveColNdx = ActiveCell.Column
15 RowNdx = ActiveCell.row
16
17 Open FName For Input Access Read As #1
18
19 While Not EOF(1)
20 Line Input #1, WholeLine
21 If Right(WholeLine, 1) <> Sep Then
22 WholeLine = WholeLine & Sep
23 End If
24 ColNdx = SaveColNdx
25 Pos = 1
26 NextPos = InStr(Pos, WholeLine, Sep)
27 While NextPos >= 1
28 TempVal = Mid(WholeLine, Pos, NextPos - Pos)
29 Cells(RowNdx, ColNdx).Value = TempVal
30 Pos = NextPos + 1
31 ColNdx = ColNdx + 1
32 NextPos = InStr(Pos, WholeLine, Sep)
33 Wend
34 RowNdx = RowNdx + 1
35 Wend
36
37 EndMacro:
38 On Error GoTo 0
39 Application.ScreenUpdating = True
40 Close #1
41
42 End Sub

See you all
Airone

 

[bryanbayfield]

The ASCII code for a tab character is 9 if that helps. So going by what you say (and I'm struggling to follow your code) you could replace sep with chr(9).

 

[bryanbayfield]

Whooah, let's get this right - Chr$(9) so that you return a string rather than a variant.

By the way -

While ... Wend

- that's a bit old! Do ... Loop While or Do ... Loop Until is usually what I use.

 

[ssVBAdev]

Question 1) But you mention your CAD program. Do the coordinates need to go into the Excel File first or could you just skip that and bring them right into AutoCAD - where I assume you are going with them anyway?

Question 2) You said &quot;I don't manage to detect the space or the Tab between each number while importing...&quot; Do you know for sure if the charcter between each data item is a &quot;Tab&quot; or a &quot;Space&quot;? And, if you do not know, would the characters be the same throughout the line (ie would they all be spaces or all tabs or are they mixed in).

You may be able to try:
Line Input #1, Name, X, Y, Z
and import all the values from each line in one step rather than importing the entire line as one string and trying to parse that string yourself.