Export from SQL Server to Fixed Width format

I know, it’s 2022, why would you want to do this? Recently I had a requirement to export a very large SQL Server table to fixed width format (FWF) for a legacy application. For the uninitiated FWF files are those where the format is specified by column widths, pad character and left/right alignment. The advantages of this format is their low space consumption but the trade-off being they can be very tedious to export or import. This StackOverFlow discussion is interesting to gauge the continued popularity of FWF.

Drivers to import and export FWF files are few and far between. The only exception being the Pandas read.fwf method in Python which can help import these files. This is the jumping off point for this article, knowing there was a Pandas read.fwf function I went looking for documentation on the write.fwf? And there isn’t one! So below are steps I followed to export from SQL Server to FWF. All code and files are available on my Github if you want to use it.

1. First thing is to garner what the layout of the FWF file should be? Hopefully this is available to you. In this example, purely illustrative, I am going to export the SQL Server System view that provides the list of tables in a database. This view has 4 columns so I will need 4 fixed widths in the file. They are all text and again for this example I am going to make them character length 20. Based on this I need to create a Schema.ini file first.
2. Create a Schema.ini file to represent the layout of the final FWF file. Below is what is required in this example. Follow this format.

Schema.ini

3. Next move over to SQL Server and for the table you want to export, select into a new table in the format and column layout as per the Schema.ini. So in this example I use this SQL to do this:

DROP TABLE IF EXISTS [TEMPDB].[DBO].[FWF_FORMAT];
SELECT [TABLE_CATALOG] = COALESCE(CAST(TABLE_CATALOG AS CHAR(20)), SPACE(20))
,[TABLE_SCHEMA] = COALESCE(CAST(TABLE_SCHEMA AS CHAR(20)), SPACE(20))
,[TABLE_NAME] = COALESCE(CAST(TABLE_NAME AS CHAR(20)), SPACE(20))
,[TABLE_TYPE] = COALESCE(CAST(TABLE_TYPE AS CHAR(20)), SPACE(20))
INTO [TEMPDB].[DBO].[FWF_FORMAT]
FROM [TEMPDB].[INFORMATION_SCHEMA].[TABLES];

4. So this is the table that will be exported from. Now in Python we need some code to read the schema.ini file, create a format object, connect to SQL Server and then dump the data into a FWF file. Sounds easy right? Well the Numpy “savetxt” function does a lot of the heavy lifting for us.

5. Read in the Schema.ini file

6. Connect to SQL Server using pyodbc

7. Finally dump the data from a dataframe using numpy:

Connect to SQL Server and export to FWF file

And that is it! It can take a little bit of time to write the SQL query to copy the data into a separate table but as long as the layout is clearly defined this should be very straightforward.

References:

https://numpy.org/devdocs/reference/generated/numpy.savetxt.html

https://docs.python.org/3/library/string.html#format-specification-mini-language

https://stackoverflow.com/questions/64826735/pandas-write-dataframe-in-fixed-width-formatted-lines-to-a-file