Bug: Pivot Analysis in attached Excel-Files not possible due to automatic filename-extension

[jens1240 0]

Hello Evernote-Team,

I am using Excel-Sheets to collect a mass of data and analyse them with Excels Pivot functions.

For Pivot-functions Excel uses additional access to the file; And this produces an error

Evernote is adding an Extension to the filename like [1], in the amendment folder. (I guess it is for protection of overwriting of different file Versions)

As these characters are prohibited as filenames, Excel produces an error, when doing a Pivot Analysis. (see attached screenshot)

 

=> Please change the type of characters used for filename-Extension into allowed ones.

 

Thanks

Jens

[gazumped 11,652]

Hi.  When Excel opens a spreadsheet attached to a note,  it actually creates a copy of the spreadsheet in the Attachments folder which is stored inside the Databases folder.  See Tools > Options > General for the location.

This is because Excel can't read the note content directly - the sheet data has been encoded into the database and is no longer a valid Excel format until restored to a normal Windows folder.

The attachments folder contents aren't cleaned out by Evernote - once opened in Excel,  the file MyFile.xlsx stays in that folder.  Any changes saved back to that file will be updated to the note.  But the next time you open the file Windows can't create MyFile.xlsx - it already exists;  so it goes to MyFile(1),  MyFile(2) etc.

The reason Evernote don't clear up after the file is edited - they can't tell whether the file in the attachments folder is an interim save or the final version.  It's simply not possible... https://discussion.evernote.com/topic/100061-fix-multiple-attachment-problem-better-drawboard-integration/?do=findComment&comment=432160

If you wish to carry out complicated analyses of your data,  the better process would be to copy the file to your desktop,  process it there,  and then re-save it back to the note.  You may be able to clear up the random file number issue by closing Evernote and deleting all the content in the Attachment folder,  but the Pivot function may not be compatible with the way in which Evernote supports Windows file handling.

[CalS 5,280]

@jens1240

Yeah, this doesn't happen all the time, sometimes the file opens and closes without any residue.  But when it doesn't, it can be a PITA. 

May not work for you, but I put any large spreadsheets that contain pivot tables in my DropBox folder and put a link to the file in a note in EN.  That way the spreadsheets are opened locally and saved locally while being backed up to the web.  Not a good solution if you want to index the contents of the files, but works fine if you want a control center from which to access your models.  FWIW.

[jens1240 0]

@gazumped: Thanks for the detailed explanation! But one small, important thing, is not correct!

Wrong: "MyFile.xlsx - it already exists;  so it goes to MyFile(1),  MyFile(2) etc."

correct: "MyFile.xlsx - it already exists;  so it goes to MyFile[1],  MyFile[2] etc."

And exactly these square brackets will cause the Problem!!!

These square brackets are not allowed in Excel convention (-> see Attachement of my first post)

So I call it a bug! Itcan be easily solved by using round brackets "(", ")"

I hope in the next Evernote Version this bug will be removed. Until then my Workaround is to rename the Excelsheet in Evernote before opening. By opening this "new file", no automatic Extension will be made and Pivot is working well.

Best regards
Jens

 

[gazumped 11,652]

Hmmn.  I apologise for the bracket mismatch.  However please advise Microsoft of this bug - it's Windows that retitles the saved files,  not Evernote.

[jens1240 0]

Are you sure, it´s Windows, who put the brackets in the filename?

So I tryed to save/copy a file with an existing filename
- in Excel produces round brackets (= perfect)
- in Windows it asks about what to do: "no copy", "overwriting" or "keep both" (Keep both uses also round brackets!

So where does the square brackets come from??
- Maybe is there a Setting anywhere to Change the Kind of brackets? In Evernote or Windows,...??

Thanks for help to solve the Problem! (Round bracket would make the world much easier ;-))
Jens

[gazumped 11,652]

Hmmn.  I know that I have lots of files on my system with square bracketed number suffixes,  but you're right - I can also find some with round brackets.  Whether that's down to the application that created them,  or the OS at the time I couldn't say.  (at various my files have run through most of WIndows finest OS's and their many variations from XP and Vista,  through 7, 8 and 10)

You could certainly leave the round-brackets-in-place-of-square-brackets thought here as a feature request,  but I'd recommend more research into the OS too - it probably isn't within Evernote's power to make that change...