Split String Using Delimiter
Hi,
I get a string whihc looks like 'Q306/Q406 Version1/Current/Q108 Version2'
I need to split the above string and get each of those values... ' / ' delimiter
Can some one please help on this.
Thanks
View Complete Forum Thread with Replies
Related Forum Messages:
Splitting A String By A Delimiter, But Ignoring Multiple Delimiters
Hi, I have a function which takes a string and a delimter. It then splits the string by the delimter and returns a table of resultant strings: CREATE FUNCTION [dbo].[vs_SplitTags] (@sep char(1), @s varchar(512)) RETURNS table AS RETURN ( WITH Pieces(pn, start, stop) AS ( SELECT 1, 1, CHARINDEX(@sep, @s) UNION ALL SELECT pn + 1, stop + 1, CHARINDEX(@sep, @s, stop + 1) FROM Pieces WHERE stop > 0 ) SELECT pn, SUBSTRING(@s, start, CASE WHEN stop > 0 THEN stop-start ELSE 512 END) AS s FROM Pieces ) This works very well, other than instances of the delimter are, themselves, considered to be results. For example: SELECT * FROM vs_SplitTags(' ', 'foo bar') AS result returns: pn s 1 foo 2 bar which is exactly the result I would want. However, SELECT * FROM vs_SplitTags(' ', ' foo bar ') AS result -- There are spaces before 'foo' and after 'bar' returns pn s 1 2 foo 3 bar 4 And SELECT * FROM vs_SplitTags(' ', 'foo bar') AS result -- There are two spaces between 'foo' and 'bar' returns pn s 1 foo 2 3 4 bar I want the function to ignore whitespace altogether, be it a single space or multiple spaces. Other than to delimit the boundries between words, of course. In other words, all three examples above should produce the same result: pn s 1 foo 2 bar How can I do this? Any thoughts much appreciated...
View Replies !
How To Split A String Using Sql
I have 5 dynamic rows each row consisting of 5 checkboxes & 5 dropdowns.I am concatenating the values of each controls in a row using a wildcard charater "~" and each row i am concatenating using "|".The complete string is then assigned to one hidden field and passed as sql parameter to the backend. Please help in writing the split function to get the values of each checkboxes and dropdowns from the string in order to save them in separate columns. Thanks
View Replies !
Split A String
Hi I have this string which might have a hyphen in it "-" What I want to do is if I get a hyphen then take all characters after hyphen else take only all the characters starting from the 5th position of the string How can this be achieved?
View Replies !
Split A String In Sql
Hi I need a stored procedure in SQL that will split a comma separated variable passed to it select a name for each value and return a recordset. Any pointers greatfully received. First attempt is dreadfully slow as I am opening recordsets each time Function func_getFood() Dim rsfoodsql Dim foodoutput for x=1 to ubound(masterfoodarray)-2 set rsfoodsql= objconn.execute ("select foodname from tbl"&language&"food where foodID='"& masterfoodarray(x) &"'") if not rsfoodsql.eof then foodoutput=rsfoodsql("foodname") if not foodoutput="" then response.write foodoutput&"<BR>" end if end if next End Function Hope someone can help, cheers
View Replies !
How To Split The String
I am having City, State i need to split these two string with comma separated and to search based on the city and state how to write the select query any one know help me Regards, Prabu R
View Replies !
How To Split A Delimeted String...
hi, i have a simple question, i have a sql server table named "Restaurant" and a field "Cuisines", the values in the field Cuisines contains delimeted string. (e.g. "C1,C2,C3...C10" ,maximum to 10 items or less) now, i want to split the values from Cuisines ("C1,C2,C3...C10") and passed all of them to a SP parameter. e.g @item0 nvarchar = "C1",@item1 nvarchar = "C2",@item2 nvarchar = "C3",@item3 nvarchar = "C4",@item4 nvarchar = "C5",@item5 nvarchar = "C6",@item6 nvarchar = "C7",@item7 nvarchar = "C8",@item8 nvarchar = "C9",@item9 nvarchar = "C10" if the field "Cuisines" contains less than 10 items, the value of the remaining parameters is null. any response will be appreciated. thanks.
View Replies !
SQL String Split With | Pipe
I've got a dilemma here. I'm currently reading records into EasyListBox (ELB) dropdownlist. The criteria in the dropdownlist is based on the select from another ELB dropdownlist. The problem is that the data contains rows with pipes that serve as separters. For example, one of the options that comes back now might be 123 | 456 | 789. I need to be able to make each of them its own row in the dropdownlist. Can anyone advice? Most records don't have any pipes and come throught fine, but some dont. My code below: Dim sAT As String = Replace(elbAttribute.SelectedValue, "'", "''") Dim adapter As New SQLDataAdapter("Select Feature_Type As Attribute_Name, FEATURE_TEXT_VALUE as Attribute_Value, (select distinct FILTER_FAMILY_FEATURE_EXCLUDED.FEATURE_TYPE_ID from FILTER_PROFILES, FILTER_FAMILY_FEATURE_EXCLUDED, FAMILY_ALLOWED_FEATURE_TYPES, SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES where FILTER_PROFILES.FILTER_PROFILE_NAME = 'MAQ' And FILTER_PROFILES.FILTER_PROFILE_ID = FILTER_FAMILY_FEATURE_EXCLUDED.FILTER_PROFILE_ID And FAMILY_ALLOWED_FEATURE_TYPES.FAMILY_ID = FILTER_FAMILY_FEATURE_EXCLUDED.FAMILY_ID And FILTER_FAMILY_FEATURE_EXCLUDED.FEATURE_TYPE_ID = SHARED_FEATURE_TYPES.FEATURE_TYPE_ID And (SHARED_FEATURE_TYPES.Feature_Type_ID = SHARED_FEATURE_VALUES.Feature_Type_ID And left(FEATURE_TYPE, 4) <> 'CPLV' And Feature_Type = '" & sAT & "')) As ExclusionFlag From SHARED_FEATURE_TYPES, SHARED_FEATURE_VALUES, PRODUCT_FEATURE_VALUES Where PRODUCT_FEATURE_VALUES.FEATURE_VALUE_ID = SHARED_FEATURE_VALUES.Feature_Value_ID And SHARED_FEATURE_TYPES.Feature_Type_ID = SHARED_FEATURE_VALUES.Feature_Type_ID And left(FEATURE_TYPE, 4) <> 'CPLV' And Feature_Type = '" & sAT & "' UNION Select distinct Column_Name As Attribute_Name, SMALL_TEXT_VALUE As Attribute_Value, (select FILTER_ATTRIBUTES_EXCLUDED.EXT_ATT_ID from FILTER_PROFILES, FILTER_ATTRIBUTES_EXCLUDED where FILTER_PROFILES.FILTER_PROFILE_NAME = 'MAQ' And FILTER_PROFILES.FILTER_PROFILE_ID = FILTER_ATTRIBUTES_EXCLUDED.FILTER_PROFILE_ID And FILTER_ATTRIBUTES_EXCLUDED.EXT_ATT_ID = EXTENDED_ATTRIBUTES.EXT_ATT_ID) As ExclusionFlag From EXTENDED_ATTRIBUTE_VALUES, EXTENDED_ATTRIBUTES, PRODUCTS Where PRODUCTS.PRODUCT_ID = EXTENDED_ATTRIBUTE_VALUES.PRODUCT_ID And not SMALL_TEXT_VALUE is null And EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID > 1499 And EXTENDED_ATTRIBUTE_VALUES.EXT_ATT_ID = EXTENDED_ATTRIBUTES.EXT_ATT_ID And Column_Name = '" & sAT & "'", myConnection) Dim dt As New DataTable() adapter.Fill(dt) elbValue.DataSource = dt elbValue.DataBind()
View Replies !
Sql Charindex Split String
HelloI am quite hopeless and of course a newbe.The situation: Sql2k / queryI would like it ot break down the following string:2004 Inventory:Ex.Plant Farm1:1st Cut:Premium:0094Whereby:Year = '2004 Inventory'plant= 'Ex.Plant Farm1'cut = '1st Cut'grade = 'Premium'lot# = '0094'It is always seperate by ':', but it can be 5 fields or 4 or 3 and sooncode to create the view:CREATE VIEW dbo.TESTASSELECT FullName, LEFT(FullName, CHARINDEX(':', FullName + ':') -1) AS year, CASE WHEN LEN(FullName) - LEN(REPLACE(FullName, ':', ''))[color=blue]> 0 THEN LTRIM(SUBSTRING(FullName,[/color]CHARINDEX(':', FullName) + 1, CHARINDEX(':', FullName + ':',CHARINDEX(':', Fullname) + 1) - CHARINDEX(':',FullName) - 1)) ELSE NULL END AS Plant, CASEWHEN LEN(FullName) - LEN(REPLACE(FullName, ':', '')) > 1 THENLTRIM(SUBSTRING(FullName,CHARINDEX(':', FullName + ':', CHARINDEX(':',FullName) + 1) + 1, CHARINDEX(':', FullName + ':', CHARINDEX(':',Fullname) + 1) - CHARINDEX(':',FullName+':') - 1)) ELSE NULL END AS [Cut]FROM dbo.ItemInventoryCan anyone help me with this? I am stuck half the way and get for cutthe rest of the string: '1st Cut:Premium:0094'Thanks!
View Replies !
Split The String Into Columns
I have a table called products with the values like ProductId ProductName 10 A 20 D,E,F,G 30 B,C 40 H,I,J I need to display each productid's with ProductId ProductName 10 A 20 D 20 E 20 F 20 G 30 B 30 C 40 H 40 I 40 J I will be appreciated if you can send me the code. Thanks, Mears
View Replies !
Split Command In String
hi all, i am still a newbie in sql i got a problem with my query for example: ------------------------------- DECLARE @LongString VARCHAR(10) @LongString = "Hello-Word" ------------------------ i want to change the LongString value by removing the "-" however i i need to identify if the @longString got "-" and delete it if it found how do i do that in sql Thanks a lot guys arifliminto86
View Replies !
SPLIT A String Inside SSIS/T-SQL Code?
I have a RPC which gives me multiple - single record rows like Robert|K|Half|TX|1123823|1423904 -- This is one such record that i get in 1 Column Now I need to split the above record into 6 Fields and populate in my local DB I know i could use any .NET Language and accomplish this. But Iam limited to using SSIS/T-SQL Proc's Any ideas / directions?
View Replies !
Delimiter - Which One To Use?
Hi Please tell me which delimiter to use. Criterion: 1) The delimiter should appear as if it were space in the Back end 2) while running reports it should not dampen the lay out 3) The delimiter should not be an enterable character Ex: i tried with chr(10) but while running reports, the characters after the delimiter are shifted to new line. The delimiter that i use, should not create such undesirable effects. Thanks in Advance
View Replies !
Row Delimiter {LF} Is Ignored?
Hi! Im trying to import some data from a Flat File Source, where a row delimiter is {LF} and column separator is SPACE. Data looks like this: GI$0c2 T08b 1 1 20060508 000655 6 8 8 c0a81f1f 1 1 1 00A 3 24206816 3 24206816 1 1 3 59910000 001 1 3 14730050 0 25 F10 XX 317148-131136 loop TG_MRB 0 M027 1 3 0 20060508 000655 0 3 59910000 SIP This is the first record that generates around 41 columns and sorts data as it should, but if the second record is smaller the row delimiter {LF} is ignored and put into one of the columns untill all 41 columns from previous record are filled. It seems to me that columns separator has the priority over the row delimiter which is very wrong. :). If there is a {LF} in the file that should mean that it needs to be in a new row as a new record. I try to keep this all in a SQL 2005 package without using any additional software, i know there might be a solution with the scripting component, but would like to see if theres someone with the similar solution before i start writing any scripts. (i dont like parsing strings with scripts from bulky files:)) Thanks! Sebastijan L.
View Replies !
Why Is "" Read As A Tab Delimiter In DTS?
hello! i was on with DTS, dumping a flat file to SQL DB and i came across an error which says: "Too many columns...; found whitespaces.." When I checked the row that caused this, I found out that entries with ""s are split into two columns possibly because the backslashes were read as tab delimiters.. I've tried other delimiters, but they do not seem to fit because most of the entries have them. Please advise. Thanks!
View Replies !
File Has No Row Delimiter
Hi, I have a problematic file. It's vertical-bar delimited, but the file conn mgr can't find ANY of the pre-defined row delimiter types. The data looks like this: col1|col2|col3||||||||| With a bunch of vertical bars on the end of each line. How should I handle this file? Thanks!
View Replies !
BCP Row Delimiter Problem
I have a datafile that has a column delimiter of "," and a row delimiter of No matter how I try BCP from a command prompt or use bulk import from a query, it seems to ignore the 0a delimiter. Yes, I've looked at the file with a text editor....0a is the only row delimiter. Any idea why it doesn't work? Thanks!
View Replies !
Field Delimiter
Hello-- I need to Ç as my field delimiter and Æ as my row delimiter in bcp script. I use the script below but I dont get the right outcome- HELP bcp databse.dbo.table out table.dat -S myserver -U myserid -P mypassword -c -tÇ -r"Æ" Josephine Josephine
View Replies !
Pipe Delimiter Problem
Can somebody help me with a delimiter problem I have.I have several PIPE (|) delimted text files which I need to import toSQL.With one of the files I keep encountering the following error;"Error at Source for Row 27753. Errors encountered so far in this task:1. Column Delimter not found."I suspect the problem is that one record (and possibly more) has a PIPE(|) within a field, because some of the fields contain free text.Getting an export of the file again using a different delimter like tabor comma will not work as these characters occur throughout the file.I'm open to suggestions as to how to resolve this and really need toget a solution soon.One solution I was thinking of, but do not know how to execute is tocount the number of PIPEs on each record and then manually change therecords which have count which is inconsistent with the rest of thefile.I've also tried importing to Access first and then SQL, as this hasworked for me in the past, but it did not work.Regards,Ciarán
View Replies !
Row Delimiter From Osql Output
hi, i had been removed the row counts and the column spaces... but ifailed to remove the extra tabs between rows of data return from theosql output.how to detect the row delimiter?i noticed i can use bcp to have a more decent output file, but i don'twant to do too much of file read-write. the programming enviroment isquite easy to "capture" the output from the comand prompt.secondary, char(252), 253, 254, 255 is not being read by the commandprompt, for example, 253 turns into 132 when i decode the input.thankx.from alan.
View Replies !
Column Delimiter As A SPACE?
I'm in a flat file connection manager editor and I have a flat file where the columns are separated by a space. Does anyone know how to specify a space in the column delimiter option? I've tried {' '} and {s} but these don't work. Not sure what the syntax is for indicating a space. Thanks in advance
View Replies !
Text Delimiter (newbie)
Sorry if this thread has been repeated but I cannot find a reference to my problem. I have read that apostrophes can be qualified with double quotes but that doesnt solve my problem. I am using SQL 2000 on a Windows 2000 Server trying to set up a job to email the results of a query. I have had no trouble getting it to run as scheduled but cannot seem to get the results out when I add a clause that filters out a text column. This is the query I am trying to run from a step within a job inside our SQL Agent: exec master.dbo.xp_sendmail @recipients = 'user@mycompany.com', @subject = 'Overnight Safedata Statistics', @query =' SELECT u.username as BackUpSet , r.requestresultstatus as Status, r.requesttype from users u ,requests r where u.userid = r.userid AND requestdate between getdate()-1 and getdate() AND requestresultstatus <> 'Success' ', @message = 'Results over the last 24 Hours', @attach_results = FALSE, @dbuse = 'BackupServer' I can successfully run the @query string in query analyzer and it worked fine from SQL mail until I added the last clause to the step (AND requestresultstatus <> 'Success' ). I do not know how to get the 'Success' treated as text. Any advice would be appreciated. Thanks JT P.S. I am a network guy trying to give our SQL developers a break.
View Replies !
Data With Semicolon Delimiter
Hi all...... I have an issue There are 2 tables... source and target... Data from source goes into target table under same field... ISSUE IS - Data from 'n' no. of records from source table goes into a single record in the target table with delimiter being a semi colon (;)... where(n >=2)... For example - if the source table has 'src1', 'src2' and 'src3' as the data then target table will have a single record with semicolon as delimiter as 'src1;src2;src3' How do we compare the data under this particular field now... Do we have to use a if then loop for identifying when the ; ends in target data... kindly help by giving a example... Thanks, Avi.
View Replies !
Custom Column Delimiter
Is it possible to specify a custom column delimiter when importing a text file using SSIS? I need to specify ASCII 127 as the field delimiter in a text file. =============================================== Creating tomorrow's legacy systems today. One crisis at a time.
View Replies !
Delimiter In Reporting Services CSV File
Hello, Does any one know a simple way of changing the delimiter value for a CSV report delivered via email from SQL Server 2000 Reporting Services? The default is comma. From the research I've done it seems that it can be done by writing a VB script that calls the Render method, but I'm not a developer and it seems crazy to have to go to those lengths just to change one attribute of a rendering extension that is available out-of-the-box. I hoped that I would be able to change a value in a config file (e,g, RSReportServer.config), but this seems not to be the case. Thanks Lempster :S
View Replies !
Flat File - Row Delimiter Problem
Hi, I'm trying to design this package where i take data from a source and need to transform it into a flatfile with some extra static information. I use a SQL script like this (ex.): SELECT ' BS0220131264202400000130001'+cast(wa.perf_applicant_number as nvarchar)+'000000000' + wa.perf_firstname + ' ' + wa.perf_lastname + CHAR(13)+ 'BS0220131264202400000330001'+REPLICATE('0',(15-LEN(wa.perf_applicant_number)))+cast(wa.perf_applicant_number as nvarchar)+'000000000' + WAPD2.strvalue+ ' BS0520131264202410001130001'+REPLICATE('0',(15-LEN(wa.perf_applicant_number)))+cast(wa.perf_applicant_number as nvarchar)+'000000000 tekst der skal stå på kortet' as nvarchar FROM dbo.WAIT_Applicant WA (nolock) This makes the text (from one record) split up over several lines in the output. I succeded with this in a SQL2000 DTS package and the flat txt-file looked liked I wan't it to. But now i tried doing it in 2005. And now it is not workin' anymore In my Flat File Connection Manager Editor i chose {LF} as the row delimiter and the preview looks really nice. Like this: BS0220131264202400000130001000000015826727000000000Søren Hesth BS0220131264202400000330001000000015826727000000000adfasdf BS0520131264202410001130001000000015827207000000000 tekst der skal stå på kortet But in the file that is created it doesn't split up over several lines. Instead of a carriage return it puts a [black box] - a sign which counts as the carriage return. I don't know if I have explained this well enough, but I hope that someone can help me. I've been trying for 3 days now.
View Replies !
Integration Services Remove The Row Delimiter On Some Lines
Hello Folks, I have a importfile that I need to insert into an db table. The file looks like this: one;two;three;text;moretext one;two;three;text;moretext; one;two;three;text;moretext one;two;three;text;moretext; one;two;three;text;moretext one;two;three;text;moretext; As you can see some rows contains a delimiter while others dont. There is a programing error on the application that generates the file and this cannot be changes. Is there a way in integration services to remove the delimiter ? Thanks holts
View Replies !
Column Delimiter In DTS Output Test File
Hello Everyone, Hope someone will be able to help me out here. I have a text file exported from my DTS package and it requires an '!' as a custom column delimiter. Does anyone have any idea how I can use the '!' mark instead of the Tab or Vertical Bar as my Column Delimiter? Would appreciate any suggestions. Thanks, Kay
View Replies !
Define Row Delimiter To Fixed Width Files
hi all, is there a way to define a row delimiter to fixed width files? such as this one: 1122333 4455666 7788999 in this file i have 3 columns that are fixed (col1.width = 2, col2.width = 2, col3.width = 3) but have {CR}-{LF} as the row delimiter. when i try to create a flat file connection to these kind of files- he always reads the CR-LF as part of the file text, and there is no place where i can define the row delimiter if i have. thanks for your help!!!
View Replies !
Set The Flat File Column Delimiter Programatically
hi guys, i am working on a project witch involves creating packages on-the-fly to import data from txt/csv/xls files according to some definitions on the database. so far, i have been doing fine. now we are planning the ASP.net page that enables the customer to define the input file format that will be imported to the system. we want it to have the same listBox as the FlatFileConnectionManager Editor has to define some properties, such as - column delimeiter. the code to set the column delimiter looks like this: SSISRunTime.IDTSConnectionManagerFlatFile90 myFilecn = null; myFilecn = (SSISRunTime.IDTSConnectionManagerFlatFile90)package.Connections["InputFileConnection"].InnerObject; DtsConvert.ToConnectionManager90(package.Connections["InputFileConnection"]); SSISRunTime.IDTSConnectionManagerFlatFileColumn90 col col = myFilecn.Columns.Add(); //..... string colDelimiter ="|" ; // it actually gets the data from the database... but it is the same thing col.ColumnDelimiter = colDelimiter; when we deal with the simple characters- "," , ";" , "|" ... we have no problems with setting the delimiter. but how can i set the delimiter to Tab? or {CR} ? {LF}? i tried to look at the dtsx- XML , and i see that the column delimiter that is defined when i choose Tab is: _x007C_, but when i try to do something like this: col.ColumnDelimiter = "_x007C_" ; it doesn't work. the same happens when I try "{t}" or "Tab". how do i solve this problem, and enable the user to select Tab as a column delimiter? Thanks!
View Replies !
Dynamic Column Delimiter In Text Files
Hi, is there any way to change the delimiter in a FlatFile Source Adapter dynamically? You can do that with the row delimiter but there is no expression for the column delimiter... Is there any other way besides changing the XML code? Thanks,
View Replies !
Loading Flat File - Row Delimiter Not Recognized
I have a text file that loads just fine with DTS 2000, but in SSIS it does not seems to recognize the row delimiter. For example, most rows in the text file have 10 columns, but some have 8 columns. For those with 8 columns, the SSIS is adding the data from next row, not padding the columns with nulls. Please help... Thanks in advance.
View Replies !
Reporting Services 2005 - CSV - Specify Alternative Delimiter
HEEELP Please..., I have a report which I need to save as CSV file. The delimiter needs to be a cooma and the values need to be delimited by double quotes..because of the requeriments. The issue is that one of the fields in the report, has commas as well...so...those values get surrounded by the specified qualifier...I had to change it to be something else than "..because it gets even worse...If I use Pipe..below is the result... "MRI","BHOB1",|"Best House on the Block, Inc."|," Is there a way to specify a whitespace or tab as the Qualifier? That would fix the issue i believe. <Configuration> <DeviceInfo> <Encoding>ASCII</Encoding> <Qualifier>'	'</Qualifier> </DeviceInfo> </Configuration> Thanks in advance...
View Replies !
BULK INSERT W/odd Delimiter OK In 2000, Not 2005
We use BULK INSERT to load client data into our program. One of our clients uses the character '²' (0xB2) as a field delimiter in their input files. This worked fine in SS2000 but is failing in SS2005. After some testing, it appears that any high-ASCII value has the same problem; if I set the delimiter to anything below 0x80, it works and with any value of 0x80 or higher it fails. I've verified that the format file we're using is correct for all of the tested delimiter values. (|, , ², ~, and €). The database collation sequence is SQL_Latin1_General_CP1_CI_AS if that matters. Is there a way I can force acceptance of high-ASCII values as delimiters in SS2005? Do I need to play with the system code pages or the collation sequence? Any assistance gratefully received.
View Replies !
Importing Text File: How To Dynamically Change The Row Delimiter
Hi, I have a dts package that imports a number of text files into a SQL Server 2000 database table. The package has been set up to accept a text file with a row delimiter of carriage return and line feed ({CR}{LF}). Some of the text files I receive only have a line feed ({LF}) as the row delimiter and the dts package fails the file. Is there an activex script i can use that will scan the file and change the row delimiter as required? i was going to use the filesystemobject which allows me to read a line at a time, however the Readline method doesn't read the new line character. The text files are too big to read into one variable and then do a replace. Any help would be appreciated I am using SQL Server 2000 SP3, Windows Server 2000 and Windows XP Professional. All systems are fully patched Regards Justin
View Replies !
No Row Delimiter For Fixed Length Flat File Destination?
When I use SQL 2000 DTS Export to create a fixed length flat file, the data rows are delimited by carriage return-line. Which means that when I open the flat file in a text editor like UltraEdit or WordPad, the data rows are broken out nicely (row ends at the max row length position and new row starts at position 0). But when I use SSIS to create the file, the whole file is displayed as one line in WordPad. The data rows don't end at the max row lenght position in ultraEdit neither. From Flat File Connection Manager's Preview page, I can see the data rows are displayed properly. Now I wonder if the flat file destination is a true fixed length file.
View Replies !
Some Info On Setting Row Delimiter Values In Package Configurations
I just spent the past 3 or 4 hours trying to figure out how i could represent carriage returns and line feeds properly in package configuration variables that an SSIS package can properly handle, so i figured I'd post my results in case anyone else has struggled with this. Initially, I thought I would be able to just represent the delimiter as the same value that you can pick from in the flat file connection manager dialog, i.e. {CR}{LF}, {CR}, {LF}, etc. nope. Then I decided to try actually storing the characters as hex, i.e. _x000D__x000A_ for {CR}{LF}. nope. After scanning the .dtsx packages for a while, I finally found something that works: you have to represent the curly brackets in hex, and you can simply use the CR or LF string literals. so if you wanted to have a row delimiter that was {CR}{LF}, you would need to set your variable to: _x007B_CR_x007D__x007B_LF_x007D_ Hope this saves some people the pain I had to endure.
View Replies !
Flat File Source - How To Configure An Appropriate Column Delimiter?
Good day everyone, I have a package that reads data from a CSV file, transforms it and finally loads it in a destination DB table. My current problem lies in the parsing of the input flat file. I shall illustrate it using a small example. Source File: P;Product-1;Short Description for product 1 P;Product-2;Short Description for product 2 Problem: I configured the flat file connection manager to use semicolon as the column separator. But then I have received some sample flat files where I found that the semicolon might be sometimes used as content of a column data. Possible Solutions: I have thought about 3 different solution and I would like to get your feedback and recommendations about them. Alternative 1: Use a complex column delimiter, which wouldn't be used in the data. Example: P#~#Product-1#~#Short Description for product 1 P#~#Product-2#~#Short Description for product 2 Question 1: - Is it possible to define such a customized column delimiter for the Flat File Connection Manager? - If yes, how can I do this? Alternative 2: Use double quotes around the data, which the Flat File Source Adapter must somehow recognize and trim before pushing the data down the Data Flow. Example: "P";"Product-1";"Short Description for product 1" "P";"Product-2";"Short Description for product 2" Question 2: - Is it possible to configure the Flat File Source Adapter to work as described? - If yes, how can I do this? Alternative 3: Use a Script Component and write the needed code for parsing the Flat File. Question 3: - Do you have further suggestions/ideas for solving this parsing problem? Thanks in advance and my regards, Samar
View Replies !
How To Specify A Special Character As A Row Delimiter In The Flat File Connection Manager?
I have a gazillion text files, each with dozens of records. The fields are pipe-delimited, the end of each record is marked by ASCII char code 28. example: 05|11900307|1|CO|Gervais|Neil|NG8880|F|540|0|0|0|T|0|||F|||F 05|11900307|2|AO|MARSHALL|BRAD|BM7843|F|510|60|0|0|T|0|||F|||F The " " is the Chr(28). The line above should parse into 2 rows... **** time passes **** An interesting discovery - if I paste that " " directly into the row delimiter box, without quotes or brackets, I get two records! So I found at least one answer. Is there another? TIA
View Replies !
Problem With SQL Server 2000 DTS Package:Column Delimiter Not Found
I am trying to import data from a text file to SQL database through a DTS package. i am always getting "Column Delmimeter not found" error at record number 8000. i copied the 8000th record into the beginning of the file to test if there is any problem with data but still i got the error at 8000 th record. i know it was a problem before and was fixed with sp1. i installed SP4 and still gettting the same error.
View Replies !
Overriding Flat File Connection Manager Delimiter From A Script Task
Hi, I am trying to figure out how to override the column delimiter from a script task for a flat file connection. Before outputing the data into a file, I have a variable which contains the delimiter to be used... but as it seems that this property cannot be changed through an expression, I assume it has to be done throught code... However, the connectionmanager object doesnt seem to have the delimiter as an available property. Any help would be appreciated
View Replies !
How To Filter Out The Items Between The Delimiter &";&"?
Hello, Inside a column I have this result: Record 1: Sales;Admins Record 2: ;Sales;Admins Record 3: Sales; Record 4: Admins; You can see the delimiter ";", it can be everywhere. Now I want to delete "Sales". Therefor I have to search where the "Sales" is. (records) After that I want to delete the "Sales". If I delete it the record may not have 2 or more delimiters after each other, like here: Record 1: ;Admins (good, better is to remove the delimiter also) Record 2: ;;Admins (bad) Record 3: ;(good, better is to remove the delimiter also) Record 4: Admins; Can somebody help me how to build this query? Thanks!
View Replies !
|
TRACKER
