Problem solve Get help with specific problems with your technologies, process and projects.

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?

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

Dig Deeper on Oracle and SQL

Have a question for an expert?

Please add a title for your question

Get answers from a TechTarget expert on whatever's puzzling you.

You will be able to add details on the next page.

Start the conversation

Send me notifications when other members comment.

Please create a username to comment.