1

I am using ssconvert to convert xlsx to csv. One of the column has date field so when I convert it into csv date column value has converted into YYYY-MM-DD

Input:

Name Date
Test 05/08/2021

Command:

ssconvert test.xlsx  test.csv

Output:

Name,Date
Test,2021-08-05

Expected Output:

Name,Date
Test,05/08/2021
Ed Morton
  • 188,023
  • 17
  • 78
  • 185
user13000875
  • 387
  • 2
  • 14
  • in a comment to this [question re: different date format in ssconvert output](https://stackoverflow.com/q/51000284) the OP mentions switching from `raw` (`-O '... format=raw'`) to `preserved` (`-O '... format=preserved'`) output which in turn I'm assuming depends on the format applied to the (spreadsheet) cell – markp-fuso Sep 05 '21 at 18:25
  • Whatever you're doing with the data afterwards that makes you want `05/08/2021` instead of `2021-08-05` consider changing that to use `2021-08-05` instead since `05/08/2021` is ambiguous (could be 5th Aug or May 8th depending on what country you're in) and can't be sorted alphabetically. – Ed Morton Sep 05 '21 at 20:12
  • @markp-fuso format option will work only when output format is txt . in my case it is csv – user13000875 Sep 06 '21 at 05:37
  • @EdMorton I need the same data in converted csv format – user13000875 Sep 06 '21 at 05:38
  • @user13000875 yes, I understood that from your question. I'm suggesting that you change whatever you're using the CSV for next such that you **don't** need the data in that ambiguous/unsortable format and can use the better/standard YYYY-MM-DD date format instead. – Ed Morton Sep 06 '21 at 13:35
  • Please [edit] your question to state if your desired output date format is `DD/MM/YYYY` or `MM/DD/YYYY` as we can't tell given just 1 date of `05/08/2021` which format that is. – Ed Morton Sep 06 '21 at 13:45
  • MY input can be any format so is it possible to print value as it is like we do for string value – user13000875 Sep 06 '21 at 13:50
  • I think when you say "input format" you're referring to the format in your .xlsx file, i.e. the input to `ssconvert`, while when I say "input format" I'm referring to the input to the tool you're asking for help to create, i.e. the output of `ssconvert`, which is `YYYY-MM-DD` per your example, it's not "any format". – Ed Morton Sep 06 '21 at 14:10
  • @EdMorton yes input is my XLSX file and Output file is the file which I got after ssconvert. So my requirement is to get date in output file in same format which was in XLSX file – user13000875 Sep 06 '21 at 14:14
  • That is not at all clear from your question. It sounded like you were asking how to convert `YYYY-MM-DD` to either `DD/MM/YYYY` or `MM/DD/YYYY`, hence the answer you got. Please [edit] your question to state what it is you're actually asking for help with and provide more than 1 example of a date in your sample input/output. Also you should remove the awk and sed tags as there's nothing they can do with a .xlsx file, nor can they convert the output of `ssconvert` back to whatever it was originally as they don't know what that was. – Ed Morton Sep 06 '21 at 14:15
  • @EdMorton no I don't want to convert date value. It should be same in output file i.e, the output file which has been generated by ssconvert – user13000875 Sep 06 '21 at 14:17
  • Having said that, if you don't need to use `ssconvert` then see https://stackoverflow.com/q/38805123/1745001. – Ed Morton Sep 06 '21 at 14:19
  • XLSX file can have millions of record and I think ssconvert is the faster way to convert large xlsx file into csv – user13000875 Sep 07 '21 at 05:00

2 Answers2

1

Try to use sed:

sed -i -Ee 's#([0-9]{4})-([0-9]{2})-([0-9]{2})#\3/\2/\1#g' test.csv
Arnaud Valmary
  • 2,039
  • 9
  • 14
  • SSconvert has already converted files to .csv with format changes. – user13000875 Sep 05 '21 at 18:05
  • @user13000875, after convertion by SSconvert, apply the `sed` command to convert dates with your expected output – Arnaud Valmary Sep 05 '21 at 18:51
  • "It's not working" is the worst possible problem statement. Imagine calling your mechanic to fix your car or calling your doctor when you're sick and all you tell them is "it's not working". No-one can help you solve any problem given just "it's not working". When asking for help with a software issue you have to tell us in what way "it's not working" (e.g. wrong output, no output, error messages, core dumps, etc.) for us to be able to help you. – Ed Morton Sep 06 '21 at 12:50
  • @EdMorton I run above command but I can't see any changes in csv which has been generated from ssconvert. So the CSV generated from SSconvert and CSV generated from above command is same. No changes in date column value – user13000875 Sep 06 '21 at 13:39
  • The `-i` without an argument used in this answer will only work in GNU sed. If you're using BSD sed you need to change that to `-i''` or something to specify the backup file name even if it's null (read the man page or google if that's not exactly right). You must be using GNU or BSD sed for `-E` to work and for `-i` not to produce an error message. – Ed Morton Sep 06 '21 at 13:41
  • @EdMorton I need output as it is irrespective of my format so suppose input format is DD/MM/YYYY then it should be in csv file as DD/MM/YYYY and if input format is MM/DD/YYYY then in csv file it should be in MM/DD/YYYY – user13000875 Sep 06 '21 at 14:00
0

This should be working:

ssconvert --export-type=Gnumeric_stf:stf_assistant -O "format=preserve" test.xlsx test.csv
Adobe
  • 12,967
  • 10
  • 85
  • 126