.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

 

[Andrew Poulsom]

Welcome to MrExcel.

Have you tried opening the file in Excel and choosing the appropriate delimiter at Step 2 of the Wizard? If you record a macro while doing it you should get some VBA code.

 

[naveenraja16]

Welcome to MrExcel.

Have you tried opening the file in Excel and choosing the appropriate delimiter at Step 2 of the Wizard? If you record a macro while doing it you should get some VBA code.

Thank you for the reply..

The data in that .dat file is available in a series form. Which means if i try to import data to excel via the macro (without delimiter) it comes and occupies in a single cell. If i use delimiter option with space and tab as the delimiters , only the first line data "// @(#)EBcolordef.dat 1.2 1/4/02 ConductorNT colordef file " appears.

As per my requirement , i dont need this line and i would like to split the data after this line into different columns as shown here [2]: http://i.stack.imgur.com/xauqQ.jpg .

 

[Peter_SSs]

Welcome to the MrExcel board!

Without knowing how the data is to be split, we have nothing to work with.
For example, towards the right hand end of the top line in your original data image, we can see an item "0.74110". In your sample result, that item appears to be split into "0.741" and "10" on the next row.
How would we know to split it that way and not, say as ..
0.74
110

or..

0.7411
0

or..

0.7
4110

..or even that that value should be split into 2 pieces at all?

Is there some invisible character between the two 1s in that item? "0.74110"

 

[naveenraja16]

Is there a possiblity for attaching a .txt file and .xls file along with this thread? if so pls let me know the way, so that i can explain it more clearly.

 

[Peter_SSs]

OK, I think I see how the split is done now. There will likely be a few more issues arise, but let's see if this is headed in the right direction.
Let's suppose we can get the data all into cell A1.

Provided the spaces between all those values are standard spaces, and they quite possibly are not, the code below produced the results in column B.
Does that look like it might be useful?
If so, give it a try on a sample of your real data.

Sub SplitIt()
  Dim bits, result
  Dim s As String
  Dim i As Long, k As Long
  
  s = Range("A1").Value
  bits = Split(Mid(s, InStr(1, s, "file") + 4) & String(Len(s) / 4, "|"))
  ReDim result(1 To (UBound(bits) + 1) / 3, 1 To 1)
  For i = 0 To UBound(bits) - 3 Step 3
    k = k + 1
    result(k, 1) = Join(Array(bits(i), bits(i + 1), bits(i + 2), Left(bits(i + 3), Len(bits(i + 3)) - Len(CStr(k + 1)))))
    bits(i + 3) = k
  Next i
  result(k, 1) = Replace(result(k, 1), "|", "")
  Range("B1").Resize(k).Value = result
End Sub

Excel Workbook
	A	B
1	// @(#)EBcolordef.dat 1.2 1/4/02 ConductorNT colordef file0 0 0 01 0 0.271 02 0 1 03 0 0 04 0 0 15 1 0.502 16 0 1 17 1 1 18 1 0.639 09 0.8 0.761 0.74110 0 0.8 011 0.902 0.902 0	0 0 0 0
2		1 0 0.271 0
3		2 0 1 0
4		3 0 0 0
5		4 0 0 1
6		5 1 0.502 1
7		6 0 1 1
8		7 1 1 1
9		8 1 0.639
10		9 0.8 0.761 0.741
11		10 0 0.8 0
12		11 0.902 0.902 0
13
Sheet2

 

[Peter_SSs]

Is there a possiblity for attaching a .txt file and .xls file along with this thread? if so pls let me know the way, so that i can explain it more clearly.

You cannot attach any files, but you can show small screen shots from Excel (as I have done above) that can be copied for testing. My signature block below has a link with more help about that.

 

[naveenraja16]

The problem is , If i copy and paste the data in excel manually with delimiter as TAB and SPACE, i can achieve what i actually require. The data comes exactly in the desired format. So there is problem only in importing data directly.

 

[naveenraja16]

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.

 

[Peter_SSs]

The problem is , If i copy and paste the data in excel manually with delimiter as TAB and SPACE, i can achieve what i actually require. The data comes exactly in the desired format. So there is problem only in importing data directly.

OK, but you also said ..

I have browsed and found a code which does the job of importing .dat data into excel.
.
.
.
I have reference code which does the action of browsing file and opening in excel but it doesnt work as per my expectation.

.. so it seemed that you could get the data into Excel but needed help rearranging it. Your thread title indicated that the data was in a single cell.

So, if you do have code that can get the data into Excel but it isn't in the right format, perhaps we could have some code that does change it to the correct format. Details would be required.

Somebody might be able to help get the data directly from the dat file, but it wouldn't be me.