title: CSV with multiple datasets/different-number-of-columns
body: <p>Similar to <a href="https://stackoverflow.com/questions/18922493/how-can-you-read-a-csv-file-in-r-with-different-number-of-columns">How can you read a CSV file in R with different number of columns</a>, I have some complex CSV-files. Mine are from SAP BusinessObjects and hold challenges different to those of the quoted question. I want to automate the capture of an arbitrary number of datasets held in one CSV file. There are many CSV-files, but let's start with one of them.</p>
  
  <p>Given: One CSV file containing several flat tables.</p>
  
  <p>Wanted: Several dataframes or other structure holding all data (S4?)</p>
  
  <p>The method so far: </p>
  
  <ol>
  <li>get line numbers of header data by counting number of columns</li>
  <li>get headers by reading every line index held in vector described above</li>
  <li>read data by calculating skip and nrows between data sets in index described by header lines as above.</li>
  <li>give the read data column names from read header</li>
  </ol>
  
  <p>I need help getting me on the right track to avoid loops/making the code more readable/compact when reading headers and datasets. </p>
  
  <p>These CSVs are formatted as normal CSVs, only that they contain an more or less arbitrary amount of subtables. For each dataset I export, the structure is different. In the current example I will suppose there are five tables included in the CSV.</p>
  
  <p>In order to give you an idea, here is some fictous sample data with line numbers. Separator and quote has been stripped:</p>
  
  <pre><code>1:     n, Name, Species, Description, Classification
  2:     90, Mickey, Mouse, Big ears, rat
  3:     45, Minnie, Mouse, Big bow, rat
  ...
  16835: Code, Species
  16836: RT, rat
  ...
  22673: n, Code, Country
  22674: 1, RT, Murica
  ...
  33211: Activity, Code, Descriptor
  32212: running, RU, senseless activity
  ...
  34749: Last update
  34750: 2017/05/09 02:09:14
  </code></pre>
  
  <p>There are a number of ways going about reading each data set. What I have come up with so far:</p>
  
  <pre><code>filepath &lt;- file.path(paste0(Sys.getenv("USERPROFILE"), "\\SAMPLE.CSV)
  
  # Make a vector with column number per line
  fieldVector &lt;- utils::count.fields(filepath, sep = ",", quote = "\"")
  
  # Make a vector with unique number of fields in file
  nFields &lt;- base::unique(fieldVector)
  
  # Make a vector with indices for position of new dataset
  iHeaders &lt;- base::match(nFields, fieldVector)
  </code></pre>
  
  <p>With this, I can do things like:</p>
  
  <pre><code>header &lt;- utils::read.csv2(filepath, header = FALSE, sep = ",", quote = "\"", skip = iHeaders[4], nrows = iHeaders[5]-iHeaders[4]-1)
  
  data &lt;- utils::read.csv2(filepath, header = FALSE, sep = ",", quote = "\"", skip = iHeaders[4] + 1, nrows = iHeaders[5]-iHeaders[4]-1)
  
  names(data) &lt;- header
  </code></pre>
  
  <p>As in the intro of this post, I have made a couple of functions which makes it easier to get headers for each dataset:</p>
  
  <pre><code>Headers &lt;- GetHeaders(filepath, iHeaders)
  colnames(data) &lt;- Headers[[4]]
  </code></pre>
  
  <p>I have two functions now - one is GetHeader, which captures one line from the file with utils::read.csv2 while ensuring safe headernames (no æøå % etc). </p>
  
  <p>The other returns a list of string vectors holding all headers:</p>
  
  <pre><code>GetHeaders &lt;- function(filepath, linenums) {
      # init an empty list of length(linenums) 
      l.headers &lt;-  vector(mode = "list", length = length(linenums))
  
      for(i in seq_along(linenums)) {
              # read.csv2(filepath, skip = linenums[i]-1, nrows = 1)
          l.headers[[i]] &lt;- GetHeader(filepath, linenums[i])
      }
      l.headers
  }
  </code></pre>
  
  <p>What I struggle with is how to read in all possible datasets in one go. Specifically the last set is a bit hard to wrap my head around if I should write a common function, where I only know the line number of header, and not the number of lines in the following data.</p>
  
  <p>Also, what is the best data structure for such a structure as described? The data in the subtables are all relevant to each other (can be used to normalize parts of the data). I understand that I must do manual work for each read CSV, but as I have to read TONS of these files, some common functions to structure them in a predictable manner at each pass would be excellent.</p>
  
  <p>Before you answer, please keep in mind that, no, using a different export format is not an option.</p>
  
  <p>Thank you so much for any pointers. I am a beginner in R and haven't completely wrapped my head around all possible solutions in this particular domain. </p>