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

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.

Correct, the rows data has to be converted to column data

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.

man tr ?

eg.

How do you ask for an example of using man??

tr command is working ..

@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.

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.

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.

**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.

SCNR ;-)
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. ;-)

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.

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.

The OP has posted further above:
And just before that post, I had already stated, that Gnumeric imports the pipe-delimited file directly. I do not know about LibreOffice but SoftMaker PlanMaker can do it, too.

But.

That is not the OP's problem.

The reason I chosen the quote-comma delimiter is because that was what I used to import a cvs file in ms excel back in the early 90's. Many cvs files were in that format at that time.

I believe the pipe delimiter can be used in a spreadsheet, so I tested it out.

I copied his original sample data from post 1 to a file called test.cvs with no modifications.

From libreoffice Calc, I opened the test.cvs and a text dialog box appears. From there you choose the options for dividing the data from the test.cvs file into columns by choosing the delimiter and text separator. Depending on the options you choose, you'll get to see a preview at the bottom on how the data will be divided in the columns.

Anyway, Long story short, it did work. So if libreoffice Calc can do it, MS excel can do it too.

OP just needs to know how to do it in MS Excel.

Yeah.. Thanks you .
The command provided worked.....