Flat file format

I have a table that I want to export to a flat file. The problem I am running into is, the person I am sending it to needs it in a specific format. It is a comma serperated file and I need quotes on some of the data but not all.

For example : "1234",abc,"id"

I know how to make it all or none but not conditionally. Also I have some fields that the total in them is 0.00 and when this gets exported to the file the format become .00, is there a way to make it 0 with out changing the ones that have totals. Thanks.

95five0 wrote:

I have a table that I want to export to a flat file. The problem I am running into is, the person I am sending it to needs it in a specific format. It is a comma serperated file and I need quotes on some of the data but not all.

For example : "1234",abc,"id"

I know how to make it all or none but not conditionally. Also I have some fields that the total in them is 0.00 and when this gets exported to the file the format become .00, is there a way to make it 0 with out changing the ones that have totals. Thanks.

Well, you may just have to assemble the quotes yourself using a derived column transformation just upstream from the flat file destination. Basically, for those fields, concatenate the double quotes to your data.

For your other question, I think you'll need to ensure that the output datatype for 0.00 is of string, so that it doesn't remove the leading zero.|||The only way to do what you describe, would be to create varchars for each field and convert them to the exact format you want, then export the varchars.

The default for comma delimited with string quotes, is "strings" get quoted, but other fields do not. So in your case the ,abc, would result in ,"abc",.

You might see if they really need it in that exact format. Most import programs will work with slightly different input, like the quotes or .00 as a number.|||Thanks for the advise. Basically what I did was create a temp table and essemble each line into one varchar field. Then I just pushed the data from the temp table to the file.|||

95five0 wrote:

Thanks for the advise. Basically what I did was create a temp table and essemble each line into one varchar field. Then I just pushed the data from the temp table to the file.

Yet another way to make it work. Good deal.