Wednesday, August 5, 2009

ORCL - Remove ^M in VI

Our management loves to use MS office products, Word, Excel, Sharepoint, to name a few...Our dev is Oracel/Unix, good thing we picked Oracle, since that is the main stream of univeristy? (BTW, I am working in university external relation department).

Quite often, we need get excel file to import into Oracle database and massaging data. Most colleagues use some gui developer tools such as Toad, PL/SQL Developer to do the job, which are pretty good. I like the Oracle external table (Worked a few years on SQL loader :).

To get file from local (of course Windows) to unix server, the PSCP comes handy! It's from Putty, maintained by a small team based in Cambridge, England, and easy command line. There are also some GUI tools like WinSCP, etc.

But sometimes when I convert excel into csv (comma, tab delimited), the external table will not work due to the stupid ( I should not say this :( )^M at the end of lines. UNIX treats the end of line differently than other operating systems. Sometimes when editing files in both Windows and UNIX environments, a CTRL-M character is visibly displayed at the end of each line as ^M in vi.

Good thing that Vi powerful replace command, :%s/^V^M//g, make sure ^V^M are ctrl-v and ctrl M, not copying/pasting :) . It looks not showing on VIM - GUI vi.

The :%s is a basic search and replace command in vi. It tells vi to replace the regular expression between the first and second slashes (^M) with the text between the second and third slashes (nothing in this case). The g at the end directs vi to search and replace globally (all occurrences).

Just another comment: I don't know why some IT people, especially the network/helpdesk IT guys like windows stuff so much (acutally I love that too, but just dont give up on *nix:), really user friendly? They will do anything to eacpae from any possibility of *nix stuff. Sigh... Are we thinking about converting data from Oracle to SQL server to work on Sharepoint stuff? Maybe it's good for performance.

We are IT people, we should handle everything and anything :)))

No comments:

Post a Comment