Parse rows to files, 1 row per file...

S

spacely

Board Regular
Joined
Oct 26, 2007
Messages
248
Hi there,

I need to pull off rows of numbers, one row at a time, into one unique file name at a time. Each file would have the same couple-line header. All this is in excel starting in column A. Each value is in a cell. They're all real values. Each uniqueName file would be stored in the current folder. Column B would be ignorSo (sorry for the messy format paste):

uniqueName1 ignore -834.6 112.9 156.7 2280.7 376.0 1825.6
uniqueName2 ignore 903.9 -13.1 94.9 -280.9 -358.0 1416.1
uniqueName3 ignore 99.3 -823.8 307.6 618.4 2504.2 2468.6
uniqueName4 ignore 17.8 881.1 96.2 1856.6 -2654.3 -22.7
uniqueName5 ignore 411.0 476.0 -284.2 843.5 502.2 -965.9
uniqueName6 ignore 191.3 707.8 597.2 -446.0 -667.2 2316.2
uniqueName7 ignore -462.0 306.5 57.0 -4823.3 -2045.2 15.4
uniqueName8 ignore -169.3 -659.8 241.5 4385.2 -1286.1 2360.4
uniqueName9 ignore -478.9 -390.4 56.5 856.6 -7230.6 1764.7
uniqueName10 ignore 203.5 455.2 134.9 -2254.1 5008.6 1908.2
uniqueName11 ignore 822.8 41.5 80.2 -155.4 -66.8 -2415.9
uniqueName12 ignore 673.8 -274.8 399.5 -36.2 -827.9 3535.6
uniqueName13 ignore -14.4 -320.0 34.5 162.5 853.0 -372.3
uniqueName14 ignore 903.9 -13.1 94.9 -280.9 -358.0 1416.1
uniqueName15 ignore 595.9 -207.1 32.6 -0.9 0.4 -20.8
uniqueName16 ignore -478.9 -390.4 56.5 856.6 -7230.6 1764.7


So uniqueName1.txt would look like:

General Title Line

time 251 252 253 254 255 256
T FxR0 FyR0 FzR0 MxR0 MyR0 MzR0
[sec] [kN] [kN] [kN] [kNm] [kNm] [kNm]

1 -834.6 112.9 156.7 2280.7 376.0 1825.6

So there's a 1 prefixing the line where that is the file number. I think the best way to for-next it is to highlight the filenames in column A and for the selection just use a loop counter to store each line to each file and the loop counter would be that number prefixing each line.

Let me know if any info is missing, and thanks a lot!
 
Last edited:

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.
Sort by date Sort by votes
I created a workbook with two sheets labeled data and headers
please note that i changed decimal point to a comma because I use the Dutch excel.

data

*ABCDEFGH
uniqueName1*
uniqueName2*
uniqueName3*
uniqueName4*
uniqueName5*
uniqueName6*
uniqueName7*
uniqueName8*
uniqueName9*
uniqueName10*
uniqueName11*
uniqueName12*
uniqueName13*
uniqueName14*
uniqueName15*
uniqueName16*

<colgroup><col style="width:30px; "><col style="width:108.67px;"><col style="width:22.67px;"><col style="width:69.33px;"><col style="width:69.33px;"><col style="width:69.33px;"><col style="width:69.33px;"><col style="width:69.33px;"><col style="width:69.33px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]

[TD="align: right"]-834,6[/TD]
[TD="align: right"]112,9[/TD]
[TD="align: right"]156,7[/TD]
[TD="align: right"]2280,7[/TD]
[TD="align: right"]376[/TD]
[TD="align: right"]1825,6[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]

[TD="align: right"]903,9[/TD]
[TD="align: right"]-13,1[/TD]
[TD="align: right"]94,9[/TD]
[TD="align: right"]-280,9[/TD]
[TD="align: right"]-358[/TD]
[TD="align: right"]1416,1[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]

[TD="align: right"]99,3[/TD]
[TD="align: right"]-823,8[/TD]
[TD="align: right"]307,6[/TD]
[TD="align: right"]618,4[/TD]
[TD="align: right"]2504,2[/TD]
[TD="align: right"]2468,6[/TD]

[TD="bgcolor: #cacaca, align: center"]4[/TD]

[TD="align: right"]17,8[/TD]
[TD="align: right"]881,1[/TD]
[TD="align: right"]96,2[/TD]
[TD="align: right"]1856,6[/TD]
[TD="align: right"]-2654,3[/TD]
[TD="align: right"]-22,7[/TD]

[TD="bgcolor: #cacaca, align: center"]5[/TD]

[TD="align: right"]411[/TD]
[TD="align: right"]476[/TD]
[TD="align: right"]-284,2[/TD]
[TD="align: right"]843,5[/TD]
[TD="align: right"]502,2[/TD]
[TD="align: right"]-965,9[/TD]

[TD="bgcolor: #cacaca, align: center"]6[/TD]

[TD="align: right"]191,3[/TD]
[TD="align: right"]707,8[/TD]
[TD="align: right"]597,2[/TD]
[TD="align: right"]-446[/TD]
[TD="align: right"]-667,2[/TD]
[TD="align: right"]2316,2[/TD]

[TD="bgcolor: #cacaca, align: center"]7[/TD]

[TD="align: right"]-462[/TD]
[TD="align: right"]306,5[/TD]
[TD="align: right"]57[/TD]
[TD="align: right"]-4823,3[/TD]
[TD="align: right"]-2045,2[/TD]
[TD="align: right"]15,4[/TD]

[TD="bgcolor: #cacaca, align: center"]8[/TD]

[TD="align: right"]-169,3[/TD]
[TD="align: right"]-659,8[/TD]
[TD="align: right"]241,5[/TD]
[TD="align: right"]4385,2[/TD]
[TD="align: right"]-1286,1[/TD]
[TD="align: right"]2360,4[/TD]

[TD="bgcolor: #cacaca, align: center"]9[/TD]

[TD="align: right"]-478,9[/TD]
[TD="align: right"]-390,4[/TD]
[TD="align: right"]56,5[/TD]
[TD="align: right"]856,6[/TD]
[TD="align: right"]-7230,6[/TD]
[TD="align: right"]1764,7[/TD]

[TD="bgcolor: #cacaca, align: center"]10[/TD]

[TD="align: right"]203,5[/TD]
[TD="align: right"]455,2[/TD]
[TD="align: right"]134,9[/TD]
[TD="align: right"]-2254,1[/TD]
[TD="align: right"]5008,6[/TD]
[TD="align: right"]1908,2[/TD]

[TD="bgcolor: #cacaca, align: center"]11[/TD]

[TD="align: right"]822,8[/TD]
[TD="align: right"]41,5[/TD]
[TD="align: right"]80,2[/TD]
[TD="align: right"]-155,4[/TD]
[TD="align: right"]-66,8[/TD]
[TD="align: right"]-2415,9[/TD]

[TD="bgcolor: #cacaca, align: center"]12[/TD]

[TD="align: right"]673,8[/TD]
[TD="align: right"]-274,8[/TD]
[TD="align: right"]399,5[/TD]
[TD="align: right"]-36,2[/TD]
[TD="align: right"]-827,9[/TD]
[TD="align: right"]3535,6[/TD]

[TD="bgcolor: #cacaca, align: center"]13[/TD]

[TD="align: right"]-14,4[/TD]
[TD="align: right"]-320[/TD]
[TD="align: right"]34,5[/TD]
[TD="align: right"]162,5[/TD]
[TD="align: right"]853[/TD]
[TD="align: right"]-372,3[/TD]

[TD="bgcolor: #cacaca, align: center"]14[/TD]

[TD="align: right"]903,9[/TD]
[TD="align: right"]-13,1[/TD]
[TD="align: right"]94,9[/TD]
[TD="align: right"]-280,9[/TD]
[TD="align: right"]-358[/TD]
[TD="align: right"]1416,1[/TD]

[TD="bgcolor: #cacaca, align: center"]15[/TD]

[TD="align: right"]595,9[/TD]
[TD="align: right"]-207,1[/TD]
[TD="align: right"]32,6[/TD]
[TD="align: right"]-0,9[/TD]
[TD="align: right"]0,4[/TD]
[TD="align: right"]-20,8[/TD]

[TD="bgcolor: #cacaca, align: center"]16[/TD]

[TD="align: right"]-478,9[/TD]
[TD="align: right"]-390,4[/TD]
[TD="align: right"]56,5[/TD]
[TD="align: right"]856,6[/TD]
[TD="align: right"]-7230,6[/TD]
[TD="align: right"]1764,7[/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


headers

*ABCDEFG

<colgroup><col style="width:30px; "><col style="width:69.33px;"><col style="width:69.33px;"><col style="width:69.33px;"><col style="width:69.33px;"><col style="width:69.33px;"><col style="width:69.33px;"><col style="width:69.33px;"></colgroup><tbody>
[TD="bgcolor: #cacaca, align: center"]1[/TD]
[TD="align: center"]time[/TD]
[TD="align: center"]251[/TD]
[TD="align: center"]252[/TD]
[TD="align: center"]253[/TD]
[TD="align: center"]254[/TD]
[TD="align: center"]255[/TD]
[TD="align: center"]256[/TD]

[TD="bgcolor: #cacaca, align: center"]2[/TD]
[TD="align: center"]T[/TD]
[TD="align: center"]FxR0[/TD]
[TD="align: center"]FyR0[/TD]
[TD="align: center"]FzR0[/TD]
[TD="align: center"]MxR0[/TD]
[TD="align: center"]MyR0[/TD]
[TD="align: center"]MzR0[/TD]

[TD="bgcolor: #cacaca, align: center"]3[/TD]
[TD="align: center"][sec][/TD]
[TD="align: center"][kN][/TD]
[TD="align: center"][kN][/TD]
[TD="align: center"][kN][/TD]
[TD="align: center"][kNm][/TD]
[TD="align: center"][kNm][/TD]
[TD="align: center"][kNm][/TD]

</tbody>


Excel tables to the web >> Excel Jeanie HTML 4


Q


the following code creates the txt files in the same folder as this workbook
Code: 
Option Explicit

Sub rowsToFiles()
   'mrExcel forum thread 1008789
   
   Dim fso        As Object
   Dim txtFile    As Scripting.file
   Dim txtStream  As TextStream
   
   Dim shtData    As Worksheeta
   Dim dataRow    As Long
   Dim dataAsText As String
   Dim fileName   As String
   Dim headers    As Range
   Dim hdrAr()    As Variant
   Dim hdrRow(1 To 3)  As String
   Dim i       As Long
   Dim nr      As Integer
   Dim arCol   As Integer
   
   Set fso = CreateObject("Scripting.FileSystemObject")
   
   Set shtData = ThisWorkbook.Worksheets("data")
   Set headers = ThisWorkbook.Worksheets("headers").Range("titles")
   hdrAr = headers
   
   For i = 1 To 3
      For arCol = 1 To UBound(hdrAr, 2)
         hdrRow(i) = hdrRow(i) & " " & hdrAr(i, arCol)
      Next arCol
      hdrRow(i) = Mid(hdrRow(i), 2)
   Next i
   
   For dataRow = 1 To shtData.Range("A1").End(xlDown).Row
      fileName = Cells(dataRow, 1).Value
      '----- extract nr at the end of the file name
      i = Len(fileName)
      While Mid(fileName, i, 1) >= "0" And Mid(fileName, i, 1) <= "9"
         i = i - 1
      Wend
      nr = Mid(fileName, i + 1)
      '-----
      'create file in folder of this workbook
      fileName = ThisWorkbook.Path & "\" & fileName & ".txt"
      
      Set txtStream = fso.CreateTextFile(fileName, True) 'ok to overwrite
      'write headers
      For i = 1 To 3
         txtStream.WriteLine hdrRow(i)
      Next i
      '----- write data prefixed by file nr
      dataAsText = shtData.Cells(dataRow, 3)
      For i = 4 To 8
         dataAsText = dataAsText & " " & shtData.Cells(dataRow, i)
      Next i
      txtStream.WriteLine nr & " " & dataAsText
      '-----
      txtStream.Close
   Next dataRow
End Sub
 
Upvote 0
It cannot find

Dim txtStream As TextStream
seems TextStream isn't showing up as a possible thing to dimension... ?
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,223,911
Messages
6,175,337
Members
452,637
Latest member
Ezio2866

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top