Results 1 to 4 of 4
  1. #1
    Join Date
    Nov 2003

    Question Unanswered: Importing Text File: How to dynamically change the row delimiter


    I have a dts package that imports a number of text files into a SQL Server 2000 database table. The package has been set up to accept a text file with a row delimiter of carriage return and line feed ({CR}{LF}). Some of the text files I receive only have a line feed ({LF}) as the row delimiter and the dts package fails the file. Is there an activex script i can use that will scan the file and change the row delimiter as required?

    i was going to use the filesystemobject which allows me to read a line at a time, however the Readline method doesn't read the new line character. The text files are too big to read into one variable and then do a replace.

    Any help would be appreciated

    I am using SQL Server 2000 SP3, Windows Server 2000 and Windows XP Professional. All systems are fully patched

    Regards Justin

  2. #2
    Join Date
    Nov 2003
    The solution is to identify and change the EOL character that is used in the file before the file is passed through the dts package. The scripting object doesn't appear to contain anything useful that will identify the EOL character so I have come up with this routine in VBA rather then VBScript. It will accept a user defined number of characters in a text file for and returns the EOL character (only a carriage return or line feed).

    Public Function gIdentifyEOLCharacter(strFileName As String, _
    lngNumOfCharactersToCheck As Long) As String
    ' identifies the end of line character
    Dim fsoSysObj As Scripting.FileSystemObject
    Dim tStream As Scripting.TextStream
    Dim strText As String, strEOLCharacter As String
    On Error GoTo ErrorHere
    Set fsoSysObj = New Scripting.FileSystemObject
    Set tStream = fsoSysObj.OpenTextFile(strFileName, ForReading)
    strText = tStream.Read(lngNumOfCharactersToCheck)
    If InStr(strText, Chr$(13)) Then _
    strEOLCharacter = strEOLCharacter & "{CR}"
    If InStr(strText, Chr$(10)) Then _
    strEOLCharacter = strEOLCharacter & "{LF}"
    gIdentifyEOLCharacter = strEOLCharacter
    Set fsoSysObj = Nothing
    Set tStream = Nothing
    Exit Function
    MsgBox "Error In: Module 'basSupportFunctions'" & vbCrLf _
    & "Procedure: gIdentifyEOLCharacter" & _
    vbCrLf & "Error Code: " & Err.Number & _
    vbCrLf & "Error: " & Err.Description, vbExclamation, "Error Alert"
    gIdentifyEOLCharacter = vbNullString
    Resume ExitHere
    End Function

    I can't believe that there is nothing simpler in the scripting object that can return the EOL character - i have looked through the object model and can't see anything that is useful

  3. #3
    Join Date
    Jul 2003
    San Antonio, TX
    I think you can try FINDSTR with /G:file parameter where you'd have nothing but CRLF in the file. If FINDSTR returns a file name then it means that the specified file contains normal EOL combination. Else, - it's not. You can also have 2 files, one with CRLF and the other with just LF. So that if the first one produced 0 results you can do FINDSTR against the second and be certain that LF is the actual delimiter.
    "The data in a record depends on the Key to the record, the Whole Key, and
    nothing but the Key, so help me Codd."

  4. #4
    Join Date
    Jul 2004
    This is just a wild guess but you might run a cmdshell and do:

    type inputfile.txt>newfile.txt

    before importing. Type may convert LF to CRLF.

    I also wonder if you could create a CR placeholder column with a default value of null and always specify LF as the line terminator.

    If these files are coming from an FTP transfer, set the type to Ascii instead of bin in FTP to get CRLF terminators.

Posting Permissions

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