Flat File Connection CSV format

Question

Post reply

Flat File Connection CSV format

WangcChiKaBastar

SSCrazy

Points: 2297
More actions
March 29, 2010 at 3:13 am

#142210

I have to export a table from sql server to CSV file

Column names are Dialcode, Jurisdiction, VendorRank

VendorRank is a list of comma separated vendorid with rates

something like 0.00984(VCX4),0.01500(VCX5),0.05000(VCX11)

when I use flat file connection manager I am specifying format as Delimited

in the columns section : Row Delimiter as CR-LF and Column Delimiter as Comma [,] now when I run the package it is exporting the VendorRank as separate columns not like in single row with comma separated values

plz advise

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 245813 More actions · Answer 1

It's surely obvious to you why this is happening. You are using the comma for two purposes.

Please confirm what you would like the output file to look like.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.

WangcChiKaBastar SSCrazy Points: 2297 More actions · Answer 2

Desired output in the CSV file:

DialCode jurisdiction VendorRank

519706 interstate 0.00000(VCX27),0.00390(VCX1),0.00660(VCX4)

604846 interstate 0.00000(VCX27),0.00400(VCX1),0.01130(VCX4)

Currently the Vendor Rank comes up as with values in separate columns

DialCode jurisdiction VendorRank

519706 interstate 0.00000(VCX27) 0.00390(VCX1) 0.00660(VCX4)

604846 interstate 0.00000(VCX27) 0.00400(VCX1) 0.01130(VCX4)

This is incorrect it should use only one cell in CSV file and use comma to separate them

plz advise what I am doing wrong

Phil Parkin SSC Guru Points: 245813 More actions · Answer 3

OK - you're using a space as a field delimiter. A bit risky unless you are sure that your data cannot contain even a single space.

So you need to check your advanced settings for the CSV file and ensure that comma does not appear anywhere as a delimiter.

If you haven't even tried to resolve your issue, please don't expect the hard-working volunteers here to waste their time providing links to answers which you could easily have found yourself.