.dat to excel with splitting data into different rows from a single cell

[naveenraja16]

I have data in .dat file format. It is basically a color database. I need to import the contents in the notepad into excel. I have browsed and found a code which does the job of importing .dat data into excel.
The screen shot of the data available in the .dat file when opened in notepad looks as shown in the image attached.

For reference, i am copy - pasting the data herewith below.


// @(#)EBcolordef.dat 1.2 1/4/02 ConductorNT colordef file
0 0 0 0
1 1 0.271 0
2 0 1 0
3 1 1 0
4 0 0 1
5 1 0.502 1
6 0 1 1
7 1 1 1
8 1 0.639 0
9 0.8 0.761 0.741
10 0 0.8 0
11 0.902 0.902 0
12 0.8 0 0
13 0 0.741 1
14 0.62 0.322 0.169
15 0.541 0.522 0.51
16 0.682 0.659 0.514
17 0.298 0.259 0.239
18 0.604 0.604 0.604
19 0.702 0.702 0.702
20 0.753 0.753 0.753
21 0.8 0.761 0.741
22 0.855 0.855 0.855
23 0.937 0.937 0.902
24 0.4 0.4 0.902
25 0.604 0.604 0.902
26 0.749 0.749 0.902
27 0.855 0.855 0.902
28 0 0 0
29 0.604 0.604 1
30 0 0 0
31 0.886 0.953 1
32 0 0 0
33 0.867 0.733 0.933
34 1 0.467 0.467
35 0.467 1 0
36 0.6 0.6 1
37 0.6 1 1
38 1 0.6 1
39 1 1 0
40 0 0 0
41 1 1 1
42 1 0 0
43 0 1 0
44 0 0 1
45 0 1 1
46 1 0 1
47 1 1 0
48 0.212 0.012 0.012
49 0.933 0.8 1
50 0.733 0 0
51 0 0.733 0
52 0 0 0
53 0 0.733 0.733
54 0.733 0 0
55 0.733 0.733 0
56 0.267 0 0
57 0.6 0.467 0.667
58 0.4 0 0
59 0 0.4 0
60 0 0 0
61 0 0.4 0.4
62 0.4 0 0
63 0.4 0.4 0
64 1 0.733 0.467
65 0.667 1 0
66 0.533 1 0
67 0.533 0.8 1
68 0.733 0.6 1
69 1 0.6 0.722
70 0.533 0.533 0.4
71 0.8 0.8 0.651
72 1 0.533 0
73 0.667 1 0
74 0 1 0
75 0 0.533 1
76 0.533 0 1
77 1 0 0
78 0.4 0.4 0.251
79 0.667 0.667 0.533
80 0.733 0.4 0
81 0.4 0.733 0
82 0 0.733 0.333
83 0 0.4 0.722
84 0.333 0 0
85 0.667 0 0
86 0.467 0.467 0.333
87 0.733 0.733 0.6
88 0.4 0.2 0
89 0.2 0.4 0
90 0 0.4 0.2
91 0 0.2 0.4
92 0.2 0 0
93 0.4 0 0
94 0.333 0.333 0.2
95 0.6 0.6 0.451
96 1 0.776 0.776
97 1 0.71 0.71
98 0.976 0.651 0.651
99 0.949 0.584 0.584
100 0.898 0.529 0.529
101 0.851 0.49 0.49
102 0.82 0.459 0.459
103 0.788 0.427 0.427
104 0.765 0.4 0.4
105 0.745 0.337 0.337
106 0.706 0.271 0.271
107 0.667 0.208 0.208
108 0.651 0.153 0.153
109 0.635 0.09 0.09
110 0.6 0.059 0.059
111 0.573 0.031 0.031
112 1 0.741 0
113 1 0.718 0
114 1 0.69 0
115 1 0.655 0
116 1 0.627 0
117 1 0.6 0
118 1 0.557 0
119 1 0.502 0
120 1 0.471 0
121 0.961 0.443 0
122 0.933 0.42 0
123 0.902 0.396 0
124 0.851 0.384 0




If i copy and paste the same in excel directly, the entire data appears in different rows as shown below.[excel file screen shot]

I would like to have a excel vba to browse the same file and get the data from the .dat to excel , then remove the first row data (// @(#)EBcolordef.dat 1.2 1/4/02 ConductorNT colordef file) from that and delimit into different columns.


I have reference code which does the action of browsing file and opening in excel but it doesnt work as per my expectation.
`


Sub CNTcolorDBgenerate()
Dim flname
Dim filename
Dim FileNum As Integer
Dim Counter As Long, maxrow As Long
Dim WorkResult As String
Dim ws As Worksheet

On Error GoTo ErrorCheck
maxrow = Cells.Rows.Count
filename = Application.GetOpenFilename _
(FileFilter:="all file(*.*),*.*", MultiSelect:=True)
If VarType(filename) = vbBoolean Then
Exit Sub
End If

Application.ScreenUpdating = False
Application.EnableEvents = False

Counter = Cells(Cells.Rows.Count, "a").End(xlUp).Row
If Cells(Counter, "a") <> "" Then
Counter = Counter + 1
End If

For Each flname In filename
FileNum = FreeFile()
Open flname For Input As #FileNum
Do While Seek(FileNum) <= LOF(FileNum)
Application.StatusBar = "Importing Row " & _
Counter & " of text file " & flname
Line Input #FileNum, WorkResult
Set ws = Nothing
Set ws = ActiveSheet
ws.Select
Cells(Counter, 1) = WorkResult
If WorkResult <> "" Then
Application.DisplayAlerts = False
Cells(Counter, 1).TextToColumns _
Destination:=Cells(Counter, 1), _
DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, _
ConsecutiveDelimiter:=False, _
Tab:=False, Semicolon:=False, _
Comma:=True, Space:=False, _
Other:=False
End If
Counter = Counter + 1
If Counter > maxrow Then
MsgBox "data have over max rows: " & maxrow

Exit Sub
End If
Loop
Close
Next


Application.StatusBar = False
Application.EnableEvents = True
Application.ScreenUpdating = True

ErrorCheck:
Application.StatusBar = False
Application.EnableEvents = True
Application.ScreenUpdating = True`

End Sub


'


[1]: http://i.stack.imgur.com/KzKNt.jpg
[2]: http://i.stack.imgur.com/xauqQ.jpg

 

[Peter_SSs]

To make the splitting of data more clear... the data will be splitted 4 columns.. I hope based on info below , we can understand the splitting of data more clear.

1st column - 0 to 475
2nd, 3rd, 4th column----- the minimum value is 0 and the max value is 1 and the other values are between 0 and 1 with two decimal or three decimal values. Thats how i get the data when i copy and paste data with delimiters as TAB and SPACE.

Couldn't we just do Text-to-Columns (space delimiter) on the results I have in column B?