Return to Question

edited tags

Link

edited Jan 27, 2015 at 18:28

Colin 't Hart

9.4k
15
36
44

sql-server-2005 import

Source Link

asked Jan 27, 2015 at 18:24

Daniel

How do I load this SAS text file to SQL 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.

import