vim: what the current best practice to work with excel file (xlsx)?

[ping]

hi guys:
I know this is kind of an "old" topic.
my colleague send me some good forms but unfortunately (as expected) in a MS xlsx file.
I can open it with OOO or even MS-excel2007(via a crossover simulation) smoothly, w/o an issue (I run Ubuntu).
but still I want to conveniently copy/paste from excel into my vim notes.

I tried "save as" from excel into:

format                                 issue
=======================================================================
csv (comma delimited)                 some of my data contains comma
formatted text(space delimited)         hard to align
web page                              hard to render a framed page

is there any known best way to load my excel data into vim today?

thanks!

regards
ping

[Erik Christiansen]

On 08.06.12 09:45, ping wrote:
> I tried "save as" from excel into:
>
> format issue
> =======================================================================
> csv (comma delimited) some of my data contains comma

If cell data contains cell delimiters, can the saved file even be
re-imported into excel? i.e. It is corrupted data which is being saved,
AIUI. The few spreadsheets I've encountered (long ago, admittedly) allow
the cell delimiter to be set prior to saving the file. Can excel not do
that? (It's what I've repeatedly done in similar circumstances ... well,
actually asked others to do with their spreadsheets, before mining
the data with text tools. ISTR tab being used instead of comma.)

> formatted text(space delimited) hard to align

An aligning plugin was named on this list in the last week or so, ISTR.
It might do the job?

> web page hard to render a framed page

That does seem like the long way there. :-)

Erik

--
After twelve years of therapy my psychiatrist said something that
brought tears to my eyes. He said, "No hablo ingles."
- Ronnie Shakes

[ping]

thanks Eric,
I was trying to find out how to define my own delimiter (like a : or ; depending on which
one is not in the data itself) but no luck.
I'll login to a real windows machine with an EXCEL and try again.
b.t.w, space as a delimiter will need to face the same issue - the data might have spaces.

regards
ping

[lith]

> csv (comma delimited)                 some of my data contains
> comma

If your method of changing the field separator doesn't work, as a last resort you could also change the locale (some use ; as field separator). Or, the proper solution, you could enclose the field in quotes (you'd probably have to research the proper escaping rules).

It's odd that OO or LibreOffice cannot open the file. Can you import the file into google docs?

Regards,
Tom

[ping]

hi Tom:
yes I did some research, very funny that the method to define a new delimiter when exporting data from EXCEL is outside of the App:
http://www.howtogeek.com/howto/21456/export-or-save-excel-files-with-pipe-or-other-delimiters-instead-of-commas/

since I'm running crossover (wine emulation) MS excel, it doesn't work for me.

b.t.w I meant OO *CAN" opent the MS files without issues in most time.

[Christian Brabandt]

Hi ping!

Use the csv plugin

regards,
Christian
--

[John Beckett]

ping wrote:
> is there any known best way to load my excel data into vim

When saving as csv, I thought Excel put quotes around fields
that contain commas? I do know that the following tip attempts
to handle csv files that contain quoted strings. Also, it has a
section on using a Perl script to work with Excel:
http://vim.wikia.com/wiki/CSV

John

[ping]

hi Chris/folks:

thanks for the suggestion.
OOO has this good feature I forgot completely that to let you define "text delimiter" (" by def) and "field delimiter" (, by def) ...
so there won't be an issue if the content of fields contains any comma or any characters..

today I look at the csv plugin again, and I got the issue right after I log a csv file:

//say this is my file content:
,,,
"NH Walk for MAC validation  for Customer's with dhcp binded but not pingable",,,
,,,
"Commands","Where to Execute","Expected Output","Actual Output collected for a affected subscriber"
"show ip route <IP of the affected subscriber> detail","CLI","it will display the information about particular interface. From this we can fetch the interface index.  See comments for example.(IntfIndex 0x1F)",
"showInterfaceInfo <0x1F>","Line card shell","From this command we can fetch the interface channel value. See comments for example(channel  = 0x00000408).",
"ipAlShowIf <0x00000408>","Line card shell","From this command we can fetch FC & IC address of codebranches which are all called & created for that particular channel.(SRAM 0xc0207d18 - codebranch pointer for Demux Tree(For mac validation))",
"luKtreeDump <0xc0207d18>","Line card shell","From this command we can fetch the descriptor pointer for the particular Ktree node.",
"luCallTableDescWalk <0,0x8020e898,4,1>","Line card shell","From this command we can get the SRAM address of the MAC entry stored.",
"luDumpSram <0,0xc0207e70,20>","Line card shell","From this command we can fetch the MAC address which is stored in the FC address.",
"show ip mac-validate int <gig 2/0/3.10>","CLI","This command will display the MAC address which is stored in the application side. This is used to check and make sure whether the same MAC address present in FC address too.",



//after loading it looks like this:


it's kind of messed up. or most probably I missed sth?

regards
ping

[Christian Brabandt]

Hi ping!

> it's kind of messed up. or most probably I missed sth?

I really barely understand, what you are trying to say. If your file
contains \n within a cell, then this is probably the problem, why it
looks bad. But please attach some sample data or upload it somewhere, or
else I don't know what you mean. A screenshot also helps.

regards,
Christian
--
Das Gehirn ist ein wundervolles Organ. Es f�ngt sofort an zu arbeiten,
wenn man morgens aufwacht, und h�rt nicht auf damit, bis man im B�ro
ist.
-- Robert Lee Frost

[ping]

sorry about the confusion..not sure why you didn't get my screenshot in my last email. and my file lines were all truncated (it does not contain \r).
guess it was filtered by google-group.
here attached the file and hope you can get it this time.

b.t.w, after reading csv plugin help , It looks I can use :ArrangeColumn after visual selection to format the selected texts...
but I'm not sure why this is not the default behavior.

regards
ping

[Christian Brabandt]

Hi ping!

On Di, 12 Jun 2012, ping wrote:

> sorry about the confusion..not sure why you didn't get my screenshot
> in my last email. and my file lines were all truncated (it does not
> contain \r).
> guess it was filtered by google-group.
> here attached the file and hope you can get it this time.

Thanks. After a quick look, I think, the CSV plugin can work with this
data. The only problem seems to be, that some lines have 4 columns,
while other have 3 columns. :ArrangeColumn will complain about that, but
should still work.

> b.t.w, after reading csv plugin help , It looks I can use
> :ArrangeColumn after visual selection to format the selected
> texts...
> but I'm not sure why this is not the default behavior.

Because the plugin by default should not change the buffer content and
because :ArrangeColumn can be very slow for large data files.

If you still want this behaviour, have a look at :h csv-arrange-autocmd

regards,
Christian

[ping]

been nearly a year now...
from time to time I use this plugin, and it mostly of the time works great.

another issue I got annoying is , the <space> was re-mapped to some
folding functions.
I understand this is a neat feature, but that overides my previous map,
to use <space> as c-f (page down).
is there a way for me to undo this? (or can I define my own key for the
folding?)

thanks!

regards
ping

On 06/12/2012 04:41 PM, ping wrote:
> oh I got it, making sense ,
> I'll see how frequent I need this and learn more about how to fine tune
> it per my need.
>
> thanks!

[Christian Brabandt]

Hi ping!

On Fr, 05 Apr 2013, ping wrote:

[CSV filetype plugin]

> been nearly a year now...
> from time to time I use this plugin, and it mostly of the time works great.
>
> another issue I got annoying is , the <space> was re-mapped to some
> folding functions.
> I understand this is a neat feature, but that overides my previous
> map, to use <space> as c-f (page down).
> is there a way for me to undo this? (or can I define my own key for
> the folding?)

Hm, no, currently there is no way to prevent the mapping of keys. I'll
take care of that.

For the time being, I suggest put your favorite mapping into a file
~/.vim/after/ftplugin/csv.vim

That should take care of it.

regards,
Christian
--

[ping]

thanks!

[ping song]

hi Chris:

did you add any new feature to solve this problem?

today I use your plugin to open another 20M csv file, and since I mapped my <blank> key into C-f, it froze for quite a while on my keystroke again...

can I config the plugin to use other keystroke for what it is doing with <blank> key?

thanks!

[Christian Brabandt]

Hi ping!

On Mi, 28 Aug 2013, ping song wrote:

> hi Chris:
> did you add any new feature to solve this problem?

This problem is better addressed at me directly or using the github
issue tracker. I don't think it makes sense to bother this list with it.

> today I use your plugin to open another 20M csv file, and since I mapped my
> <blank> key into C-f, it froze for quite a while on my keystroke again...
> can I config the plugin to use other keystroke for what it is doing with
> <blank> key?

This problem has been solved since you mentioned it the last time.
Simply set :let g:csv_nomap_<key> = 1 into your .vimrc, e.g. if you
don't want to have space mapped, use

:let g:csv_nomap_space = 1

Note, this is also described in the help at :h csv-mapping

regards,
Christian
--
Man mu�, schon aus Welt, dem andern auch nicht das geringste
Unangenehme sagen, sobald man nicht ihn oder sich bessern damit will
oder kann. �Sage nicht zum Mietsherrn, deine Zimmer haben keine
Morgensonne.�
-- Jean Paul