Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase ««12

SED and the Big Bad UNIX File Expand / Collapse
Author
Message
Posted Tuesday, January 10, 2006 10:37 AM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Monday, September 15, 2008 12:02 PM
Points: 1,318, 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

 

 




Post #249677
Posted Wednesday, January 11, 2006 2:12 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 01, 2014 1:39 PM
Points: 189, Visits: 542

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.

Post #249831
Posted Thursday, January 12, 2006 3:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 8:51 AM
Points: 100, Visits: 176
"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).
Post #250179
Posted Thursday, January 12, 2006 7:57 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Friday, April 11, 2014 2:09 PM
Points: 154, Visits: 323
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).
Post #250477
Posted Friday, January 13, 2006 10:50 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Monday, April 05, 2010 1:37 PM
Points: 38, 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
Post #250698
Posted Wednesday, January 10, 2007 9:01 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 09, 2013 8:32 AM
Points: 108, 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
Post #335823
« Prev Topic | Next Topic »

Add to briefcase ««12

Permissions Expand / Collapse