TechIt · Windows

Export data from SQL server to a fixed record format flatfile

So, you want to run a query in sql server and get the output into a fixed record format flat-file / flatfile?

This solution involves some manual process and the use of excel (though other spreadsheet software may work as well).

There are solutions you can use on windows with command line utilities for SQL server like BCP and sqlcmd.exe, but I think they require padding with spaces and then trimming to a specified length, which you may well be fine with doing.

If you are looking to have a repeatable solution you may have more performance and less headache by using a programming language or scripting language to read the data and create the output in the desired format.

When i say flat-file i mean without character(s) reserved for delimiting like with commas in a .csv file, or tab or vbar(|) etc. as is often the case.

If you’re working with mainframes you may think of RECFM=FB, so LRECL is going to be the same for all records.

  1. Run your query in SQL server with “Results to grid” option selected
  2. Copy the output to excel
  3. Select a column, right click the column header, select “column width” and set to desired length
  4. Repeat for all cols
  5. select File -> Save as -> Formatted text (space delimited) (*.prn)
  6. save your file – this is your fixed record format flatfile!

That’s it, a little manual intervention with excel and you can easily save you data to a flatfile without delimiters.

I have found this very useful for generating once off reports.


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )


Connecting to %s