0

Perhaps this was not asked correctly> This is my first post to stackoverflow.

I create a file that looks like this: and run it as a shell from terminal

#!/bin/sh
in2csv -f xls "A1.csv"  >  "export/A1.csv" |
in2csv -f xls "Allison am sch..XLS"  >  "export/Allison am sch..csv" |
in2csv -f xls "B & C Rmap am sch.XLS"  >  "export/B & C Rmap am sch.csv" |
in2csv -f xls "BOW K's am sch.XLS"  >  "export/BOW K's am sch.csv" |
cat *.csv > all.csv

Since no one tool does it. Can I build the text file from terminal that will create the shell file?

first ls > files.txt # this list all files which i want to add text in front and behind: in front in2csv -f xls " file names goes here behind " > "export/Allison am sch..csv" | once I have all this I can just simply run doing a sh command.

Or is there a better way. without having to use another program with lots of dependiencies to manage? Thanks I would like to know how to use unix (terminal) to get all files in a dir that are .xls convert to csv. I can do it but would like to know better way. My step is to 

ls > files.txt

then import the txt file into SAS where I add text to the files to use csvkit for the conversion: The SAS code looks like this, where VAR1 is the text of the name of the xls files.

data names2; 
set names;
example VAR1 = "Lakota Trade ctr am sch.XLS"

there are many files like this: Here is SAS code:

data file;
set file;
f = "in2csv -f xls " || '"'|| strip(VAR1) || '"'|| "  >  " || '"'||strip(tranwrd(VAR1, "XLS", "csv"))||'"';
drop var1;
run;

I then can copy each line into terminal and let csvkit:in2csv do the conversion. I do not want to use the native import of SAS for the conversion. I would like to know how to do something like this totally in the unix terminal or even Perl.

muraenok
  • 95
  • 1
  • 9
  • You can't do that, you will have to use a non-unix tool. – Robert Krzyzanowski Mar 26 '14 at 19:56
  • There are several methods described in this SO question: [Convert xlsx to csv in linux command line](http://stackoverflow.com/questions/10557360/convert-xlsx-to-csv-in-linux-command-line) – ThisSuitIsBlackNot Mar 26 '14 at 20:04
  • Seems like you just need a **for** loop in your script (the first file you mention) that will run your command for every file in the directory. It would need to parse out the trailing ".XLS" or ".csv" part of the file name. It's not clear that's what you want, so I won't try to answer. – BellevueBob Mar 26 '14 at 21:21

1 Answers1

0

check out the package gdata. This provides a function that uses perl, read.xls, that will convert .xlsx files to .csv. You can probably dig into the package to see how to do it from the command line instead of R.

stanekam
  • 3,906
  • 2
  • 22
  • 34