1
$\begingroup$

I have 70 Excel workbooks (in .xlsx format) that I need to import into Mathematica.

The first workbook contains the following table: Excel Workbook 1

The second workbook contains the following table: Excel Workbook

and I have 68 more workbooks that obey the exact same format.

How can I import those 70 Excel workbooks into Mathematica; so that I can in turn export them into a single Excel workbook that contains 70 worksheets? The first worksheet could be called "221100"; the second could be called "221200"; etc.

Thank you!

$\endgroup$

2 Answers 2

1
$\begingroup$

You may use a "Directory" Import and then Export "XLSX" with the "Sheets" syntax and "Rules" parameter.

First copy the 70 files to a their own directory without any other .xlsx files. You do not need to rename the files but all the .xlsx files in the directory will be imported.

Then import all "XLSX" files in the directory.

xlsxData = Import["<directory path>", {"Directory", "*.xlsx"}];

Next construct the "Sheets" Export syntax with the first sheet of each workbook and take the sheet name from the first cell of the first sheet.

sheets = StringTrim@*First@StringSplit[#[[1, 1, 1]], "-"] -> #[[1, 2 ;;]] & /@ xlsxData;

Finally Export to one workbook.

Export["<path>"<>"stuff.xlsx", "Sheets" -> sheets, "Rules"]

Hope this helps.

$\endgroup$
1
$\begingroup$

One way may be the following:

  • create a folder on the desktop and name workbooks;

  • enter the folder 70 file.xlsx;

  • numbers such files from 1 to 70;

  • in MMA do work the following code:


datasheets = Table[0, 70];

namesheets = Table[0, 70];

For[i = 1, i <= 70, i++,

datasheets = ReplacePart[datasheets, 
i -> Import["C:\\Users\\TeM\\Desktop\\workbooks\\" <> ToString[i] <> ".xlsx"]];

namesheets = ReplacePart[namesheets, 
i -> "\"MySheet" <> ToString[i] <> "\"\[Rule]datasheets[[" <> ToString[i] <> "," <> "1]]"]];

Export["C:\\Users\\Manu\\Desktop\\workbooks\\merge.xlsx", 
                  {ToExpression[namesheets]}, {{"Sheets"}}];

where, of course, you will have to fix the address of the folder and wanting well as the names of the excel sheets. In that folder you will find the desired "merge.xlsx".

$\endgroup$
2
  • $\begingroup$ I'm having trouble importing the files using the For[i=1, i<=70, i++... $\endgroup$ Feb 20, 2017 at 16:02
  • $\begingroup$ I use: setDir := Quiet@Check[SetDirectory@DirectoryName@$InputFileName, SetDirectory@NotebookDirectory[]] I can then import the workbooks into Mathematica using: workbook1 = Import["1-221100.xlsx", {"Sheets", "Projection Results", All}]; workbook2 = Import["2-221200.xlsx", {"Sheets", "Projection Results", All}]; etc. because the worksheet containing the data in each of the 70 input workbooks is called "Projection Results". How can I modify the above For[i=1, i<70, i++ ... program to import the workbooks? Thanks! $\endgroup$ Feb 20, 2017 at 16:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.