convert bcp file to standard csv file

Question

I am looking for any known tools or scripts which can convert my bcp files to csv files.

Input bcp file format:

Output format I want:

Standard csv format file with comma delimited
Field values should contain original content including special characters (I mean no addition or deletion of special character(s), CR should also not be deleted)
The file able to cut by column index/name to select interested columns.

For this I did the following:

Transformed the bcp file to csv with few sed commands, with this I can open the file in MS excel program with proper alignment, and I could see content was not altered (as expected).

sed -i 's/\"/\"\"/g' $inFile

sed -i 's/XXXXXXX/","/g' $inFile

sed -i 's/YYYYYYY/"\n"/g' $inFile

sed -i '1s/^/\"/' $inFile

sed -i '$s/\"//' $inFile

sed -i -e '${/^$/d}' $inFile

sed -i '1s/^/"Header","added","here"\n/' $inFile
Tried csvkit tool: csvcut $infile This tool is selecting the preferred columns but modifying the content like deleting the CR.

Any ideas in this kind conversion?

Open as CSV but with different delimiters. – Ignacio Vazquez-Abrams May 03 '16 at 20:20 — Ignacio Vazquez-Abrams, May 03 '16 at 20:20
An example of your input data, would help, pleasd – Borodin May 04 '16 at 02:08 — Borodin, May 04 '16 at 02:08

score -1 · Answer 1 · answered May 03 '16 at 21:04

awk to the rescue!

awk -F='XXXXXXX' -v RS='YYYYYYY' -v OFS='","' -v ORS='\n' 
                  '{gsub(/"/,"\""); $1=$1; print "\""$0"\""}' file

escape quotes, quote fields, change field delimiter to comma and record delimiter to newline. $1=$1 forces awk to apply the new delimiters.

1 Answers1