Thursday, December 14, 2017

file

  • Using Notepad++ to change end of line characters (CRLF to LF)

    End of Line characters include CR or LF.  Windows uses both CRLF at the end of a line, whereas Unix uses only a LF.

    • CR = Carriage Return
    • LF = Line Feed

    Recently, while troubleshooting why data wont import successfully as part of an automated process, I was pulling a subset of data out of the main text file, but the end of line markers weren't correct.  I copied several lines using Notepad ++ and it automatically used CRLF markers.  The automated process expected the end of line markers to be LF to be read by the SSIS package properly.

    This article will help job my memory when I run across this again... but hopefully it helps someone else too! :)

    First off, within Notepad ++ to see the end of line markers, you need to indicate you want to see them.  Click on View > Show Symbol > then either Show End of Line, or Show All Characters if you want to see spaces and tabs, sometimes the second option is easier).

  • Who has the #@!*$% file open?

    I just found out about a nifty tool and thought I would share.

    Scenario: The nightly ETL uses files as source data to insert into the data warehouse. The ETL failed because the file was locked and not accessible. The file is on a server in a shared folder. I needed to find out who had the file open, although I had a suspicion it was sql agent, not an actual person. Either way, the file needed to be closed, or the connection terminated.

    Answer: openfiles.exe

    I had never heard of this utility, but it works wonders. It was able to tell me who had the file open and allow me to disconnect the user's session with that file. In my case, the ETL was able to run successfully again. :) I did find out afterwards that our IT department performed maintenance over the weekend which must have caused the connection to 2 of these files to be "hung up".

    The link above shows all the options available, but here is a summary also.

    You can access the following information on the local server/workstation, or from remote. If you are remotely querying/disconnecting, then use the following switch:

    • /s ComputerName - you can use the name or IP of the remote computer.

    You can perform all of these functions as yourself, or specify a specific user. You can use the following switches to indicate a specific user:

    • /u - specify the username to run the task as, format is domain\user
    • /p - enter the password for the username entered

    List of files open and by whom
    /query

    • /fo {TABLE | LIST | CSV} - this alows you to specify the format of the results
    • /nh - this allows you to hide the headers in the results, but this switch can only be used when the /fo tag indicates a TABLE or CSV formatted resultset
    • /v - this stands for verbose and shows the verbose task information

    Terminate the connection
    /disconnect

    • /id ### - disconnects the file specified; use /query to determine the id of each file
    • /a username - disconnects all files opened by the specific username
    • /o mode - disconnects all open files that are in the specified mode (values expect either Read/Write or Read)
    • /se sessionname - disconnects all open files in the specified session
    • /op filename - disconnects the specific file by file name

    Note: I tried to disconnect the files using the ID specified from the /query switch. However, it didnt disconnect as I expected. When I tried the /o Read/Write, it did work as expected and closed all files.  Also, when looking at files on a software share, you can use the ip address.

    Hope this helps someone - I am sure I will be referring back to my own blog for this one a few times!

  • Working with current and previous rows in text files

    An issue came up today where the vendor was sending a text file, but the text for some of the lines was on multiple lines, rather than a single line.  After researching, I found out that I needed to compare a line of text and based on certain criteria append it to the prior line.  I couldn't just strip certain end of line markers because it was mixed.

    I am using a dummy test file in this example, but here is how everything played out:

Archives

Powered by mod LCA