SQL for a fixed width flat file

SQL for a fixed width flat file

Is there a way to create a fixed width flat file from a SQL query using the SQL language? If so, how?

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchOracle.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchOracle.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Yes and no. No, not with just SQL, because an SQL query just produces a result set, which is actually a table, which it then "hands back" to whatever interface or program or API you submitted the query through -- Query Analyzer, PHPMyAdmin, Toad, Access, etc.

The first thing to check is whether your database has a utility for this purpose. For example, SQL Server has DTS (Data Transformation Services), which can use an existing SQL query to produce a text file.

If your database has nothing like this, or if you do not have access to it, you can still "fake it" if you can highlight and copy the query result set's rows and columns.

For example, in Access, after you run a query, you will be in datasheet view; just right-click the top left corner, and select Copy. Now you can paste the result set as rows and columns into Excel, and then get Excel to save the worksheet as a Formatted Text (Space delimited) *.prn file.

Even if all you have is a text editor, you can still do it. Create a "pre-formatted" result set which consists essentially of just one long column. For example, consider this query:

 select smallintcol , varchar30col , datetimecol , nullablechar1col from yourtable

Rewrite it like this (SQL Server syntax):

 select right('00000'+cast(smallintcol as varchar(5)),5) + left(varchar30col+space(30),30) + convert(char(10),datetimecol,120) + cast(coalesce(nullablechar1col,' ') as char(1)) from yourtable

The result set of this query will have only one columm, which you can then paste into a plain text editor and save as a flat file, without worrying about variable-length fields or tabs between them.

 

For More Information


This was first published in August 2003

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.
    Back to top