Flat File Export 1

[talibm]

I am trying to create a flat file for an electronic data conversion to a new software system. I am using crystal x and our database is ms sql server 2005. Each field has a specific start position, length and data type. They must be in order e.g., {case no}, {lname}, {fname}, {dob}, etc. The file can be fixed with or asterisk delimited.

I have created a file but I can't figure out how to include the field size and satrt position using DTS. For instance the filed size for {lname} is 25. How do I include the blank spaces for this field? I looked at thread 149-1454973 which is similar but I couldn't get it to work. Thanks for any help

tm

 

[hilfy]

There may be other ways to do this, but here's what I do:

1. For each field, create a formula to pad the spaces on to the data. It will look something like this:

if IsNull({table.lname}) then
replicate(' ', 25)
else
left({table.lname} + replicate(' ', 25), 25)

2. Suppress all sections of the report except the details.

3. Place a single text object in the details section and make it as wide as you can. Drag each of the field formulas into the text object, in the order you want your fields to appear.

4. Once all of your formulas are in the text object, righ-click and select Format. On the Common tab, turn off Can Grow. (NOTE: Can Grow will usually turn itself back on if you drag a field or formula into a text object, so it's best to wait until all of your data is in the object before turning it off.)

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[Madawc]

An alternative would be to export as comma-delimited. You could then change the commas to asterisks, if that was what was needed it.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10

 

[talibm]

Thanks hilfy, I tried your suggstion and it works. There are a couple of issues that I need to resolve. One is that my report is grouped by client id # becuse the file I am exporting is of clients who have received services within the previous year. If I place the text field in the detail section I get multiple records and I only need one. The other is the required format for the date field MMDDYYYY and filed size is 8. The current forment is MM/DD/YYYY. How can I change the format.

Madawc I'm don't fully understand your suggeation. If you could give me some details I will give it a try.

Thanks to both of you for your help.

tm

 

[hilfy]

In the formula for the date, use ToText to convert the date to the format that you need (format string is 'MMddyyyy' and it IS case sensitive!)

For the grouping issue, group by client, put all of the data in the group header section and suppress all other sections.

-Dell

A computer only does what you actually told it to do - not what you thought you told it to do.

 

[talibm]

Thanks hilfy, I forgot about ToText. I really appreciate your help

 

[Madawc]

When you select Export, you should see a list of alternatives. This ought to include 'Separated Values (CSV)'.

Madawc Williams (East Anglia, UK). Using Windows XP & Crystal 10