Read the data from a text file and reshape the data

jdbaba

Board Regular
Joined
Sep 24, 2010
Messages
52
I have a data set for different time intervals. The data has three comment lines before data for each time interval. For each time interval there are 500 data points. I want to change the dataset such that I have the following format:

<code style="margin: 0px; padding: 0px; border: 0px; font-size: 13.63636302947998px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">t1 t2 t3 ................
0.00208 0.00417 0.00625 ..............
a1 a2 a3 ...................
b1 b2 b3 ...................
c1 c2 c3 ...................
</code>
The link to the file is as follows: https://www.dropbox.com/s/hc8n3qcai1mlxca/WAT_DEP.DAT

As you will see on the file, time for each interval is the second data of the third line before the data starts. For the first time, t= 0.00208. I need to change the data in several rows into one column. At last I need to create a dataframe with the format shown above. In the sample above, a1, b1, c1 are the data for time t1, and so on.

Currently, I have data of more than 10 columns for each time. I want to change such that each column has 500 data points. So, I want to arrange the data columns such that first the data on row 1 will be used and then data on second row and so on. This way, we will have one column for one time. Sample data may be confusing here. Would you please look at the link I posted above ?

I hope I made it clear.

The sample data is as follows:


<code style="margin: 0px; padding: 0px; border: 0px; font-size: 13.63636302947998px; vertical-align: baseline; font-family: Consolas, Menlo, Monaco, 'Lucida Console', 'Liberation Mono', 'DejaVu Sans Mono', 'Bitstream Vera Sans Mono', 'Courier New', monospace, serif;">** N:SNAPSHOT TIME DELT**
WATER DEPTH [M]: (HP(L),L=2,LA)
1800 0.00208 0.10000
3.224 3.221 3.220 3.217 3.216 3.214 3.212 3.210 3.209 3.207 3.205 3.203 3.202 3.200 3.199 3.197 3.196 3.193 3.192 3.190 3.189 3.187 3.186 3.184 3.184 3.182 3.181 3.179 3.178 3.176 3.175 3.174 3.173 3.171 3.170 3.169 3.168 3.167 3.166 3.164 3.164 3.162 3.162 3.160 3.160 3.158 3.158 3.156 3.156 3.155 3.154 3.153 3.152 3.151 3.150 3.150 3.149 3.149 3.147 3.147 3.146 3.146 3.145 3.145 3.144 3.144 3.143 3.143 3.142 3.142 3.141 3.142 3.141 3.141 3.140 3.141 3.140 3.140 3.139 3.140 3.139 3.140 3.139 3.140 3.139 3.140 3.139 3.140 3.139 3.140 3.139 3.140 3.140 3.140 3.140 3.141 3.141 3.142 3.141 3.142 3.142 3.142 3.143 3.143 3.144 3.144 3.145 3.145 3.146 3.146 3.147 3.148 3.149 3.149 3.150 3.150 3.152 3.152 3.153 3.154 3.155 3.156 3.157 3.158 3.159 3.160 3.161 3.162 3.163 3.164 3.165 3.166 3.168 3.169 3.170 3.171 3.173 3.174 3.176 3.176 3.178 3.179 3.181 3.182 3.184 3.185 3.187 3.188 3.190 3.191 3.194 3.195 3.196 3.198 3.199 3.202 3.203 3.205 3.207 3.209 3.210 3.213 3.214 3.217 3.218 3.221 3.222 3.225 3.226 3.229 3.231 3.233 3.235 3.238 3.239 3.242 3.244 3.247 3.248 3.251 3.253 3.256 3.258 3.261 3.263 3.266 3.268 3.271 3.273 3.276 3.278 3.281 3.283 3.286 3.289 3.292 3.294 3.297 3.299 3.303 3.305 3.307 3.311 3.313 3.317 3.319 3.322 3.325 3.328 3.331 3.334 3.337 3.340 3.343 3.347 3.349 3.353 3.356 3.359 3.362 3.366 3.369 3.372 3.375 3.379 3.382 3.386 3.388 3.392 3.395 3.399 3.402 3.406 3.409 3.413 3.416 3.420 3.423 3.427 3.430 3.435 3.438 3.442 3.445 3.449 3.453 3.457 3.460 3.464 3.468 3.472 3.475 3.479 3.483 3.486 3.491 3.494 3.498 3.502 3.506 3.510 3.514 3.518 3.522 3.526 3.531 3.534 3.539 3.542 3.547 3.551 3.555 3.559 3.564 3.567 3.572 3.576 3.581 3.584 3.589 3.593 3.598 3.602 3.606 3.610 3.615 3.619 3.624 3.628 3.633 3.637 3.642 3.646 3.651 3.655 3.660 3.664 3.669 3.673 3.678 3.682 3.686 3.691 3.695 3.700 3.704 3.710 3.714 3.719 3.723 3.728 3.733 3.738 3.742 3.747 3.752 3.757 3.761 3.766 3.771 3.776 3.780 3.786 3.790 3.795 3.800 3.805 3.810 3.815 3.819 3.825 3.829 3.835 3.839 3.845 3.849 3.855 3.859 3.865 3.869 3.875 3.879 3.885 3.889 3.895 3.900 3.905 3.910 3.915 3.920 3.926 3.930 3.935 3.941 3.945 3.951 3.956 3.961 3.966 3.972 3.976 3.982 3.987 3.993 3.997 4.003 4.008 4.014 4.018 4.024 4.029 4.035 4.039 4.045 4.050 4.056 4.061 4.066 4.071 4.077 4.082 4.088 4.093 4.099 4.103 4.109 4.114 4.120 4.125 4.131 4.136 4.142 4.147 4.153 4.157 4.163 4.168 4.174 4.179 4.185 4.190 4.195 4.201 4.206 4.212 4.217 4.223 4.228 4.234 4.239 4.245 4.250 4.256 4.261 4.267 4.272 4.278 4.283 4.289 4.294 4.300 4.305 4.311 4.316 4.322 4.327 4.333 4.339 4.345 4.350 4.356 4.361 4.367 4.372 4.378 4.383 4.389 4.394 4.400 4.405 4.411 4.417 4.423 4.428 4.434 4.439 4.445 4.450 4.456 4.461 4.467 4.473 4.478 4.484 4.489 4.495 4.500 4.506 4.511 4.517 4.523 4.529 4.534 4.540 4.545 4.551 4.556 4.562 4.568 4.574 4.579 4.585 4.590 4.596 4.601 4.607 4.613 4.619 4.624 4.630 4.635 4.641 4.646 4.652 4.658 4.664 4.669 4.675 4.680 4.686 4.691 4.697 4.703 4.709 4.714 4.720 4.725 4.731 4.736 4.741
** N:SNAPSHOT TIME DELT**
WATER DEPTH [M]: (HP(L),L=2,LA)
3600 0.00417 0.10000
4.124 4.123 4.123 4.122 4.122 4.121 4.121 4.120 4.120 4.119 4.118 4.117 4.117 4.116 4.116 4.115 4.115 4.114 4.114 4.114 4.114 4.113 4.113 4.112 4.112 4.111 4.111 4.110 4.110 4.109 4.109 4.109 4.109 4.108 4.108 4.107 4.107 4.106 4.107 4.106 4.106 4.105 4.105 4.105 4.105 4.104 4.104 4.104 4.104 4.103 4.103 4.103 4.102 4.102 4.102 4.102 4.101 4.102 4.101 4.101 4.101 4.101 4.100 4.101 4.100 4.101 4.100 4.100 4.100 4.100 4.100 4.100 4.100 4.100 4.100 4.100 4.100 4.100 4.100 4.100 4.100 4.100 4.100 4.100 4.100 4.100 4.100 4.100 4.100 4.101 4.100 4.101 4.100 4.101 4.101 4.101 4.101 4.102 4.101 4.102 4.102 4.101 4.102 4.102 4.103 4.102 4.103 4.103 4.104 4.103 4.104 4.104 4.105 4.104 4.105 4.105 4.106 4.106 4.107 4.106 4.107 4.107 4.108 4.108 4.109 4.109 4.110 4.110 4.110 4.110 4.111 4.111 4.112 4.112 4.113 4.113 4.114 4.114 4.115 4.115 4.116 4.116 4.117 4.117 4.118 4.118 4.120 4.120 4.121 4.121 4.122 4.122 4.122 4.123 4.123 4.125 4.125 4.126 4.126 4.127 4.128 4.129 4.129 4.130 4.130 4.132 4.132 4.133 4.133 4.135 4.135 4.136 4.137 4.138 4.138 4.139 4.140 4.141 4.141 4.143 4.143 4.145 4.145 4.146 4.147 4.148 4.149 4.150 4.150 4.152 4.152 4.154 4.154 4.156 4.156 4.158 4.158 4.160 4.160 4.162 4.162 4.163 4.164 4.165 4.166 4.167 4.168 4.169 4.171 4.171 4.173 4.173 4.175 4.176 4.177 4.178 4.180 4.180 4.182 4.183 4.184 4.185 4.187 4.187 4.189 4.190 4.192 4.192 4.194 4.195 4.197 4.197 4.199 4.200 4.202 4.203 4.204 4.205 4.207 4.208 4.210 4.210 4.212 4.213 4.215 4.216 4.218 4.219 4.221 4.221 4.223 4.224 4.225 4.227 4.228 4.230 4.231 4.233 4.234 4.236 4.237 4.239 4.240 4.242 4.243 4.245 4.246 4.248 4.249 4.251 4.252 4.254 4.255 4.257 4.258 4.260 4.262 4.264 4.265 4.267 4.268 4.270 4.271 4.273 4.275 4.277 4.278 4.280 4.281 4.283 4.285 4.287 4.288 4.290 4.291 4.294 4.295 4.297 4.298 4.301 4.302 4.303 4.305 4.307 4.309 4.310 4.312 4.314 4.316 4.317 4.320 4.321 4.323 4.325 4.327 4.328 4.331 4.332 4.334 4.336 4.338 4.339 4.342 4.343 4.346 4.347 4.349 4.351 4.353 4.355 4.357 4.359 4.361 4.362 4.365 4.366 4.369 4.370 4.373 4.374 4.377 4.378 4.381 4.382 4.385 4.386 4.389 4.390 4.393 4.394 4.397 4.398 4.400 4.402 4.404 4.406 4.408 4.411 4.412 4.415 4.416 4.419 4.421 4.423 4.425 4.427 4.429 4.432 4.433 4.436 4.437 4.440 4.442 4.444 4.446 4.449 4.450 4.453 4.455 4.457 4.459 4.462 4.463 4.466 4.468 4.470 4.472 4.475 4.476 4.479 4.481 4.484 4.485 4.488 4.490 4.492 4.494 4.497 4.499 4.501 4.503 4.505 4.508 4.509 4.512 4.514 4.517 4.519 4.521 4.523 4.526 4.528 4.530 4.532 4.535 4.537 4.540 4.541 4.544 4.546 4.549 4.551 4.554 4.555 4.558 4.560 4.563 4.565 4.568 4.569 4.572 4.574 4.577 4.579 4.582 4.584 4.586 4.588 4.591 4.593 4.596 4.598 4.601 4.603 4.605 4.607 4.610 4.612 4.615 4.617 4.620 4.622 4.624 4.627 4.628 4.631 4.633 4.636 4.638 4.641 4.643 4.646 4.648 4.651 4.653 4.656 4.657 4.660 4.662 4.665 4.667 4.670 4.672 4.675 4.677 4.680 4.682 4.685 4.687 4.690 4.692 4.695 4.697 4.700 4.702 4.705 4.706 4.709 4.711 4.714 4.716 4.719 4.721 4.724 4.726 4.729 4.731 4.734 4.736 4.741
</code>
 
Last edited:

Some videos you may like

Excel Facts

Which came first: VisiCalc or Lotus 1-2-3?
Dan Bricklin and Bob Frankston debuted VisiCalc in 1979 as a Visible Calculator. Lotus 1-2-3 debuted in the early 1980's, from Mitch Kapor.

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,601
Office Version
365
Platform
Windows
So you would have a column for each time interval with 500 rows of data?

Something like this?
0.002080.00417
3.2244.124
3.2214.123
3.2204.123
 

jdbaba

Board Regular
Joined
Sep 24, 2010
Messages
52
So you would have a column for each time interval with 500 rows of data?

Something like this?
0.002080.00417
3.2244.124
3.2214.123
3.2204.123

<tbody>
</tbody>






Yes, that table is exactly what I am looking for. Yes, I will have a column with 500 rows of data.

Thank you.
 

Norie

Well-known Member
Joined
Apr 28, 2004
Messages
75,601
Office Version
365
Platform
Windows
What you can do is open the file in Excel by going to Data>From Text.

When the text import wizard comes up select Delimited and space as the delimiter.

Then run this code.
Code:
Option Explicit

Sub ReShape()
Dim arrDat
Dim rng As Range
Dim rngD As Range
Dim I As Long
Dim J As Long
Dim K As Long

    arrDat = Sheet1.UsedRange
    Set rng = Sheet2.Range("A1")

    For I = 1 To 50827 Step 53

        rng.Value = arrDat(I + 2, 3)
        Set rngD = rng.Offset(1)

        Set rng = rng.Offset(, 1)

        For J = I + 3 To I + 52
            For K = 2 To 11

                rngD.Value = arrDat(J, K)

                Set rngD = rngD.Offset(1)
            Next K

        Next J
    Next I
End Sub
 

jdbaba

Board Regular
Joined
Sep 24, 2010
Messages
52
Norie,

Thank you so much for this elegant piece of code. This really does help and is exactly what I am after.

Thanks.
 

Watch MrExcel Video

Forum statistics

Threads
1,101,810
Messages
5,483,040
Members
407,375
Latest member
achusp

This Week's Hot Topics

  • Finding issue in If elseif else with For each Loop
    Finding issue in If elseif else with For each Loop I have tried this below code but i'm getting in Y column filled with W005. Colud you please...
  • MsgBox Error
    Hi Guys, I have the below error show up when i try and run my macro in File1 but works fine if i copy and paste the same code into file2. [ATTACH...
  • CELL FORMAT - IF CONDITION
    My Cell Format is [B]""0.00" Cr". [/B]But in the cell, it is showing 123.00 for editing. (123 is entry figure). (Data imported from other...
  • Show numbers nearly the same
    Is this possible. I have a number that can change very time eg 0.00001234 Then I have a lot of numbers 0.0000001, 0.0000002, 0.00000004...
  • Please i need your help to create formula
    I need a formula in cell B8 to do this >>if b1=1 then multiply ( cell b8) by 10% ,if b1=2 multiply by 20%,if=3 multiply by 30%. Thank you in...
  • Got error while adding column and filter
    Got error while adding column and filter In column Z has some like "Success" and "Error". I want to add column in AA if the Z cell value is...
Top