SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


SED and the Big Bad UNIX File


SED and the Big Bad UNIX File

Author
Message
TDuffy
TDuffy
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1954 Visits: 57

Good article on SED. But could you not just specify LF as the Row Delimiter in the properties of the Text File source within the package?

This may not have solved identifying the problem row, but there are some very good GUI based, inexpensive, text editors for opening large files. My favorite is vedit http://www.vedit.com/

Terry





mosaic-287947
mosaic-287947
Old Hand
Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)Old Hand (346 reputation)

Group: General Forum Members
Points: 346 Visits: 841

Thanks for a helpful article - and all the replies with other utilities and work-rounds. My favourite text editor is Notepad2.

I would like to make a suggestion: although it is nice to see the error messages as images, but it would help if you also mentioned the initial error message:

Error calling OpenRowset on the provider

in the text. Then this article can be found when someone else hits this problem and uses google to find a solution.


Rich James
Rich James
Old Hand
Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)Old Hand (302 reputation)

Group: General Forum Members
Points: 302 Visits: 182
"less" is a command that I find most useful for browsing through text based files.
Ability to scroll forward, backwards, jump to line number, and so on, all make this an invaluable tool.
Again though, based in UNIX (and available on the UNIX ports from sourceforge).
corey lawson
corey lawson
SSC Eights!
SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)SSC Eights! (935 reputation)

Group: General Forum Members
Points: 935 Visits: 576
Awk, Perl, Ruby are probably better than sed, as could some VBScript programming (hint: open the file as a stream, and replace LF with CRLF, or whatever...). sed has some limitations on line lengths...

Me? I would have used awk (or, more specifically, gawk). In the Textutils section on the gnuwin32 website you can d/l its verson of Less for win32, as well as gawk.

Kernighan, I think, keeps the 'original' awk on his site (he's the 'K' of awk).
Chuck Ritenour
Chuck Ritenour
SSC Journeyman
SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)SSC Journeyman (90 reputation)

Group: General Forum Members
Points: 90 Visits: 3
Regarding the post that talked about mainframe file dumps that used column width as the delimiter vs csv, pipes, tabs etc. I run across this a lot because the folks that actually purchase our data absolutely refuse to even discuss data formats - it's a 'not my problem to load it issue'.
There's no easy answer, but the sweat equity answer is to build a parser in the programming language of you choice to replace two spaces with one (except when found between quote delimiters). Run that multiple times until there are no double spaces, then replace the space with the column delimiter of your choice.
Now if I could just get mainframe weenies to understand that giving me a data dictionary of the columns, data types and widths IN column name ALPHABETICAL ORDER vs. ordinal position doesn't help at all....(especially when the first row of the file is not column names, which for some reason just never happens on main frame files).


Thanks, and don't forget to Chuckle
Roger L Reid
Roger L Reid
Mr or Mrs. 500
Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)Mr or Mrs. 500 (568 reputation)

Group: General Forum Members
Points: 568 Visits: 166

The lack of a decent shell in Windows is a killer. appropos newline would have shown you "unix2dos".

tr couild have done it. For a custom solution, I'd almost certainly turn to Perl before going back to sed.

The text file customs of both OSs are well documented so should not have been tough to research.

Stranger still is the idea that having to write filters in data processing would freak anyone out. That seems to me to be a huge part of what we do - take data however it is provided and turn it into something we can handle.

I don't understand the fellow who had a problem with the listing order of a data dictionary provided by an outside firm - maybe the mainframe weenies understand that "ordinal position" is a lame sort of concept to begin with. None of us wants to do the drudge work, but its what we get paid for. One thing I will say for the mainframe weenies (never was one but some of my best friends...) - they at least know basic database concepts, unlike 84.3% of the SQL Server jockies I've met.



Roger L Reid
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search