How to split string in excel file with bash script?-CodePudding

Good Afternoon

I am trying to develop a bash script which fetches data from a database and then fills an csv file with said data.

So far i have managed to just that but the way the data is presented is not good: all the data is written in one single cell like so:

and i would like for the data to be presented like this:

Here is my bash script code so far:

#! /bin/bash

currentDate=`date`
mysql -u root -p -D cms -e 'SELECT * from bill' > test_"${currentDate}".csv

Can anyone of you tell me what bash commands i can use to achieve the desired result?

Running the cat command of the file gives the following result:

thank you in advance

CodePudding user response：

Using sed, you can change the delimiter from the output displayed in your image (please use text in the future)

$ sed 's/ \ /,/g' test.csv

If happy with the output, you can then save the file in place.

$ sed -i 's/ \ /,/g' test.csv

You should now have the output in different cells when opened in excel

CodePudding user response：

Data appears to be tab-delimited (cat -T test.csv should show a ^I between each column); I believe excel's default behavior when opening a .csv file is to parse the file based on a comma delimiter.

To override this default behavior and have excel parse the file based on a different delimiter (tab in this case):

open a clean/new worksheet
(menu) DATA -> From Text (file browser should pop up)
select test.csv and hit Import (new pop up asks for details on how to parse)
make sure Delimited radio button is chosen (the default), hit Next >
make sure Tab checkbox is selected (the default), hit Next >
verify the format in the Data preview window (@ bottom of pop up) and if ok then hit 'Finish'

Alternatively, save the file as test.txt and upon opening the file with excel you should be prompted with the same pop ups asking for parsing details.

I'm not a big excel user so I'm not sure if there's a way to get excel to automatically parse your files based on tabs (a google/web search will likely provide more help at this point).