-1

I want to run a line of code to create a Text to columns output on a file which is comma delimited.

I have created a file which results in the output been created in the format comma delimited. So all the data is in column A separated by a ,. The file is apilist.xls I want to add a line of code that will take the data here and separate out the data in to the columns, similar to the excel command text to columns.

I have tried: 

$ cat apilist.xls | tr "\\t" "," > apilist.csv

This gives the message:

bash: $: command not found

It creates the apilist.csv file but there is no data in this.

Any suggestions on how to progress this? 

$ cat apilist.xls | tr "\\t" "," > apilist.csv

Delimited output - move the data from one column to be separated into multiple columns.

avermaet
  • 1,543
  • 12
  • 33
Stephen
  • 47
  • 1
  • 11
  • if your input file is already comma separated, why you `tr "\t" ","`? which role `grep` is gonna play in your question? also, paste some sample data as input, and expected output from the input. – Kent Jul 17 '19 at 12:02
  • 1
    It seems that you run the following command `$` instead of `cat ...` – Zelnes Jul 17 '19 at 12:04
  • 1
    `$` is a generic shell prompt, not a command you'd actually type... If you see an answer with something like `$ foo`, it means `foo` is being run in an interactive shell session. – Shawn Jul 17 '19 at 12:06
  • Input - Illiquid Flag,Exception Sub Type,Primary ID,ID_GLOBAL,ID_SEDOL1,ID_UNIQUE,SECURITY_TYP,FIGI LAST_UPDATE_DT,FIGI PX_LAST,,SEDOL LAST_UPDATE_DT,SEDOL PX_LAST,,UNIQUE LAST_UPDATE_DT,UNIQUE PX_LAST,,PRICING_SOURCE,CRNCY,QUOTED_CRNCY,FIGI SEDOL PRICE CHECK,FIGI BBU PRICE CHECK,SEDOL BBU PRICE CHECK,,PrimaryIdentifier,SUBSCRIBER,PRICE_DATE,SERVICE_PRICE,PRICING_SOURCE,PRICE_CRNCY in column A – Stephen Jul 17 '19 at 12:29
  • Illiquid Flag Exception Sub Type Primary ID ID_GLOBAL ID_SEDOL1 ID_UNIQUE SECURITY_TYP FIGI LAST_UPDATE_DT FIGI PX_LAST SEDOL LAST_UPDATE_DT SEDOL PX_LAST UNIQUE LAST_UPDATE_DT UNIQUE PX_LAST PRICING_SOURCE CRNCY QUOTED_CRNCY FIGI SEDOL PRICE CHECK FIGI BBU PRICE CHECK SEDOL BBU PRICE CHECK PrimaryIdentifier CL_SUBSCRIBER PRICE_DATE SERVICE_PRICE PRICING_SOURCE PRICE_CRNCY - output delimited by the comma – Stephen Jul 17 '19 at 12:29

1 Answers1

0

You would need to convert your .xls file to .csv before you're able to work on it. Here are a number of methods to convert the file: https://linoxide.com/linux-how-to/methods-convert-xlsx-format-files-csv-linux-cli/

You could then use awk to create columns between the data where it is separated by a comma. This is assuming that you have three columns separated by a comma - you would need to edit the below command to meet your needs:

awk -F ',' '{printf("%s %s %s\n", $1, $2, $3)}' input_file
am401
  • 34
  • 4
  • When I actually changed my input file from .xls to .csv the delimited action happened automatically, I didnt require nay further code to initiate this. I still need to test further but for now I think this is closed/answered. Thanks to all for their input. – Stephen Jul 17 '19 at 15:03
  • The CLI can read the .csv without any further applications as opposed to the .xls file. Sounds good that it is now working. – am401 Jul 17 '19 at 15:10