import csv file to specific sheet in excel

Hi all, I'd like to import 2 csv files into two separate sheets of a blank output excel file in their original formats. I know I can retain original format of csv files by using xlswrite to read into specific column of my csv data, copy that data to specific xlrange(ie. A1:E9) in my output excel file. However, that's too cumbersome and requires too many additional lines that slows down my code when processing large data sets. I'm using importdata function here (instead of csvread) to load my csv because my csv files contains text. I'm trying to use xlswrite to copy the csv data onto an output excel file, so I need to load the csv data into matlab as matrices first. The code below somehow don't read my csv data as matrices, which is what I am trying to do below with variable 'J' & 'K':

fidQ = fopen('pvtdata.xls','w+');
  Z = importdata('pvtsort.csv')
  U = importdata('pvtstats.csv')
  J = [[Z.data],{Z.textdata}]
  K = [[U.data],{U.textdata},{U.colheaders}]
  C = xlswrite('pvtdata.xls',J,1);
  S = xlswrite('pvtdata.xls',K,2);
  fidQ = fclose(fidQ)

The code copies empty data onto output excel file. In addition, Matlab throws me the following info in command line while running the code:

Z = 
        data: [11x5 double]
    textdata: {11x36 cell}
U = 
          data: [16x9 double]
      textdata: {'Trails'  'mean'  'median'  'lower%5'  'higher%5'  'lower%10'  'higher%10'  '#false_start'  '#lapse>500ms'}
    colheaders: {'Trails'  'mean'  'median'  'lower%5'  'higher%5'  'lower%10'  'higher%10'  '#false_start'  '#lapse>500ms'}
J = 
    [11x5 double]    {11x36 cell}
K = 
    [16x9 double]    {1x9 cell}    {1x9 cell}

Given the above information, I felt like I'm not formatting the csv data correctly in matlab (ie. loading the data as matrices correctly),as if there's another cell layer to my data, which is why the code above only copies empty data into my output. This is just my conjecture. How do I make matlab actually load my csv data as matrices and xlswrite the matrices in the same format of the csv onto the output excel file? The above code is my failed attempt to xlswrite 'pvtsort.csv' to Sheet1 and 'pvtstats.csv to Sheet2 of output. I'm pretty sure there's an easier/shorter way around this.

Pointers appreciated.

Answer 1

[Henry Giddens]

Hi,

xlswrite attempts to write each element of your array to a single cell in the worksheet. Your arrays (J and K) are cell arrays, and each entry of this is an array (double or cell), which contains multiple elements. Multiple element arrays cannot be written to a single cell in an excel spreadsheet.

To write an array of multiple data types to the xls file, you must first construct a single cell array in matlab, where each entry (or cell) contains the data that will be written to a single cell in the xls file. Data that can be written to a spreadsheet using xlswrite can either be numeric or text (a string).

In your case, you therefore need to combine the arrays 'data' and 'textdata' returned from the importdata function into a single cell array. This can be done by:

J = {Z.textdata; num2cell(Z.data)}

You will then be able to call xlswrite as above.

[Tammy Chen]

Hi, Thanks for looking into this. I tried your solution but it only copied empty cells to sheet 1 & 2 of my excel file, so I figured I need to add several lines to read into the cells. I did this:

V = importdata('pvtsort.csv');
U = importdata('pvtstats.csv');
Z = {V.textdata; num2cell(V.data)};
J = {U.colheaders; U.textdata; num2cell(U.data)};
Zsize = 1:size(Z)
Jsize = 1:size(J)
Zall = Z{Zsize}
Jall = J{Jsize}
writeZ = xlswrite('pvtdata.xls',Zall,1);
writeJ= xlswrite('pvtdata.xls',Jall,2);

...but it only copied the V.textdata from "Z" variable and U.colheaders from "J" variable to sheet one and two of output excel. I guess I didn't combine the components of Z and J properly or shouldn't have used the size function, but that was the only way I figured to copy anything at all to my output file (instead of just empty cells). Any ideas?

[Henry Giddens]

Sorry, I made a mistake... I shouldn't have used the curly brackets but the square brackets. There should be one entry, (either a string or a number) in each cell of the array J.

Try

J = [Z.textdata; num2cell(Z.data)]