MATLAB Answers

import csv file to specific sheet in excel

15 views (last 30 days)
Tammy Chen
Tammy Chen on 31 Aug 2016
Commented: Henry Giddens on 7 Sep 2016
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.

Answers (1)

Henry Giddens
Henry Giddens on 5 Sep 2016
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.
  2 Comments
Henry Giddens
Henry Giddens on 7 Sep 2016
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)]

Sign in to comment.

Community Treasure Hunt

Find the treasures in MATLAB Central and discover how the community can help you!

Start Hunting!