How to change the ragged right CR/LF position in .txt file (ie using UltraEdit) before importing in SQL Server?

Question

I was provided a ragged right UNIX text file with the CR/LF position of most lines appears to be set at 100. I was provided a column layout document that takes the file out to position 150. Is there a way to change the CR/LF position to 150? If I import into SQL Server using the format provided, since the linefeed is at 100, SQL Server grabs the first 50 characters of the next line.

Answer 1

I can offer 2 solutions.

Using column mode

1. Move caret to column 151 according to status bar in first line of file.
   There are now 150 characters left of caret position.
2. Click in menu Column on menu item Column Mode to turn on this mode.
3. Click in menu Column on menu item Insert/Fill Columns, enter a single character like # and execute this command.
4. You see now character # on all lines inserted at column 151.
   Click in menu Column on menu item Delete Columns, enter 1 and execute the command.
5. Click in menu Column on menu item Column Mode to turn off this mode.

Now all lines in file have 150 characters. You might want to go to end of file using Ctrl+End and delete the 150 spaces in last line if this line does not contain anything else.

You can enable Show Line Endings in menu View to check the lines visually.

This solution is often the easiest method for small files.

Using Perl regular expression replaces

1. Insert at top of your file 150 spaces, select the 150 spaces using Ctrl+Shift+Home and cut them with Ctrl+X to the clipboard.
2. Run a Perl regular expression Replace All from top of file with $ as search string (means end of line) and paste with Ctrl+V the 150 spaces into the replace edit field replacing everything already existing in this edit field.
   Now all lines have 150 or more characters.
3. Run one more Perl regular expression Replace All from top of file with ^(.{150}).*$ as search string and \1 as replace string.

The second Perl regular expression using a marking group for the first 150 characters of each line back referenced with \1 to keep them removes all other inserted spaces from each line from column 151 to end of line.

This solution is better for very large files and of course works also with other editors supporting Perl regular expression replaces.