LinuxQuestions.org
Support LQ: Use code LQ3 and save $3 on Domain Registration
Go Back   LinuxQuestions.org > Forums > Linux Forums > Linux - Newbie
User Name
Password
Linux - Newbie This 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


Reply
  Search this Thread
Old 03-17-2016, 03:19 AM   #1
Vthimmap
Member
 
Registered: Oct 2012
Posts: 34

Rep: Reputation: Disabled
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

sample file:
A0|A|Database|1|0.0%|3||0|0.00%|0|0.00%|0|0.00%|0|0.00%|0|0.00%|0|0.00%|0|0.00%|0|0.00%|0|0.00%|0|0. 00%|0|0.00%|0|0.00%|0|0.00%|0|0.00
 
Old 03-17-2016, 04:33 AM   #2
Michael Uplawski
Member
 
Registered: Dec 2015
Distribution: Debian stretch/sid
Posts: 436
Blog Entries: 9

Rep: Reputation: 275Reputation: 275Reputation: 275
Quote:
Originally Posted by Vthimmap View Post
Note: each row data separated by delimiter | has to be converted to columns

sample file:
A0|A|Database|1|0.0%|3||0|0.00%|0|0.00%|0|0.00%|0|0.00%|0|0.00%|0|0.00%|0|0.00%|0|0.00%|0|0.00%|0|0. 00%|0|0.00%|0|0.00%|0|0.00%|0|0.00
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 04:34 AM.
 
Old 03-17-2016, 04:36 AM   #3
Vthimmap
Member
 
Registered: Oct 2012
Posts: 34

Original Poster
Rep: Reputation: Disabled
Correct, the rows data has to be converted to column data
 
Old 03-17-2016, 07:52 AM   #4
Turbocapitalist
Senior Member
 
Registered: Apr 2005
Distribution: Ubuntu, Devuan, OpenBSD
Posts: 1,369
Blog Entries: 3

Rep: Reputation: 565Reputation: 565Reputation: 565Reputation: 565Reputation: 565Reputation: 565
LibreOffice Calc

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.
 
1 members found this post helpful.
Old 03-17-2016, 08:09 AM   #5
schneidz
LQ Guru
 
Registered: May 2005
Location: boston, usa
Distribution: fc-15/ fc-20-live-usb/ aix
Posts: 5,051

Rep: Reputation: 852Reputation: 852Reputation: 852Reputation: 852Reputation: 852Reputation: 852Reputation: 852
man tr ?
 
Old 03-17-2016, 10:40 AM   #6
Michael Uplawski
Member
 
Registered: Dec 2015
Distribution: Debian stretch/sid
Posts: 436
Blog Entries: 9

Rep: Reputation: 275Reputation: 275Reputation: 275
Quote:
Originally Posted by schneidz View Post
man tr ?
eg.
 
Old 03-17-2016, 10:48 AM   #7
schneidz
LQ Guru
 
Registered: May 2005
Location: boston, usa
Distribution: fc-15/ fc-20-live-usb/ aix
Posts: 5,051

Rep: Reputation: 852Reputation: 852Reputation: 852Reputation: 852Reputation: 852Reputation: 852Reputation: 852
Code:
cat vthimmap.csv | tr '|' '\n'
 
Old 03-17-2016, 01:08 PM   #8
grail
LQ Guru
 
Registered: Sep 2009
Location: Perth
Distribution: Manjaro
Posts: 9,422

Rep: Reputation: 2815Reputation: 2815Reputation: 2815Reputation: 2815Reputation: 2815Reputation: 2815Reputation: 2815Reputation: 2815Reputation: 2815Reputation: 2815Reputation: 2815
How do you ask for an example of using man??
 
1 members found this post helpful.
Old 03-18-2016, 03:16 AM   #9
Vthimmap
Member
 
Registered: Oct 2012
Posts: 34

Original Poster
Rep: Reputation: Disabled
tr command is working ..
 
Old 03-19-2016, 12:49 PM   #10
Michael Uplawski
Member
 
Registered: Dec 2015
Distribution: Debian stretch/sid
Posts: 436
Blog Entries: 9

Rep: Reputation: 275Reputation: 275Reputation: 275
Quote:
Originally Posted by grail View Post
How do you ask for an example of using man??
@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”.
 
Old 03-19-2016, 05:13 PM   #11
schneidz
LQ Guru
 
Registered: May 2005
Location: boston, usa
Distribution: fc-15/ fc-20-live-usb/ aix
Posts: 5,051

Rep: Reputation: 852Reputation: 852Reputation: 852Reputation: 852Reputation: 852Reputation: 852Reputation: 852
Quote:
Originally Posted by Michael Uplawski View Post
@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 was probably sarcasticly saying rtfm.
 
Old 03-19-2016, 07:35 PM   #12
WayneB
LQ Newbie
 
Registered: Mar 2016
Posts: 25

Rep: Reputation: Disabled
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'
the output will look like this

"A0","A","Database","1","0.0%","3","","0","0.00%","0","0.00%","0","0.00%","0","0.00%","0","0.00%","0 ","0.00%","0","0.00%","0","0.00%","0","0.00%","0","0. 00%","0","0.00%","0","0.00%","0","0.00%","0","0.00 "

If this is what you want, run the command again and redirect the output to a new file name.

Code:
cat file.txt | tr '|' ',' | sed 's/\([^,]*\)/"\1"/g' > newfile.cvs
**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.

Use unix2dos to convert it if necessary.

Last edited by WayneB; 03-19-2016 at 08:41 PM.
 
Old 03-20-2016, 08:41 AM   #13
Michael Uplawski
Member
 
Registered: Dec 2015
Distribution: Debian stretch/sid
Posts: 436
Blog Entries: 9

Rep: Reputation: 275Reputation: 275Reputation: 275
SCNR ;-)
Quote:
Originally Posted by WayneB View Post
It has something to do with cartridge returns.
Always return your empty cartridges for recycling.

An alternative to unix2dos is open the CSV file directly in Gnumeric and save it in MS-Excel format. ;-)
 
1 members found this post helpful.
Old 03-20-2016, 09:49 AM   #14
cnamejj
Member
 
Registered: Mar 2015
Distribution: Ubuntu
Posts: 37

Rep: Reputation: Disabled
I've almost posted here and resisted, but this thread keeps updating, so I can't resist any longer...

You know you CAN just import the files specifying "|" as the delimiter character. That way you don't have to do anything at all.
 
Old 03-20-2016, 11:13 AM   #15
tronayne
Senior Member
 
Registered: Oct 2003
Location: Northeastern Michigan, where Carhartt is a Designer Label
Distribution: Slackware 32- & 64-bit Stable
Posts: 3,516

Rep: Reputation: 1039Reputation: 1039Reputation: 1039Reputation: 1039Reputation: 1039Reputation: 1039Reputation: 1039Reputation: 1039
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.

Hope this helps some.
 
  


Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off



Similar Threads
Thread Thread Starter Forum Replies Last Post
[SOLVED] How to extract an entire row of one file based on a matching column in a CSV file arbex5 Programming 13 07-27-2015 02:54 PM
retrieve data column wise from csv files srinureddy Linux - Newbie 16 11-01-2014 07:54 AM
Help needed - How to check quality of a specific column in a tab-delimited file? Jason7449 Linux - Newbie 3 03-08-2010 10:36 AM
Bash - Reading csv delimited file to array and for further manipulation BLWEGRZYN Programming 1 01-06-2010 10:38 PM
Culling Data from a CSV file to output in excel jterr02 Programming 2 05-19-2006 05:58 AM


All times are GMT -5. The time now is 01:11 PM.

Main Menu
Advertisement
My LQ
Write for LQ
LinuxQuestions.org is looking for people interested in writing Editorials, Articles, Reviews, and more. If you'd like to contribute content, let us know.
Main Menu
Syndicate
RSS1  Latest Threads
RSS1  LQ News
Twitter: @linuxquestions
Facebook: linuxquestions Google+: linuxquestions
Open Source Consulting | Domain Registration