Results 1 to 3 of 3
  1. #1
    Join Date
    Oct 2006
    Posts
    115

    Question Unanswered: [SQL 2005 Express] How do I load fixed width per row flat file? Bulk Insert possible?

    I can't use DTS nor DTSwizard as I need to put it in a .sql and run it through a command line via .bat file (it's more for the users).

    Each row ends with an EOL character, the fields are all fixed width, but I have a little problem here, some rows are empty but just with a EOL character.

    How shall I go about it?

    many thanks!

  2. #2
    Join Date
    Sep 2013
    Posts
    1
    This procedure will imort fixed length text files. You will need to create a format.fmt file that matches your text file (the instructions are in the comment section) and replace the @Define_Columns_SQL parameter with a select statement that matches your text file fields format:

    /****** Object: StoredProcedure [dbo].[usp_IMPORT_Formatted_TEXT_File_to_Table] Script Date: 9/18/2013 4:51:24 PM ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER ON
    GO


    CREATE Procedure [dbo].[usp_IMPORT_Formatted_TEXT_File_to_Table]
    (
    @Drive_and_Path Varchar(200),
    @FileName Varchar(100),
    @FormatFileName Varchar(100),
    @DatabaseName Varchar(50),
    @SchemaName Varchar(50),
    @TableName Varchar(50),
    @Define_Columns_SQL Varchar(8000),
    @RtnCode Bit Output
    )
    -----------------------------------------------------------------------------------------------------------------------
    --
    -- Purpose: This procedure allows you to import a formatted text file (must provide a format file with .fmt ext
    -- as an input) into a SQL Server Database (creates the table if it does not exists and drops and recreates
    -- the table if it does exist).
    --
    -- Modification Log:
    --
    -- 1. Steve Kirchner 07/18/2012 Created.
    --
    -- Sample Format file Text: format example line by line description is:
    --
    -- Line 1: 9.0 = version 9.0 (SQL Server 2005 or higher)
    -- Line 2: 1 = the column count, only 1 column in this example
    -- Line 3: the record description:
    -- 1 is the 1st field
    -- SQLCHAR is the data type
    -- 0 is the starting position of the 1st field
    -- 309 is the length of the 1st field
    -- "\r\n" is the field and in this case the row delimeter, other field delimeters include ',' and tab for field
    -- NonParsedData is the field name and will become the column name in the import table if not otherwised parsed with
    -- an alias by the @Define_Columns_SQL parameter
    -- SQL_Latin1_General_Cp437_BIN is the SQL Server Collation Type
    --
    -- If this file had multiple fields, it would have a record desription for each field like line 3 with appropriate offsets
    --
    --------------------------------------------------------------------- Format File text for a 1 field import - used to grab all the record data and parse later
    -- 9.0
    -- 1
    -- 1 SQLCHAR 0 309 "\r\n" 1 NonParsedData SQL_Latin1_General_Cp437_BIN
    -------------------------------------------------------------------------------------------------------------------------------------------------------------
    --
    --
    --
    -- Sample Exec
    /*
    Declare @iRtnCode Bit

    Exec usp_IMPORT_Formatted_TEXT_File_to_Table
    @Drive_and_Path = 'F:\SSIS',
    @FileName = 'DEX_nofilenamelikethis.txt',
    @FormatFileName = 'format.fmt',
    @DatabaseName = 'SCO_Processing',
    @SchemaName = 'dbo',
    @TableName = 'IMPORT_SCO_DEX_DATA',
    @Define_Columns_SQL = 'SELECT
    FileCode = Left(nonparseddata, 3),
    AgencyCode = Substring(nonparseddata, 4, 4),
    Filler1 = Substring(nonparseddata, 8, 9),
    ClmSchdNo = Substring(nonparseddata, 17, 8),
    Filler2 = Substring(nonparseddata, 25, 3),
    SchdType = Substring(nonparseddata, 28, 2),
    Fund = Substring(nonparseddata, 30, 4),
    Filler3 = Substring(nonparseddata, 34, 3),
    Agency = Substring(nonparseddata, 37, 4),
    FiscalYear = Substring(nonparseddata, 41, 4),
    RefItem = Substring(nonparseddata, 45, 7),
    Filler4 = Substring(nonparseddata, 52, 31),
    IssueDate = Substring(nonparseddata, 83, 8),
    WarNoPrntNo = Substring(nonparseddata, 91, 8),
    Filler5 = Substring(nonparseddata, 99, 6),
    WarAmtPayAmt = Substring(nonparseddata, 105, 13),
    PayeeID = Substring(nonparseddata, 118, 10),
    PayeeName = Substring(nonparseddata, 128, 30),
    Filler6 = Substring(nonparseddata, 158, 5),
    Addrline1 = Substring(nonparseddata, 163, 30),
    Addrline2 = Substring(nonparseddata, 193, 30),
    Addrline3 = Substring(nonparseddata, 223, 30),
    Addrline4 = Substring(nonparseddata, 253, 30),
    AEcode = Substring(nonparseddata, 283, 1),
    ClaimNo = Substring(nonparseddata, 284, 2),
    ZipCode = Substring(nonparseddata, 286, 9),
    OptID = Substring(nonparseddata, 295, 6),
    WholeRecord = left(nonparseddata, 309) ',
    @RtnCode = @iRtnCode Output

    select @iRtnCode

    */
    --
    --
    -----------------------------------------------------------------------------------------------------------------------
    AS

    Declare @SQL Varchar(Max)
    Declare @found int
    Declare @FilePath varchar(300)

    select @FilePath = @Drive_and_Path + '\' + @FileName

    declare @tblFile table
    (FileExists bit, FileIsaDirectory bit, ParentDirectoryExists bit)

    insert into @tblFile
    EXEC master.dbo.xp_fileexist @FilePath

    select @found = FileExists
    from @tblFile

    IF @found = 1
    Begin
    Set @SQL = 'If Exists(Select 1 from [' + @DatabaseName + '].[INFORMATION_SCHEMA].[TABLES] WHERE [TABLE_NAME] = ''' + @TableName + '''AND [TABLE_SCHEMA] =''' + @SchemaName + ''') DROP TABLE [' + @DatabaseName + '].['+ @SchemaName + '].[' + @TableName +']'

    print @SQL
    exec (@SQL)

    Set @SQL = @Define_Columns_SQL
    +
    'INTO [' + @DatabaseName + '].[' + @SchemaName + '].[' + @TableName + ']
    FROM OPENROWSET(BULK N''' + @Drive_and_Path + '\' + @FileName + ''', FORMATFILE = ''' + @Drive_and_Path + '\Format\' + @FormatFileName + ''') AS a;'

    print @SQL
    exec (@SQL)

    If @@ERROR = 0

    Set @RtnCode = 1
    Else
    Set @RtnCode = 0
    End
    Else
    Set @RtnCode = 0

    -----------------------------------------------------------------------------------------------------------------------
    -- End of Procedure usp_IMPORT_Formatted_TEXT_File_to_Table
    -----------------------------------------------------------------------------------------------------------------------



    GO

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    BCP: bcp Utility
    You may need to use a format file.
    George
    Home | Blog

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •