[R] Multiple CSV files in different sheets of an Excel file

[Marc Schwartz]

Hi,

Just to augment Jeff's comments, if you envision taking one of your CSV files (presuming that they are truly a CSV format) and opening it with Excel without any other pre-processing, that is what would happen by default with the majority of R tools that can create Excel files.

Excel, as you may be aware, can directly open CSV files and place the incoming data into a single worksheet. The comma delimiters and line breaks would be parsed and the data would be placed into appropriate rows and columns. You might even want to try that with one of your files, just to see what happens.

You need to think of the worksheets in Excel as being like an R data frame, which has a regular rectangular structure with rows and columns. Sure, you can do things like merge individual cells, etc. in Excel, but the essential structure is a row/column rectangle.

If your CSV files do not conform to that general structure, then you are going to have to do some pre-processing, either in R or elsewhere, before writing the data to worksheets in an Excel file using R.

In the case of WriteXLS, which I mentioned earlier, you are limited to essentially exporting data frame objects to the Excel worksheets. That is the primary goal of the package.

Some of the other Excel file writing packages provide for additional functionality, including formatting and manipulating cells and similar things. But, you would have to code around any uniqueness in each of your CSV files, if they do not have a consistent format within each file and from file to file.

One alternative in R, would be to use ?readLines (note capital 'L'), which will simply read each line of the CSV files into an R vector. You would then have to parse each line as may be required, into a format that would then conform to the requirements of the Excel row/column cell structure. Perhaps even consider writing the rectangular data in each file to one worksheet and the tables contained into separate worksheets. 

That all being said, you may want to take a step back and re-consider, whether or not this process is really what you want to do and whether or not there is a solution to your problem that does not involve using Excel as the end result.

Regards,

Marc Schwartz


> On Jan 13, 2016, at 12:16 PM, Jeff Newmiller <jdnewmil at dcn.davis.ca.us> wrote:
> 
> There is no such thing as a "csv sheet"... if you have a complex layout of data in one CSV file then if you want to import it into R then you are going to have to cobble together something that reads the file as character data and parses the pieces out using functions from the grep or sub category. If your goal truly is to go from these files into Excel files with no data processing in R then perhaps you should be asking for help in a forum dedicated to Excel or OpenOffice.
> -- 
> Sent from my phone. Please excuse my brevity.
> 
> On January 13, 2016 8:30:42 AM PST, Mohsen Jafarikia <jafarikia at gmail.com> wrote:
>> Hi Frans,
>> 
>> There is a problem that my csv files are not just several columns of
>> data.
>> I have some data and then a couple of tables after my data. I can't use
>> read.table to read my files. There should be something different to
>> look at
>> the whole csv sheet.
>> 
>> Thanks again,
>> Mohsen
>> 
>> 
>> On Wed, Jan 13, 2016 at 9:53 AM, Frans Marcelissen <
>> fransiepansiekevertje at gmail.com> wrote:
>> 
>>> Hi Mohsen,
>>> Just read them with read.csv or read.table
>> (file1<-read.csv(file=.....),
>>> and you can write them.
>>> Success!
>>> Frams
>>> 
>>> 2016-01-13 15:47 GMT+01:00 Mohsen Jafarikia <jafarikia at gmail.com>:
>>> 
>>>> Thanks Frans,
>>>> 
>>>> My files are CSV. If presume first I should convert them to Excel
>> format
>>>> and run the code you have suggested. Am I right?
>>>> 
>>>> Thanks again,
>>>> Mohsen
>>>> 
>>>> 
>>>> On Wed, Jan 13, 2016 at 9:44 AM, Frans Marcelissen <
>>>> fransiepansiekevertje at gmail.com> wrote:
>>>> 
>>>>> Hi Mohse,
>>>>> You can do that with the append parameter of the write.xlsx routine
>> in
>>>>> the xlsx package:
>>>>> 
>>>>> xlsx::write.xlsx(file1,file='XXXXX.xlsx',sheetName = '1')
>>>>> xlsx::write.xlsx(file2,file='XXXXX.xlsx',sheetName = '2',append =
>> T)
>>>>> 
>>>>> Success!
>>>>> Frans
>>>>> 
>>>>> 2016-01-13 15:18 GMT+01:00 Mohsen Jafarikia <jafarikia at gmail.com>:
>>>>> 
>>>>>> I have multiple CSV files that I would like to have them in a
>> single
>>>>>> Excel
>>>>>> file. For example, I have file1.csv and file2.csv and I want to
>> have
>>>>>> file.xls where file1.csv and file2.csv each have been copied to a
>> single
>>>>>> sheet of the file.xls file.
>>>>>> 
>>>>>> Thanks,
>>>>>> Mohsen
>>>>>> 
>>>>>>        [[alternative HTML version deleted]]
>>>>>> 
>>>>>> ______________________________________________
>>>>>> R-help at r-project.org mailing list -- To UNSUBSCRIBE and more, see
>>>>>> https://stat.ethz.ch/mailman/listinfo/r-help
>>>>>> PLEASE do read the posting guide
>>>>>> http://www.R-project.org/posting-guide.html
>>>>>> and provide commented, minimal, self-contained, reproducible code.
>>>>>> 
>>>>> 
>>>>>