CODEWITHSUNDEEP
csvsap-asebcpexport-to-csv
Nitrodist
Nitrodist

Reputation: 1745

Sybase ASE 12.0 CSV Table Export

What I'm trying to do is export a view/table from Sybase ASE 12.0 into a CSV file, but I'm having a lot of difficulty in it.

We want to import it into IDEA or MS-Access. The way that these programs operate is with the text-field encapsulation character and a field separator character, along with new lines being the record separator (without being able to modify this).

Well, using bcp to export it is ultimately fruitless with its built in options. It doesn't allow you to define a text field encapsulation character (as far as I can tell). So we tried to create another view that reads from the other view/table that concatenates the fields that have new lines in them (text fields), however, you may not do that without losing some of the precision because it forces the field into a varchar of 8000 characters/bytes, of which our max field used is 16000 (so there's definitely some truncation).

So, we decided to create columns in the new view that had the text field delimiters. However, that put our column count for the view at 320 -- 70 more than the 250 column limit in ASE 12.0.

bcp can only work on existing tables and views, so what can we do to export this data? We're pretty much open to anything.

Upvotes: 1

Views: 2902

Answers (3)

masterziv
masterziv

Reputation: 91

You can use BCP format files for this.

bcp .... -f XXXX.fmt

BCP can also produce this format files interactively if you don't state any of -c -n -f flags. Then you can save the format file and experiment with it, editing it and runnign BCP. To safe time while exporting and debugging, use -F -L flags like "-F 1 -L 10" -- this gets only first 10 lines.

Upvotes: 0

abe crabbers
abe crabbers

Reputation: 31

That sounds like bcp's right, but process the output via awk or perl. But are those things you have and know? That might be a little overhead for you. If you're on Windows you can get Active Perl free and it could be quick.

something like:

perl -F, -lane 'print "\"$F[0]\",$F[1],\"$F[2]\",$F[3]\n" ;' bcp-output-file

how's that? $F is an array of fields. The text ones you encircle with \"

Upvotes: 0

Karl
Karl

Reputation: 3372

If its only the new line char that is causing problems can you not just do a replace

create new view as
select field1, field2, replace(text_field_with_char, 'new line char,' ' ')
from old_view

You may have to consider exporting as 2 files, importing into your target as 2 tables and then combining them again in the target. If both files have a primary key this is simple.

Upvotes: 1

Related Questions