Site Search:
Sign in | Join | Help
4Penny.net

Steve Gray

Random Thoughts from the 4Penny VP of Development

Exporting SQL Data to a fixed width file

From a recent email: 

Our client needs to extract data from a variety of Dynamics GP payroll tables into a couple of files to send to their new 401 plan administrator.  The required files need to be in a fixed-width file format.  I have been thinking about possible tools that could do this job, such as SmartLists, SmartLists Builder, GP Report Writer, or perhaps a SQL view.

Your thoughts?

 

I’ve done this in the past several ways. Crystal would be the easiest – create one field that that has all the data, powered by a stored procedure. Then export the report to a text file.

 Another possibility would be just to export the data directly from a stored procedure, either via cut and paste from the query window or by running a task that will create a text file; but those options are more techincal. If you create a Crystal report, any end user can do it.

 

Published Sep 05 2007, 12:12 PM by Steve Gray

Comments

 

Mark Polino said:

I agree that Crystal is the easiest solution for this, but I have done it in SQL as well. Some other apps have a limited only SQL window that works well for power users. They simply run the stored proc and save the results as a text file.

I find the extracts that require fixed length fields to be more of a pain. Something like a 12 digit amount field where the last two digits are always the cents and there has to be leading zeros in front to fill in all twelve digits. It's not hard, just a pain!

The last one I did looked like this for one field:

Right('000000000' + cast(cast(Sum(Amount_Current)as integer)as varchar),9) as Amount_Current

Mark

September 5, 2007 1:59 PM

Leave a Comment

(required)  
(optional)
(required)  
Add

About Steve Gray

Steve is a seasoned (translate: old) developer in VB and ASP.NET. He spends most of his time in Dynamics GP, writing custom mods for consulting firms. Crystal reports, eConnect, VS Tools for Dynamics... anything that comes along.