InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comThu, 24 May 2012 14:02:37 GMT20http://www.sqlservercentral.com/Forums/Topic464209-71-1.aspx[quote]I get these files which are basically text streams and they are only delimited by record. I've written a utlity to handle it now (it's slow and difficult to manage on all my remote sites) but is there anything already in SSIS that would just delimit the row at a specified length?[/quote]If you just need to break at a specified length, I would look at something like sed to add a row delimiter:sed s/.../(&)"\ n"/g(remove the space in \ n)should add a CRLF after every 3 characters.Dump the output into a new file and process it using bcp.--JimFive--Edit to fix filtered text.Tue, 20 May 2008 10:10:58 GMTJames Goodwinhttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxWe can all jump through flaming hoops to accomplish our ETL. However, there are limits on what we can or should accept as input. If the data format can't be parsed with consistent business rules that can be coded in T-SQL or C# or VB.Net, then we really can't process the file. Columns have to either be fixed-length or have a well defined delimiter. Same with row termination. It needs to be something that we can parse with the tools at hand. We have powerful tools, bcp, bulk insert, SSIS and others, but they can't guess at what the data is supposed to be. They have to be told what the data is supposed to be, and then when it isn't we must handle the exceptions. IMHO, It is acceptable to go back to the people who own the source data and say to them that a data format is unacceptable for specific reasons. Otherwise we need to hire a roomful of 10-key data entry clerks. Remember those days?Brandon_Forest@sbcglobal.netTue, 20 May 2008 09:44:50 GMTBrandon Foresthttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxHello,Would it be possible to email me the pacakge created in this example? I would like to use it as a guide since I am new to developing SSIS packages.Email: tcgeeks@live.comThanks,AssudadMon, 19 May 2008 14:11:55 GMTtcgeeks-689677http://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxYou could load the entire file into a single VARCHAR(MAX) column and use SUBSTRING to split it up on the server or you may be able to find some functionality in SSIS that would essentially substring the data out at fixed-width positions... I'm just not 100% on what the best SSIS functionality to do that would be.Thu, 20 Mar 2008 10:36:30 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxyeah i realized after i posted this that i included row delimiters in the example but there really aren't in the file i'm talking about. it's to the same end though. i'm after an ssis method to import the file into a table based on a column delimiter of every (n) characters.appreciate the feedback. thanks so much,-jThu, 20 Mar 2008 10:12:16 GMTdba4evahttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxHi johnr,The sample you posted appears to have row delimiters in it. If there are no row delimiters at all, and the file looks like this:[code]TEST1ABCTEST2DEFTEST3 TEST4GHI[/code]Then you'll probably have some problems. If the file looks like this:[code]TEST1ABCTEST2DEFTEST3 TEST4GHI[/code]And there actually are row delimiters, then the BCP format file trick should work. What you seem to be indicating though, is that you want to essentially rotate the columns and rows. I believe there's an SSIS transformation to do something similar, although if not you can bulk load into a temp table and perform a few INSERT...SELECT FROM... statements to move the data from the temp table into your permanent table pretty easily.Wed, 19 Mar 2008 19:40:41 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxI'm not sure that works because it's not a fixed width file, it's a chunk of text that may not have any delimiters in it at all and BCP still needs to know the ROW delimiter? If I'm wrong please do correct me, it would be very helpful.I guess an easier way to ask this question is how would you take a flat file that has these three lines of text below:TEST1 ABCTEST2 TEST3 DEFGand insert them into a table using SSIS with the ROW delimiter being every 3rd character so that your rows in your table would be:TEST1 ABCTEST2 TEST3 DEFGWed, 19 Mar 2008 17:42:23 GMTdba4evahttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspx[quote][b]johnr (3/19/2008)[/b][hr]Is it possible to use SSIS to import this same flat file if it doesn't have a ROW delimiter?I get these files which are basically text streams and they are only delimited by record. I've written a utlity to handle it now (it's slow and difficult to manage on all my remote sites) but is there anything already in SSIS that would just delimit the row at a specified length?Perhaps there's a different approach that doesn't require an external utility?[/quote]You can use the Bulk Insert task with a BCP format file to import fixed-length record files. You can probably do it in the data flow task as well with some transformations, although I haven't had occasion to do that myself yet.Wed, 19 Mar 2008 17:11:32 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxIs it possible to use SSIS to import this same flat file if it doesn't have a ROW delimiter?I get these files which are basically text streams and they are only delimited by record. I've written a utlity to handle it now (it's slow and difficult to manage on all my remote sites) but is there anything already in SSIS that would just delimit the row at a specified length?Perhaps there's a different approach that doesn't require an external utility?Wed, 19 Mar 2008 16:35:09 GMTdba4evahttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspx[quote][b]Jeff Moden (3/6/2008)[/b][hr][quote][b]Mike C (3/6/2008)[/b][hr]Heh... guess it depends on what business you're in... I've been fortunate in not ever having to mess with EDI files. I have had to do exactly what's in this article a couple of dozen times. Not necessarily using the methods in this article each time, but same problem files.[/quote]I guess I should have suspected, but I've gotten used to working with file formats where the headers and footers (if there are any) are used to group detail lines for specific reasons. I guess there's still a proliferation of headers and footers in files out there with the sole purpose of getting in the way to make the ETL process harder :)Thu, 06 Mar 2008 21:36:34 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspx[quote][b]Mike C (3/6/2008)[/b][hr]Yes, catchy title. When I first clicked on it I actually expected it would be about the type of file I was asking about. The author's right though, I've never had any need to solve this very specific problem. I was kind of wondering how common this specific problem is compared to, say, ANSI X12 EDI and other formats where simply stripping headers and footers is not an option?[/quote]Heh... guess it depends on what business you're in... I've been fortunate in not ever having to mess with EDI files. I have had to do exactly what's in this article a couple of dozen times. Not necessarily using the methods in this article each time, but same problem files.Thu, 06 Mar 2008 21:24:37 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspx[quote][b]Jeff Moden (3/6/2008)[/b][hr]I don't believe it was meant to... wasn't advertised that way except maybe by a slightly misleading title.[/quote]Yes, catchy title. When I first clicked on it I actually expected it would be about the type of file I was asking about. The author's right though, I've never had any need to solve this very specific problem. I was kind of wondering how common this specific problem is compared to, say, ANSI X12 EDI and other formats where simply stripping headers and footers is not an option?Thu, 06 Mar 2008 21:16:48 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspx[quote]How does your process work for files like this? [/quote]I don't believe it was meant to... wasn't advertised that way except maybe by a slightly misleading title.Thu, 06 Mar 2008 20:52:04 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspx[quote][b]Charles Kincaid (3/6/2008)[/b][hr]With all due respect to all of y'all after these many years I've seen some zany stuff. I'm rarely surprised any more. I write file parsers all the time. I just don't have to use SQL to do the job. I'm also not reading millions of rows at a crack either.We all have to play the cards we are dealt. We don't [b][i][u]have[/u][/i][/b] to like them and we get to complain about them.[/quote]I agree with your point of view, as I've done the same type of thing and have written many custom file parsers and custom ETL applications over the years. Honestly I don't think I've ever seen a header/footer file format in which all of the headers and footers could be so easily discarded without a second thought.Thu, 06 Mar 2008 19:28:57 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspx[quote][b]pnewhart (3/6/2008)[/b][hr]Unfortunately in my line of work...a IT solutions vendor...it's part of my job to be a tedious coder. Again, with all due respect to you, I've never seen an example in practice of having header and trailer records along with field names.[/quote]With all due respect to all of y'all after these many years I've seen some zany stuff. I'm rarely surprised any more. I write file parsers all the time. I just don't have to use SQL to do the job. I'm also not reading millions of rows at a crack either.We all have to play the cards we are dealt. We don't [b][i][u]have[/u][/i][/b] to like them and we get to complain about them.Thu, 06 Mar 2008 18:05:32 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspx[quote][b]paul.ibison (3/6/2008)[/b][hr]Brandon,all due respect but I think you're missing the point. Judging by some of the questions, some others (Jeff apart) have also never really tried to solve this problem in practice. Take a csv file with 50 columns and use your conditional split starting with a single column and see how long it takes. You'll have to define each of the 50 columns separately, and define the substring function separately for each one. In the example CSV file have no fixed width so your substring function will take account of looking for the commas, possibly nested inside a string. How long before this is robust? How tedious will it be to do all this coding? And the resulting code required will be huge. This method can do the same in an hour or so and have a very simple resultant package.Cheers,Paul Ibison[/quote]Unfortunately in my line of work...a IT solutions vendor...it's part of my job to be a tedious coder. Again, with all due respect to you, I've never seen an example in practice of having header and trailer records along with field names.Thu, 06 Mar 2008 13:07:24 GMTpnewharthttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspx[quote][b]paul.ibison (3/6/2008)[/b][hr]Brandon,all due respect but I think you're missing the point. Judging by some of the questions, some others (Jeff apart) have also never really tried to solve this problem in practice. Take a csv file with 50 columns and use your conditional split starting with a single column and see how long it takes. You'll have to define each of the 50 columns separately, and define the substring function separately for each one. In the example CSV file have no fixed width so your substring function will take account of looking for the commas, possibly nested inside a string. How long before this is robust? How tedious will it be to do all this coding? And the resulting code required will be huge. This method can do the same in an hour or so and have a very simple resultant package.Cheers,Paul Ibison[/quote]I'll admit I've never had to solve this exact problem before, where simply stripping headers and footers away would do the trick. Problems I've had to solve involving headers and footers have sometimes involved files with complex hierarchical structures from legacy systems, like this:FILEORDERS|2HDR|100293|987|20080326ITM|897654|9876.87|3ITM|098643|76.34|12FTR|100293|2HDR|100294|456|20080326ITM|765432|11.99|6FTR|100294|1ENDORDERS|2CUST|2HDR|987ITM|Joe|Jackson|98 Palomino Way|Los Angeles|CA|90823FTR|987HDR|456ITM|Lisa|Lewis|123 Sesame Street|New York|NY|10014FTR|456ENDCUST|2ENDFILEHow does your process work for files like this? Ignoring header and footer information in this file isn't an option since you will lose important information during the process, such as the order #s and order dates, the line item count, and other auditing information included in the file like record counts, etc.Thu, 06 Mar 2008 12:53:50 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxBrandon,all due respect but I think you're missing the point. Judging by some of the questions, some others (Jeff apart) have also never really tried to solve this problem in practice. Take a csv file with 50 columns and use your conditional split starting with a single column and see how long it takes. You'll have to define each of the 50 columns separately, and define the substring function separately for each one. In the example CSV file have no fixed width so your substring function will take account of looking for the commas, possibly nested inside a string. How long before this is robust? How tedious will it be to do all this coding? And the resulting code required will be huge. This method can do the same in an hour or so and have a very simple resultant package.Cheers,Paul IbisonThu, 06 Mar 2008 09:34:44 GMTpaul.ibisonhttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxThe "Ragged Right" Files I have to deal with are Mixed Row Type files as well. Because of that, I cannot use column names from the csv file, but also, each row starts with a TypeID.So, in DTS, I create a transform task for each row type and use a Transform Task with this code on the first column:Function Main() If DTSSource("Col001") = "HDRID" then Main =DTSTransformStat_OK ELSE Main = DTSTransformStat_SkipRow End ifEnd FunctionIn the example for the article your initial transform would just check the length and skip it if it was a header or footer. There's no need to import-export-import, even to get the column headings.--JimFiveThu, 06 Mar 2008 08:57:16 GMTJames Goodwinhttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxJust to clarify - the "hour or so" being referred to was the time taken to create the package.Rgds,Paul IbisonThu, 06 Mar 2008 07:15:11 GMTpaul.ibisonhttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxHeh... Hour or so? Like I said previously, I've used this method to do 5.1 million rows in about 3 minutes. I'm sure there's a faster method, but it worked for me.Thu, 06 Mar 2008 06:48:04 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxCool... let's see your solution for this, Brandon.Wed, 05 Mar 2008 15:21:45 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxI agree that this is not a ragged file problem, but an exercise in stripping the first and last rows. A conditional split would be the approach I would also take, or simply import with a delimited field mask for the "," into a staging table with nullable fields and then delete for null data. I have an aversion to bringing data into the system, cleaning it and then extracting it just to import again. I see no reason to create a cleaned extract, and several reasons not to. That will create a file management problem eventually, especially with large datasets. Once the data is in the RDBMS why go outside again? All the tools for transformation are there in SSIS and SSMS. I see this as a simplistic and inappropriate use of the tools available. The desire to make it easier for developers down the road can be accomplished inside the package without complex logic. And what "code" would need to be made can be made dynamic by using column indexes instead of names.Brandon ForestSr. SQL DBAWed, 05 Mar 2008 14:46:21 GMTBrandon Foresthttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxThat's certainly another way to do it.Wed, 05 Mar 2008 13:08:16 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxI had a question about right-ragged file formats in a previous post.I ended up having to use a script because of the llimitations of the conditional split.(see post http://www.sqlservercentral.com/Forums/Topic355490-148-1.aspx)Wed, 05 Mar 2008 13:03:03 GMTRin Sitahhttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxThis looks like a traditional header/footer formatted text files. It doesn't seem to account for additional information in the header and/or footer that applies to all rows nested within them. I don't think you can account for these types of files with this method. It also seems to think that all header/footer combinations in these files will contain the exact same content in the exact same format all the way through the file. For many header/footer files I've had to import this is not a safe assumption.Wed, 05 Mar 2008 10:06:13 GMTMike Chttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspx[quote][b]admin (3/5/2008)[/b][hr][quote][b]Jeff Moden (3/5/2008)[/b][hr]And, no... I'm not making fun of anyone who uses DTS... if you take it that way, then I apologize. I'm just saying I've always been able to beat DTS with T-SQL especially on CDR files.[/quote] No apologie needed!:P[/quote]Thank you for your understanding... pretty cool especially for someone relatively new to the forum. :)Wed, 05 Mar 2008 09:39:08 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspx[quote][b]paul.ibison (3/5/2008)[/b][hr]Thanks Jeff. The name makes sense but I can find no reference to it. Can you provide a link to the "mixed rowtype" definition (search for "mixed rowtype" in Google and get 0 results). Cheers,Paul[/quote]Heh... apparently.... not. :D I've never had a problem with folks understanding me when I say "Mixed RowType" but, you're correct... it doesn't appear in Google even if I split the word "RowType" into two. Looks like I've coined another phrase.I did find a couple of close references... but they don't come near the simplicity of "Mixed RowType"....[url]http://publib.boulder.ibm.com/infocenter/iseries/v5r3/index.jsp?topic=/dbp/rbafoformt.htm[/url][url]http://accessblog.net/2007/03/export-mixed-type-data-to-excel.html[/url]Wed, 05 Mar 2008 09:35:14 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspx[quote][b]Jeff Moden (3/5/2008)[/b][hr]And, no... I'm not making fun of anyone who uses DTS... if you take it that way, then I apologize. I'm just saying I've always been able to beat DTS with T-SQL especially on CDR files.[/quote] No apologie needed!:PWed, 05 Mar 2008 09:24:28 GMTMiguel Pereira-543757http://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxOops. I reread the original. You are getting the column names from the header row and letting BCP or the like do the column splitting for you.Sorry, had not had my first cup yet.Wed, 05 Mar 2008 09:20:40 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxThanks Jeff. The name makes sense but I can find no reference to it. Can you provide a link to the "mixed rowtype" definition (search for "mixed rowtype" in Google and get 0 results). Cheers,PaulWed, 05 Mar 2008 09:17:43 GMTpaul.ibisonhttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxPaul,Once that data was imported into SQL why go back to a flat file? I would have thought that you could have started digesting the data straight away. One less round trip. Even if you are aggregating multiple input files into a larger batch you could just suck the fitting rows into the aggregate table.Wed, 05 Mar 2008 09:15:54 GMTCharles Kincaidhttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspx[quote][b]Paul Ibison (3/4/2008)[/b][hr]Comments posted to this topic are about the item [B] [/B][/quote]Pretty good article, Paul... straight and to the point.Wed, 05 Mar 2008 09:10:41 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspx[quote][b]admin (3/5/2008)[/b][hr]Definitely an ingenious solution; however, it can quickly become a poor approach when having to deal with larger import file -think telephone call records for instance.I've found that the best approach is -and forgive me for still using DTS! :)- the judicious application of modest VB scripting to quickly manipulate suspicous data, or 'cleansing' done directly within the package and temp table.[/quote]Actually, I've used the method in this article for CDR (Call Detail Records) cleansing... Consider that bulk insert will import 5.1 million rows in 60 seconds and BCP will do an export in about the same time... that means you can do a 5.1 million row "clean up" of this nature at the rate of 5.1 million rows in about 3 minutes (1 export, 2 imports). Of course, CDRs are normally in a fixed field format and it's a lot cheaper to just input the file into a single column and use substring to split the records whilst ignoring the "short rows". Works nasty fast. Another advantage is for CDR files like what some of the "Bell" companies send... they send a mixed bag... they use a certain "record indicator" in the same postion across multiple record types that have different layouts (CDRs vs Tax Records vs records counts for both). A simple substring "detector" allows me to filter out all but the rows that I want to split.Heh... and forgive me for not using DTS! I've actually never learned how to use it because I can normally beat the guys at work that do use it for performance.And, no... I'm not making fun of anyone who uses DTS... if you take it that way, then I apologize. I'm just saying I've always been able to beat DTS with T-SQL especially on CDR files.Wed, 05 Mar 2008 09:09:59 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspx[quote][b]admin (3/5/2008)[/b][hr]Definitely an ingenious solution; however, it can quickly become a poor approach when having to deal with larger import file -think telephone call records for instance.I've found that the best approach is -and forgive me for still using DTS! :)- the judicious application of modest VB scripting to quickly manipulate suspicous data, or 'cleansing' done directly within the package and temp table.[/quote]DTS is IMO a preferred method for doing quick imports with a lot less overhead. However, there are various scripting tools available within SSIS also. Also, since DTS is being deprecated for SQL Server 2008, it may be time to bite the bullet. :)Wed, 05 Mar 2008 09:02:25 GMTpnewharthttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspx[quote][b]brewmanz.sqlservercentral (3/5/2008)[/b][hrI was hoping for the import of the classic ragged-right file - a variable number of fields in records that are wanted. [/quote]For your definition of "ragged right", is each row supposed to have the same number of "fields" in the same position in each row but because the right hand fields aren't necessarily populated nor padded, that's what makes it ragged?Do you have an example ragged right file and a record layout that I could play with?Wed, 05 Mar 2008 08:55:37 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxDefinitely an ingenious solution; however, it can quickly become a poor approach when having to deal with larger import file -think telephone call records for instance.I've found that the best approach is -and forgive me for still using DTS! :)- the judicious application of modest VB scripting to quickly manipulate suspicous data, or 'cleansing' done directly within the package and temp table.Wed, 05 Mar 2008 08:52:21 GMTMiguel Pereira-543757http://www.sqlservercentral.com/Forums/Topic464209-71-1.aspx[quote][b]paul.ibison (3/5/2008)[/b][hr]True - the title is a little misleading. The problem is that there isn't a 'correct' term for such files and this is the nearest commonly understood one. Anyway, what you need is this link: http://www.sql-server-performance.com/article_print.aspx?id=1056&type=artRgds,Paul Ibison[/quote]Actually, there is... it's called a "mixed rowtype" file.Wed, 05 Mar 2008 08:51:10 GMTJeff Modenhttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspx[quote][b]paul.ibison (3/5/2008)[/b][hr]I looked at the conditional split, but if you have >50 columns (as I have), you'll need to manually define each column - that means define the name and write 50 substring clauses. If you have extracted the data to a csv staging flat file, with the column headers as the first row, this part is all done automatically. Cheers,Paul Ibison[/quote]OK. I misread your statement about defining the columns. I generally follow a supplied file layout to define the columns. It's not been my experience where a file would have column names given [i]and[/i] have a header and trailer row.Wed, 05 Mar 2008 08:15:22 GMTpnewharthttp://www.sqlservercentral.com/Forums/Topic464209-71-1.aspxI looked at the conditional split, but if you have >50 columns (as I have), you'll need to manually define each column - that means define the name and write 50 substring clauses. If you have extracted the data to a csv staging flat file, with the column headers as the first row, this part is all done automatically. Cheers,Paul IbisonWed, 05 Mar 2008 08:03:26 GMTpaul.ibison