title: Data mapping to Excel files
body: <p>I have 635 text files in one folder. I want to read this data into excel. This is not as straightforward  a task as it sounds at first. I've been working on exporting the files to excel(with the help of <strong>stackoverflow</strong> :)) and now that I have got that working, I want to finish my application. Here are the parameters:</p>
  
  <ol>
  <li><p>My output excel spreadsheet will have 637 columns and data would be entered from the third column onward. As you can guess, each column(3-639) will represent one of the 635 sub-folders.</p></li>
  <li><p>There are 73902 rows in the spreadsheet and the data will be written from the 3rd row onward. </p></li>
  <li><p>Now, the data to be written comes from the 635 files. Every file has 2 columns. For example to fill out column 5(which will correspond to <strong>5-2 = 3rd file out of 635</strong>) in the excel sheet, we go to the <strong>3rd</strong> file and take the values from there.The first column in that file determines what cells to be filled out in the excel sheet. The values to be filled out are taken from the second column of this file(Sorry if the word column is getting confusing). We need to fill out 73900 rows for every column on the sheet(every column is 1 file). And then repeat this for 635 columns. </p></li>
  </ol>
  
  <p>If the 3rd file looks like this:</p>
  
  <pre><code>5849 66883
  395 4492863
  681 1835871
  817 4039961
  835 3246671
  868 4041156
  889 1891481
  1305 4467688
  1317 175306
  1361 3252611
  2101 174589
  4364 4053046
  4897 4466547
  4991 3879532
  5327 3992891
  5397 175328
  6067 3881675
  6075 176782
  6906 2358727
  7497 1838021
  </code></pre>
  
  <p>then we will fill out rows <code>5849, 385, 681,817</code> and so on...We fill these cells with the corresponding values from the second column in the file. So the 5th column in the excel sheet will have the cells <code>5849, 385, 681,817...</code> filled with values <code>66883,4492863,1835871,4039961...</code></p>
  
  <p>I am attaching an image of the excel sheet to make the situation clearer <img src="https://i.stack.imgur.com/sFKQO.png" alt="enter image description here"> </p>
  
  <p>So far I have the Visual Basic code to import text files into Excel but don't really process any of the info discussed above. I also have a small MATLAB script to do the same(this is not completely functioning) to write data to excel. I am pasting both below.</p>
  
  <pre><code>Sub ReadFilesIntoActiveSheet()
  Dim fso As FileSystemObject
  Dim folder As folder
  Dim file As file
  Dim FileText As TextStream
  Dim TextLine As String
  Dim Items() As String
  Dim i As Long
  Dim cl As Range
  
  ' Get a FileSystem object
  Set fso = New FileSystemObject
  
   ' get the directory you want
    Set folder = fso.GetFolder("D:\275_25bp")
  
  ' set the starting point to write the data to
   Set cl = ActiveSheet.Cells(1, 1)
  
  ' Loop thru all files in the folder
  For Each file In folder.Files
  ' Open the file
  Set FileText = file.OpenAsTextStream(ForReading)
  
  ' Read the file one line at a time
  Do While Not FileText.AtEndOfStream
      TextLine = FileText.ReadLine
  
      ' Parse the line into | delimited pieces
      Items = Split(TextLine, " ")
  
      ' Put data on one row in active sheet
      For i = 0 To UBound(Items)
          cl.Offset(0, i).Value = Items(i)
      Next
  
      ' Move to next row
      Set cl = cl.Offset(1, 0)
  Loop
  
  ' Clean up
  FileText.Close
  Next file
  
  Set FileText = Nothing
  Set file = Nothing
  Set folder = Nothing
  Set fso = Nothing
  
  End Sub
  </code></pre>
  
  <p><strong>####################### MATLAB SCRIPT################################</strong></p>
  
  <pre><code>dirname = uigetdir;#
  Files = dir(fullfile(dirname,'*.txt'))
  for i=1:numel(Files)
  filename = fullfile(dirname,Files(k).name);
  [col1,col2] = textread( filename, '%d%d' )
  %pos1 = strcat('A',num2str(i));
  %pos2 = strcat('B',num2str(i));
  xlswrite('sample_output',col1,'Sheet1','A1:CI1')
  xlswrite('sample_output',col2,'Sheet1','A2:CI2')
  end
  </code></pre>
  
  <p>There's no general naming pattern for the file-NAMES except that they are unique names. The folder contains them alphabetically, so file 1(starting with A) and file 635(will start with Z).Sample file names:</p>
  
  <p><code>Acidothermus_cellulolyticus_11B-list.txt</code></p>
  
  <p><code>Frankia_alni_ACN14a-list.txt</code>
  <code>...</code>
  <code>Zymomonas_mobilis_ZM4-list.txt</code></p>
  
  <p>It doesn't matter what language is used, but preferably something in UNIX(I know this is not a language :P ) or MATLAB(as I have been doing this project in these 2 languages). </p>
  
  <p>I'd greatly appreciate help with this. Please let me if you need any clarification on what needs to be done. Thank you!</p>