Message Boards Message Boards

0
|
8872 Views
|
8 Replies
|
6 Total Likes
View groups...
Share
Share this post:

How to export Tables with high precision and column headers?

Posted 10 years ago

Dear Community,

I had some progress on my calculations and would like to export my findings to an Excel Sheet, preferably with column headers. The underlying formula is the following:

h[i_] := {i,
  Extract[Extract[f[i], {1}], {1}],
  Association[Extract[Extract[f[i], {1}], {2}]][[Key[w]]],
  Association[Extract[Extract[f[i], {1}], {2}]][[Key[x]]],
  Association[Extract[Extract[f[i], {1}], {2}]][[Key[y]]],
  Association[Extract[Extract[f[i], {1}], {2}]][[Key[z]]]}

and returns results like this list:

{7500, 1.44263, 3622.8, 3877.2, Missing["KeyAbsent", Key[y]], 0.}

Unfortunately

Export["RequestedTable.xls", {"Header A","Header B","Header C","Header D","Header E","Header F"},
 SetPrecision[Table[h[i], {i, 7300, 7700, 100}], 5]]

throws an error, saying the length 0 of Header A does not match length 6 of the following list entries (or something like that...).

(EDIT: Changing the command to

Export["RequestedTable.xls", 
 Extract[{{"Header A","Header B","Header C","Header D","Header E","Header F"}, 
   SetPrecision[Table[h[i], {i, 7300, 7700, 100}], 5]}]]

eliminates the error message, but the whole formula exports into one cell. Within this formula the values do have a high precision, though. It seems like they get lost while being written into single cells in the Excel sheet...?)

Neglecting the headers and executing the command

Export["RequestedTable.xls", 
 SetPrecision[Table[h[i], {i, 7300, 7700, 100}], 5]]

the export succeeds, but only with whole numbers. The numbers can be small, though, which requires an output including decimals. But although I'm using SetPrecision, the resulting Excel table only includes whole numbers. This raises three questions:

  • Is it possible to return 0 (zero) instead of Missing["KeyAbsent", Key[whatever]]?
  • How can I include a header into the export?
  • Most important: How can I increase the precision of the export?

Best regards, Peter

POSTED BY: Peter Bergmann
8 Replies
Posted 10 years ago

Strange, that's exactly how I want it to be...! The error messages occur, because the long Minimum-Function consists out of several minimum functions - and some of them will return errors if the requested number becomes too big and therefore cannot fulfill any constraint within them.

Anyways... I would love to know, why this is not working on my side. I also tried to increase the decimal part, but it will only show zeros... I wonder if this has anything to do with my Mathematica running on a Raspberry Pi or the Export itself being run on a Raspberry Pi, which does not run Excel by itself. Every time I have an exported file, I email it to a stationary PC and open it there. Shouldn't be a problem to my understanding, though?!

POSTED BY: Peter Bergmann
Posted 10 years ago

Hi Peter

Take a look at the attached picture, I'm not sure why I was getting those warning messages, but that's what I got just running each section from top to bottom, however there was some output sent to Excel as you can see and it was showing a decimal part. I honestly don't think it has anything to do with Mathematica but some settings in Excel. Have you tried selecting the cells in Excel and increasing the decimal part, select all the cells, right click and choose format cells, choose number and then increase the decimal part.

enter image description here

Paul.

POSTED BY: Paul Cleary
Posted 10 years ago

Good morning Paul,

Sorry for the late reply, I didn't have time to follow up with this during the last week...

Well, the calculations definately have a decimal part. They are shown in the intermediate results. The full code is attached. It'd be great if we can figure this out...!

BR, Peter

Attachments:
POSTED BY: Peter Bergmann
Posted 10 years ago

Hi Peter

At face value I can only assume that the results of your calculations don't have any decimal part, the results from your first example certainly don't, do you have a full example code I can try?

Paul.

POSTED BY: Paul Cleary
Posted 10 years ago

Hi Paul,

thanks a lot for your answer! The command to implement the header is great - I did a lot of reading in the guides, but never discovered the Join to be this powerful. Anyways, the precision problem still is not solved (see attached file for outcome). Is this just happening to me?!

BR, Peter

Attachments:
POSTED BY: Peter Bergmann
Posted 10 years ago

just had another look at this, try this code which generates decimal numbers and open the sheet, it defaults to a few dp, but you can set the precision in excel to say 14 dp and its fine.

Export["RequestedTable.xlsx", 
 Join[{{"Header A", "Header B", "Header C", "Header D", "Header E", 
    "Header F"}}, 
  SetPrecision[Transpose@Table[{i/17}, {i, 7300, 7800, 100}], 10]]]

Paul.

POSTED BY: Paul Cleary
Posted 10 years ago

Hi Peter

See if this is any better

Export["RequestedTable.xlsx", 
 Join[{{"Header A", "Header B", "Header C", "Header D", "Header E", 
    "Header F"}}, 
  SetPrecision[Transpose@Table[{i}, {i, 7300, 7800, 100}], 5]]]

Paul.

POSTED BY: Paul Cleary
Posted 10 years ago

Hey guys,

is there nobody, who can help me out on this? Right now I'm thinking of a workaround to solve at least the precision-problem. I'll multiply the second value by 1000 or a Million and divide this after exporting in Excel. But that's probably not how it's ment to be, right?

BR, Peter

P.S.: If you need the complete code of this Problem, please let me know.

POSTED BY: Peter Bergmann
Reply to this discussion
Community posts can be styled and formatted using the Markdown syntax.
Reply Preview
Attachments
Remove
or Discard

Group Abstract Group Abstract