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
how to separate names but i cannot make work in this case. The name field might contain anywhere from only one name with no delimeters to five names with four delimeters. I want to replace the delimeter with a space and reorder the names.
Original data format: Name2/Name1/Name3/Name4/Name5.
Desired data format: Name1 Name2 Name3 Name4 Name5.
Examples of source data
Company ABCDoe/JohnSmith/Jim/EtalJones/Jeff/Jr/& Sally
Bush/Jim/Sr/Etal/Trustee
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
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.
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!
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!
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!
I have a PL/pgSQL Code like this:
[code=" CREATE OR REPLACE FUNCTION public.split_string(text, text)
RETURNS SETOF text
LANGUAGE plpgsql
AS $function$
DECLARE
pos int;
[Code] ....
Its split a String with a delimiter. Like this
[code="select * from split_string('3.584731 60.739211,3.590472 60.738030,3.592740 60.736220', ' ');
"3.584731"
"60.739211,3.590472"
"60.738030,3.592740"
"60.736220""][/code]
My question is how i can save the first result in a temp_array (or table i dont know) so I can get the result and split up the results again with the 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.
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 3 Replies View Relatedhi, 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 1 Replies View RelatedHi,
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
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 1 Replies View Related
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.
The input is like this : xxxxxxxx=yyyyy=key_id=12345xyxyx
I would like to use the right function and get 12345xyxyx alone. The function must search for the first '=' symbol from the right of the word and get the characters from the end till the '=' symbol.
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
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.
I want to extract two strings from xxxxx - yyyyyy separately as xxxxx and yyyyyy. The source always has two strings brought together with a - symbol. How to extract these two strings.
View 4 Replies View RelatedHello 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
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
i am running sql server 2000 on windows 2000. i have a need to export a view and delimit the columns with double quotes as it has imbedded commas in the columns, how do i do this??
View 1 Replies View RelatedI have a date file with no delimiter like bellow
0080970393102312072981103378000004329392643958
0080970393102312072981103378000004329392643958
I just know 5 first number in a line is for example "ID of bank"
or 6th and 7th number in a line is for example "ID of employee"
How can I create a XML format file?
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.
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!!!
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,
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.
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!
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
I have a flat file that is row delimited by x00 x0D x0A. Any ideas on how to specify the row delimiter in the Columns section of the Flat File Connection Manager?
View 2 Replies View RelatedI have a table with a varchar column that is delimited by char(227)+char(228)
i.e.,
OrNo=7807ãäSeqNo=1ãäPStopType=PãäPCity=TillsonburgãäPSt=ONãäPZIP=N4G4J1ãäPAdr1=10 ROUSE STREETãäPAdr2=ãäPLat=42.8625ãäPLon=-80.7267ãäDStopType=DãäDCity=GaffneyãäDSt=SCãäDZIP=29341ãäDAdr1=121 PAN AMERICAN DRIVE
Looking for split function that accepts a multiple character value as a delimiter?
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.
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
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.