The Stack Overflow podcast is back! Listen to an interview with our new CEO.

Hot answers tagged

44

Here is a manual method using copy-and-paste that is suitable for small volumes of data on an ad hoc basis... 1) Enter the following expression into a notebook, but don't evaluate it: data = ImportString["", "TSV"] 2) Copy the cells from the source spreadsheet onto the clipboard. 3) Paste the contents of the clipboard between the empty pair of quotes in ...


23

You don't need the initial InstallNET[]. That should come after Needs["NETLink"]. I made a post on this topic a while back, here: http://forums.wolfram.com/mathgroup/archive/2011/Oct/msg00386.html Some code to illustrate the method: Needs["NETLink`"] ReadFromExcel[file_String, cell_String, rows_Integer, cols_Integer] := Module[{excel, workbook, ...


19

Excel VBA enumeration values cannot be accessed symbolically through COM. We must use the corresponding numeric values found by consulting the Microsoft Excel object model enumeration reference. The relevant enumerations in this case are XlBordersIndex (xlDiagonalDown = 5) and XlBorderWeight (xlThick = 4). Once we know the enumeration values, the code is ...


16

From a very pragmatic point of view, it might be easier to use an external tool such as xlsx2csv (Python script, but other alternatives exist). Then simply import the comma-separated values: ImportString[StringJoin @Riffle[ReadList[OpenRead["!./xlsx2csv.py test.xlsx"], "String"], "\n"], "CSV"]; On a 21 MB XLSX file on my Mac Book Pro, the above takes 115 ...


15

I've got VBA calling Mathematica functions. It's not without issues, but maybe some other smart people here can help with the hiccups. First things first: The .dll that Mathematica includes with its installation for .NETLink is not COM-compatible, meaning that VBA cannot find entry points into the dll functions. To get around this, .NET must be installed ...


14

In the answer linked here, I did the following: out = FileNameJoin @ {$TemporaryDirectory, "MathematicaOutput" <> ToString /@ Date[] <> ".rtf"} /tmp/MathematicaOutput2012519111731.900549.rtf Then you would say Export[out, ...]. If you want to have the date in a more readable and less detailed form, you could use this for the name: "file-" &...


13

You can also try to increase the Java Virtual Machine Heap space using the following code Needs["JLink`"]; ReinstallJava[JVMArguments -> "-Xmx1536m"];


12

If your Excel file test.xls is very simple: Then the code is a one-liner (if I understand correctly what is needed): Set@@@Transpose[{ToExpression[First[#]], Transpose[Rest[#]]}&@Import["test.xls"][[1]]] To check: {Paris, Moscow} {{1., 2., 3., 4., 5., 6., 7., 8.}, {12., 23., 34., 45., 56., 67., 78., 89.}} The rest is more complex cases. Imagine ...


12

Overall, your data is just badly formatted. For instance, later in the list your dates look similar to "3-Mar" which is interpreted as the third of March not March 2003, as you intended. For the most part, this is not your fault, but Excel arbitrarily formats data, and you have to be vigilant that it doesn't misinterpret it. Towards that end, I've rewritten ...


12

I see there are no accepted answers for this question after more than 10 months so I thought I'd have a go at it. Although I have been using Mathematica for since V8, I am only an occasional user and hence not at all an expert like the others who have chimed in so far - but I'll give it a shot. Rather than using the Java based import to open and import the ...


12

You can easily implement it in two steps. You create an Excel macro in your personal.xlsb that you can use to execute some keybord shortcut to copy your selected data. I use CTRL+SHIFT+C. Second you can create a Mathematica function to import this clipboard data (optional, but very usefull) More information on how to handle your personal.xlsb here How to ...


12

The interpolating functions that NDSolve returns contain an irregular grid that reflects which points were used to calculate the solution. Not always, but often this grid is a better choice than a regular grid as you would generate with Table when exporting. Here is how you could export the data as NDSolve generated it: a = 10^-2; eq1 = {hf'[t] == -a*(hf[t] ...


12

Unfortunately, it does not appear that this is possible without the Enterprise edition of Mathematica (or, perhaps, an alternative conclusion is that Mathematica is not the best tool for this job). As Mr. Wizard points out: I'm afraid you are in for disappointment. Apart from the Enterprise Edition or Wolfram Player Pro Mathematica does not have this ...


12

Using SE as my rubber duck to organize ideas worked just nice. I found the answer in NETLinkUserGuide. To get current Excel session use GetActiveCOMObject instead of CreateCOMObject. Here is a code example. Needs["NETLink`"] InstallNET[] excel=GetActiveCOMObject["Excel.Application"] workbook=excel@Workbooks["MySpreadsheet.xlsx"]; worksheet=workbook@...


11

I see several ways how you could achieve what you want. Any of them either needs extra "non-mathematica" software, efforts or knowledge. I think the best way is to learn how to interact with excel via .Net/COM as is described e.g. in the documentation, but you have mentioned that you want to avoid that. Here are the alternatives that I can think of: Use ...


11

Import as Table instead of CSV, and set the "FieldSeparators" option. ImportString[ "1;2 3;4", "Table", "FieldSeparators" -> {";"}] (* {{1, 2}, {3, 4}} *) "Table" is a generalization of CSV, TSV, etc. where you can customize the delimiters and separators.


10

You can export both data and the images using one of several syntax patterns that you find in the docs on XLS format: For example: g = CompleteGraph[7]; Export["output1.xls", {g, {"mySheet1" -> Normal@AdjacencyMatrix[g]}}, {{"Images", "Sheets"}}] gives EDIT: Exporting multiple images: It seems you need at least one sheet (which could be empty) as ...


10

You can copy a tabulated data to clipboard and then paste it in a sheet with formatted cells without loss of formatting: CopyToClipboard@ Cell[StringReplace[ ExportString[myTable, "TSV"], "\r\n" -> "\n"], TextClipboardType -> "PlainText"] Also you can automate this process by using Data ► Import from external source... ► Import data... Excel ...


10

(*show all sheets*) Import["D:\\allstuff.xls", "Sheets"] {"portf", "notes", "calc", "xirr", "tx", "JTrade", "Yahoo"} Import["D:\\stuff.xls", {"Sheets", "portf", 6}] (*gets 6th row of sheet named "portf"*) Leave off the 6 to get the whole sheet or say 6,2 to get one cell at 6,B.


10

Let's say you've managed to import the data from excel and you want to transform list: {{strength, angle}...} so it fits the methods from the Q&A: How to create a wind rose with Mathematica?. There are couple of operations you must do: r = Table[{RandomReal[{0, 20}], RandomReal[{0, 360}]}, {t, 0, 15}]; (*sample data*) Your list is not sorted with ...


9

I know this is an old question, but I ran into this problem myself. The permanent solution for me was to alter the settings of JLink directly. As for most commenters too, increasing the heap-space by giving commandline options to ReinstallJava and friends didn't work. Specifically, I tried to increase the heap-space by specifying the complete CommandLine ...


9

If you know the number of header rows, you can always just Drop that number of rows as part of the code to import the data. For example, suppose your file is called "myfile.xls" and the worksheet is called "Data". Then your code would be: data = Drop[Import["myfile.xls",{"Sheets","Data"}],1] or using Part with Span (;;): data = Import["myfile.xls",{"...


9

A simple solution is to use Rationalize on the result. If your original data is all integers, but with the head Real (e.g. {1., 2., 3.}) then they'll have the head Integer now (i.e., {1, 2, 3}).


9

For example: (* some data *) pts = Range[10]^4; f1 = Interpolation[pts, InterpolationOrder -> 1]; f2 = Interpolation[pts, InterpolationOrder -> 3]; t1 = Join[{{"x", "f1[]"}}, Table[{x, f1@x}, {x, 1.5, 4.5, 1}]]; t2 = Join[{{"x", "f2[]"}}, Table[{x, f2@x}, {x, 1.5, 4.5, 1}]]; data = Join @@@ Transpose@{t1, t2}; (* Transformation from Excel "A1" ...


9

It seems there are some troubles with the XLSX format, whereas using the old XLS it works fine. Look the following tests: Import["c:\\tempmath\\test.xlsx", "Sheets"] {"SheetWithArrayFormula"} Import["c:\\tempmath\\test.xlsx", {"Sheets", "SheetWithArrayFormula"}] Import::fmterr: Cannot import data as XLSX format. >> $Failed Import["c:\\tempmath\\...


9

There are a few different syntaxes for exporting formulas, all giving the same results. For example: Export["test1.xlsx", {"Data" -> {1, 2, 3, 4, 5}, "Formulas" -> {{{"", "A1*27", ""}, {"", "A2/3", "B1*B2"}}}}, "Rules"] Export["test2.xlsx", {{1, 2, 3, 4, 5}, {{{"", "A1*27", ""}, {"", "A2/3", "B1*B2"}}}}, {{"Data","Formulas"}}] someData = {1, 2, 3, ...


8

If we define your parameters as: tstep = .5; tstop = 10; nss = StateSpaceModel[{ {{0, 1, 0, 0}, {5.3572, 7.602, 56.6571,18.102}, {0, 0, 0, 1}, {-5.3572, -7.602, -46.8571, -18.102}}, {{0}, {0.5}, {0}, \{-0.5}}}, SamplingPeriod -> None, SystemsModelLabels -> None]; Then store the response as a list: response = Table[...


8

a = 10^-2; eq1 = {hf'[t] == -a*(hf[t] - hs[t]), hs'[t] == a*(hf[t] - hs[t]), hf[0] == 20, hs[0] == 0}; sol1 = NDSolve[eq1, {hf, hs}, {t, 0, 100}] Now: Plot[{hf[t], hs[t]} /. sol1, {t, 0, 100}] Export["c:\\test.xls", Table[Flatten[{t, hf[t], hs[t]} /. sol1], {t, 0, 100}]]


8

Ok, well, I only needed the inverse function so far and have implemented it as: FromExcelCol[col_String] := FromDigits[ToCharacterCode[col] - 64, 26] It runs fine because FromDigits does not complain about characters larger than base-1. However, the other way round seems to be more tricky. The leading digit runs from 0 to 26 (1 to 27 if you want -> base ...


8

The following syntax works for exporting the two sheets: data1 = Table[{t, 9.8*t^2}, {t, 0, 3, .1}]; data2 = Table[{t, 9.8*t^2}, {t, 0, 5, .2}]; Export["datasets.xlsx", {"Experiment 1" -> data1, "Experiment 2" -> data2}] It creates an XLSX file with two seperate sheets holding the datasets: However, i agree with the comments above that QA for ...


Only top voted, non community-wiki answers of a minimum length are eligible