Sql-server – How to load this SAS text file to SQL Server 2005

importsql-server-2005

Every 3 months, I have to load several fixed-width text files into our SQL Server 2005 database. They can have anywhere from 5 to 798 columns (seriously) and the columns change pretty regularly.

Here's an example of the fixed-width data file:

010001    2000040620000406001200004060220000406
010001    2000040620001116002        0020000406
010006    2007051620070516001200706220120070516
010006    2010071520100908002        0020100715
010006    2010071520100715001201007300120100715
010007    1993110919931109001199311230219931109

With the text files, I also receive an "SAS" file that documents the columns and data types. Here's an example:

/************************************************************************
*           Job Number: 119164
*     Procedure Number: 6
*        Record Length: 47
*************************************************************************/

INPUT

         @1   PRVDR_NUM                         $CHAR10.    
        @11   SRVY_DT                           $CHAR8. /*YYYYMMDD*/
        @19   CYC_VISIT_DT                      $CHAR8. 
        @27   CYC_VISIT_NUM                     3.
        @30   POC_SGN_DT                        $CHAR8. 
        @38   LSC_BLDG_CNT                      2.
        @40   SRVY_CMPLTN_DT                    $CHAR8. 
;

***************************************************************;
***   SAS LABEL Statement Follows                              ;
***************************************************************;

LABEL

      PRVDR_NUM                         = 'CCN'
      SRVY_DT                           = 'Survey Date'
      CYC_VISIT_DT                      = 'Cycle Visit Date'
      CYC_VISIT_NUM                     = 'Cycle Visit Number'
      POC_SGN_DT                        = 'Administrator Signed POC Date'
      LSC_BLDG_CNT                      = 'Building Count'
      SRVY_CMPLTN_DT                    = 'Survey Completion Date'
;

The person I get this file from calls it "SAS Input Code"

Is there a solution for loading the fixed-width file that can also use the SAS file to create the table? My current solution is too elaborate, and I really hope there's an alternative.

Best Answer

I would write a python script that would,

  1. parse the columns descriptor file, get their widths and names
  2. iterate over all the data in the input file, splitting out the data into variables
  3. construct a sql query with these variables to the sql server with SQLAlchemy to be sent one by one or as a batch of rows or 1000 at a time

This script would take the columns descriptor file and the data file. The script would also have a database config file containing the database connection string or you can pass that as a third parameter.

I would write this for you, but I don't have time right now. This is a neat problem to solve. Python is perfect for this however. I wish my SAS problem was so easy.

Related Question