-3

I am having data in CSV file as follows in single column:

Input:

Company;C Desc;Region;R Desc;
H;Hindustan;13;Maharashtra;
I;Unilever;28;Dadra;

I would like to change the data Text to Columns with Delimited ';' as follows:

Output:

Company   C Desc    Region    R Desc
H         Hindustan 13        Maharashtra
I         Unilever  28        Dadra

How to write this scenario in shell script?

input fileOutput file

0

If you have the column utility (most free Unices has):

$ column -t -s ';' infile.csv >outfile.txt

$ cat outfile.txt
Column A
Company code  Company Desc  Region Code  Region Desc
H             Hindustan     13           Maharashtra
I             Unilever      28           Dadra

Then it's a matter of inserting the "Column B", "Column C" etc., but it was not clear from the question if these were part of the input/output or not.

EDIT: After the question was clarified:

Replace the ; in the CSV file by commas using tr, then re-import the file into Excel.

$ tr ';' ',' <infile.csv >outfile.csv

Excel should also be able to import your original data if you specify ; as column delimiters. I'm no Excel user though so I can't help with that.

  • thanks for quick response. I need to split the data in output file as shown in the images. Like text to columns feature in excel – ramachandra reddy Jun 28 '16 at 7:33
  • @ramachandrareddyAha! I will update my answer... (after thinking). – Kusalananda Jun 28 '16 at 7:36
  • Dear Kusalananda, Your command is working fine after modifying my CSV header. Thank you so much for your efforts. Very much appreciated your inputs – ramachandra reddy Jun 28 '16 at 13:35
  • @ramachandrareddy I'm happy you got it sorted. You may consider "accepting" the answer by clicking the tick under the up-vote/down-vote buttons here to the left. Thanks. – Kusalananda Jun 28 '16 at 14:20
  • Dear Kusalananda, thank you for your inputs. I am having numeric data also. Once it is saving into CSV all the formats are changing. Like amount (numbers) coming as "1777686.23-" instead of "-1777686.23" Please let me know how to get this minus sign as a prefix instead of suffix – ramachandra reddy Jun 29 '16 at 10:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.