Converting delimited file to csv or excel file - column wise
Linux - NewbieThis Linux forum is for members that are new to Linux.
Just starting out and have a question?
If it is not in the man pages or the how-to's this is the place!
Notices
Welcome to LinuxQuestions.org, a friendly and active Linux Community.
You are currently viewing LQ as a guest. By joining our community you will have the ability to post topics, receive our newsletter, use the advanced search, subscribe to threads and access many other special features. Registration is quick, simple and absolutely free. Join our community today!
Note that registered members see fewer ads, and ContentLink is completely disabled once you log in.
If you have any problems with the registration process or your account login, please contact us. If you need to reset your password, click here.
Having a problem logging in? Please visit this page to clear all LQ-related cookies.
Get a virtual cloud desktop with the Linux distro that you want in less than five minutes with Shells! With over 10 pre-installed distros to choose from, the worry-free installation life is here! Whether you are a digital nomad or just looking for flexibility, Shells can put your Linux machine on the device that you want to use.
Exclusive for LQ members, get up to 45% off per month. Click here for more info.
Converting delimited file to csv or excel file - column wise
Hi ,
The input file looks as below, which has around 236 rows.
In each row data is separated by the delimiter "|"
Wanted to convert the below kind of file in unix to a CSV or excel file in rows and column format .
Note: each row data separated by delimiter | has to be converted to columns
I like this kind of question.
But there is one thing in our description which is only about 50% clear.., that is, when you say Note, which indicates something special, that not everybody may think of immediately. Because of this “Note” I deem it possible that you want rows converted to columns...
For the remainder, it depends much on our clarification about that mentioned detail. Let us assume for a moment, that you do not need this conversion and just hope to open the CSV and have rows in the text-file displayed as rows in the spreadsheet. Gnumeric opens the CSV file directly and converts it automatically to a spreadsheet. Screen Shot: http://pix.toile-libre.org/?img=1458203566.png
For merging those cells which should be merged, several methods are available, depending on your needs and preferences.
Last edited by Michael Uplawski; 03-17-2016 at 03:34 AM.
If you have LibreOffice Calc available, you can open the file directly and then save it as CSV or ODS or whatever. When opening the file with Calc, it will go through an import wizard. There you have "separator options" -> "separated by" -> "other" and then put in the pipe symbol. It should then read in the rows and put the data in separate columns.
@grail, I had not even noticed at first that your post is in response to mine. When I wrote e.g., I meant “for example”, but I see where I have failed again. The call to “man” did not even interest me, but rather “tr”.
@grail, I had not even noticed at first that your post is in response to mine. When I wrote e.g., I meant “for example”, but I see where I have failed again. The call to “man” did not even interest me, but rather “tr”.
This code can turn it into a quote-comma separated cvs file which excel can use. I copied the sample data from your post to test the code out. The Quote-Comma is a popular cvs style format.
Code:
cat file.txt | tr '|' ',' | sed 's/\([^,]*\)/"\1"/g'
**Note: Since you will be using the newly created file in a windows OS with excel, you may have to convert the file for windows to understand, It has something to do with cartridge returns.
Location: Northeastern Michigan, where Carhartt is a Designer Label
Distribution: Slackware 32- & 64-bit Stable
Posts: 3,541
Rep:
Pipe symbols, at least in many data base file import/export use, are common delimiters (not so much in Windows stuff). They are far more convenient than comma separated data, particularly if there are string data that include commas (the pipe symbol is not part of any language, thus it's unique which makes it handy).
Spreadsheet programs, at least the ones I know about, allow you to define the delimiter, be it a comma, tab or pipe. DBMS', such as MySQL/MariaDB, PostgreSQL are perfectly happy to export and import using pipes as delimiters.
One distinct advantage of using the pipe as a delimiter is that you do not have to enclose string fields in double quotes, just import and there you go.
If you really, really don't like the pipes, just substitute them with tabs (as long as some idiot didn't include tabs in the data, that is). VI, SED, whatever editor you like will do a global replacement for you -- just don't use any character that is in your data, for example one comma in one field will screw up the entire thing -- thus, the pipe.
Basically, just import the thing into a spreadsheet or load into a data base table; tell the spreadsheet or import/export utility that the delimiter is a pipe and there you go.
LinuxQuestions.org is looking for people interested in writing
Editorials, Articles, Reviews, and more. If you'd like to contribute
content, let us know.
