i found a great tool xls2csv
to convert MS excel sheets to csv files which I'm using to insert into a database.
I'm having a slight problem with one excel sheet which includes commas in the fields. Because of this, the CSV file is damaged and is not reliable to be inserted into a database. So the solution is to change the delimiter in xls2csv. According to man:
xls2csv - reads MS-Excel file and puts its content as comma-separated
data on standard output
xls2csv [-xlV] [-f format ] [-b string ] [-s charset ] [-d charset
] [-q number ] [-c char] files
xls2csv reads MS-Excel spreadsheet and dumps its content as comma-sepa‚
rated values to stdout. Numbers are printed without delimiters, strings
are enclosed in the double quotes. Double-quotes inside string are dou‚
-x print unknown Unicode chars as \xNNNN, rather than as question
-l list known charsets and exit successfully
-cchar cell separator char. By default - comma.
Manual page xls2csv(1) line 1
so i should be able to use the "-cchar" option to change the delimiter. in addition "-c" option is for the destination CSV file. Please look below:
xls2csv - Recode a spreadsheet's charset and save as CSV.
usage: xls2csv -x spreadsheet.xls [-w worksheet] [-b charset] [-c csvfile.csv] [-a charset] [-qshvW]
-x : filename of the source spreadsheet
-b : the character set the source spreadsheet is in (before)
-c : the filename to save the generated csv file as
-a : the character set the csv file should be converted to (after)
-q : quiet mode
-s : print a list of supported character sets
-h : this help message
-v : get version information
-W : list worksheets in the spreadsheet specified by -x
-w : specify the worksheet name to convert (defaults to the first worksheet)
example: xls2csv -x "spreadsheet.xls" -b WINDOWS-1252 -c "csvfile.csv" -a UTF-8
More detailed help is in "perldoc xls2csv"
Now here is the problem:
NG:~/downloads# xls2csv -x "test.xls" -w "Sheet1" -c "test.csv" -cchar "~"
Now reading "test.xls" as UTF-8.
Converting the "Sheet1" worksheet.
The spreadsheet has been converted to UTF-8 and saved as "char".
so it named the output char rather than test.csv .
I'm not sure what i can do next to make it work. is there is a way to change the default delimiter for xls2char rather trying the above?
Any input will be highly regarded.