Tracking Forums, Newsgroups, Maling Lists
Home Scripts Tutorials Tracker Forums
  Advanced Search
  HOME    TRACKER    MS SQL Server






SuperbHosting.net & Arvixe.com have generously sponsored dedicated servers and web hosting to ensure a reliable and scalable dedicated hosting solution for BigResource.com.







How To Import In Special Character Delimited Text File By Using SSIS ?


Hi,
 
I would like to know how to import in the custom delimited text file by using SSIS.
For example, instead by using tab or comma delimited, I use this character : '¶'
The reason is the delimited format that SSIS provided is too common such as colon, semi colon, tab, comma and pipeline.
I have the data that the user also key in the pipeline there. So I am thinking to separate the field by using this special character, but cannot see if there is anyway to import in by using SSIS.
 
Please help to share the solution on this :
 
A¶B¶C
1¶2¶3
 
thanks
best regards,
Tanipar
 
 


View Complete Forum Thread with Replies
Sponsored Links:

Related Messages:
Field Delimited By A Special Character
Hi All,
When using bcp, you delimit a field by the switch -t .. -t -- tab delimited,etc..
Is it possible to define a special character? In this case this special character is
formed when you do 'select CHAR(1)' ... it generates the  .
Can we do something like -t1 ??? or -t01 ....
Thanks in advance.
David

View Replies !   View Related
Import A CSV Delimited Text File Into A Table
Hi,

Could you help me to write a script to import a CSV delimited text file into a sql server table.?

Thanks,

 

carlos

 

View Replies !   View Related
Import Into Comma Delimited Text File (very Urgent)
I want to join differnet tables and import the data into comma delimited text file. There will be lot of checks like if then else to manipulate data. I want to use stored procedure but don't know how to output to text file. Is there any utility which can be used in stored procedure. In future this will be run as an automated job.
Thanks in advance.

View Replies !   View Related
Saving Text With Special Character Into SQL Express Table
Hi, fellows!

 

When I need to save a text into a SQL Express table I use the character ' to encote the text. (ex:  'myText' )

 

How to procedure when the text has already the character ' ? (ex: how to enconte the text  Color's car ) ?

 

Many thanks to any kind of help!

View Replies !   View Related
Character Delimited BCP File
Is there a faster way to create my pipe delimited BCP file, besides from creating a format file?  Actually, my problem is that I am having issue with the file.  It looks perfect, like:
 



Code Snippet
Marie|32|brown|single
Gay|33|black|married
 
 
But when I load it to DataStage it puts the entire row as one column.  I already specified the | as the delimiter in DataStage.  I think the issue is from the column collation.  If my data is as simple as my example above, what column  collation should i use for the format file? Currentyl, i have something like:
 



Code Snippet8.0
4
1  SYBCHAR     0   4       "|"       1     emp_id               ""
2  SYBCHAR     0   4       "|"       2     emp_cand_id          ""
3  SYBCHAR     0   4       "|"       3     emp_statusid         ""
4  SYBCHAR     0   4       "
"    4     emp_type        SQL_Latin1_General_CP1_CI_AS
 
 I generated this through prompts given by BCP for each column. Then I changed the 2nd column values all to SYBCHAR and 0 prefix length..

View Replies !   View Related
Special Character Problems With SSIS Data Reader Using ODBC Driver For DB2
 

Hi,
in SSIS I read data from a DB2 database on AS400 using the Client Access ODBC Driver for DB2 from IBM and write it to a SQL Server database. Since it does not work using the odbc driver as data source directly, I use a data reader component with .net providersodbc.
Some special characters were not translated correctly when read from DB2. They show up as ? in the SQL Server target table.
 

I tried to change the client locale in the ODBC connection properties but it did not help me. I tried changing all other settings in odbc but it still does not work.

 
In dts I could source all the data without this problems and good speed using the same nodbc driver.

 
The OLEDB Providers delivered with SSIS do not work in SSIS or I am too stupid to configure them correctly. They are even too slow as I explained above.

 
I cannot use the MS OLEDB Provider for DB2, because it works only in Enterprize Edition and we only have the Standard Edition.

 
Thus, only using Client Access ODBC Driver for DB2 with net providersodbc (as bridge) is performant enough and works on Itanium. But how to work around the problem with the special characters?

 
Best regards,
Stefoon

View Replies !   View Related
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 !   View Related
Best Practice For Ragged Delimited Text Import
I have tab delimited text files which may have optional fields (meaning they can be not present at all) to the right of the required fields that I care about.  It would appear that using a Flat File Connection with Delimited Format (tab) set will choke if it is initially configured with a file that has something like:
 
data data data

 
and it then encounters
 
data data data optionaldata

 
It chokes.  I know this could be parsed line by line, but that seems silly.  It seems like there should be a way to ignore columns beyond a certain point (e.g. Format "Delimited Ragged Right").
 
Is there some way to do this with a directly with a flat file connection?
 
Thanks,
 
--Andrew

View Replies !   View Related
SSIS Import Text File To SQL Server With Nulls?
I have a text file I am trying to import into SQL Server using OLEDB connection.

It's a fixed field text file, ragged right format. One of my columns maps to a numeric column in the DB. In some spots in the file, it is blank, in others there is actual numeric data.

I can't get it to import. If I set the text file column to numeric, I get an error "That value could not be converted because of a potential loss of data." If I set the text file column to string, I get a similar error from the OLE DB provider, "Invalid character value for cast specification"

I have tried telling it to retain nulls in the data flow and the other way as well. Can someone tell me what I am doing wrong?

View Replies !   View Related
Import Fixed Length Text File To Sql Server Using SSIS
 

what is the best way to import fixed length text file to sql server using SSIS?
 
I was trying to using text file source and ole db destination..but since the text file has no columns and have different length per column and per line( it show only one column becasue it all concatnated), I can not map it to destination column..
 
How can I import it?
 
Here is the example of text file ( fixed with row delimeter)that i need to import to different columns...
 
 

010000000000000000001164.00023 YV

02004101 1 2008-04-OLL 43456 0000000001 2008-04-08

030000100000000000000000000007.00

047890    7556 YYU 779
 

View Replies !   View Related
Trying To Import Delimited File
I am brand spanking new to SQL and just learning all of it'sintricacies and pecularities via the OJT route. I'm tring to import acomma delimited file into a table so I can play inquiries with somefamiliar looking data. It looks like it works for a while and then Iget 2 messages.-Validating (Error)MessagesError 0xc00470fe: Data Flow Task: The product level is insuffucientfor component "Source - phyflat"(1)(SQL Server Import and Export Wizard)Error 0xc00470fe: Data Flow Task: The product level is insuffucientfor component "Data Conversion 1"(151).(SQL Server Import and Export Wizard)What is my problem and how can I fix it?

View Replies !   View Related
Best Way To Import ASCII Tab Delimited File Every Week (5,000 Records)...
I need to import an ASCII tab delimited file that has roughly 5,000 recordsonce a week into a SQL Server table. I have researched BCP and it seemslike the way to go. Am I headed in the right direction?Thanks in advance,James

View Replies !   View Related
Comma Delimited Text File
Hi I'm pretty new to using Microsoft Visual C# .NET and I want to upload a comma delimited text file from my local machine into a table in an sql server database through a web app. How would I go about programming this and what controls do I need? Any help would be much appreciated. Thanks in advance.

View Replies !   View Related
COMMA Delimited TEXT FILE
Hi,On SQLServer 2000, I have a table with a following structure:MYTABLEcol1 char,col2 date,col3 numberMy Objective:------------Externally (from a command line), to select all columns and write theoutput into a file delimited by a comma.My method:---------1. Probably will use OSQL or BCP to do this.2. Use the following syntax:select RTRIM(col1) +','+ RTRIM(col2) +','+ RTRIM(col3)from MYTABLE;My 3 Problems:-------------1) If there is a NULL column, the result of concatenating any value withNULL, is NULL. How can I work around this? I still want to record thiscolumn as null. Something like say from the example above, if col2 isnull, would result to: APPLE,,52) The time format when querying the database is: 2003-06-24 15:10:20.However, on the file, the data becomes: 24 JUN 2003 3:10PM. How can Ipreserve the YYYY-MM-DD HH:MM:SS format? Notice that I also lost theSS.3) Which utility is better? BCP or OSQL?For OSQL, it has a "-s" flag which gives me the option of putting acolumn separator. But the result is:"APPLE ,14 JUN 2003 , 5"I don't need the extra space.While for BCP, there is no column separator flag.You will notice from my inquiry above that my background in SQLServer isnot very good.Thanks in Advance!!RegardsRicky*** Sent via Developersdex http://www.developersdex.com ***Don't just participate in USENET...get rewarded for it!

View Replies !   View Related
Reading Tab Delimited Text File
Is there anyway Sql Server reads a "Tab Delimited Text File" and Compare each record with the Column in a table..

my question is..

I've a Country_Code table which has 3 letter Country Code and the Actual Country names are listed in a Tab Delimited Text File "Country Data" with Country Code and Country Name, how do i read each record and compare to get the Actual Country Name for Display.

any ideas/suggestions.

thanks

View Replies !   View Related
Export As Tab Delimited Text File
 

Hi,

I am trying to export as a tab delimited text file. For that I have changed my config file as :



<Extension Name="TXT" Type="Microsoft.ReportingServices.Rendering.CsvRenderer.CsvReport,Microsoft.ReportingServices.CsvRendering">
<OverrideNames>
 <Name Language="en-US">TXT (Tab Delimited Text File)</Name>
</OverrideNames>
<Configuration>
<DeviceInfo>
 <FieldDelimiter>&#9;</FieldDelimiter>
 <Extension>TXT</Extension>
 <Encoding>ASCII</Encoding>
 <NoHeader>true</NoHeader>
 </DeviceInfo>
</Configuration>
</Extension>

 
I got this code from another one of the MSDN forms. When I run the report and try to export using this format, it still gives me a csv file instead of tab delimited file.
 
Can someone please help me fix this code so I can get tab delimited text files.
Thanks a lot,
-Rohit

View Replies !   View Related
Can MSSQL Load A Tab Delimited Text File?
Hi all...I would like to know if SQL SERVER can load a tab delimited text file.If yes, how?A search on the web did not return me the "load data" command as mysqlor other.Thank you all.

View Replies !   View Related
Export Data Into Text File Using ç Delimited
Hi,
I need to export data from SQL server 2000 database into text file uisng ç Delimited. Because my destination database will be teradata. Could you let me know if you have any method for this.
Thanks

View Replies !   View Related
2 Sql Tables To A Text File With Comma Delimited
 

hi ,

I have 2  sql tables. 1 is the header table and another is the detail table. How can I have the header record being appended in the text file and then have the detail records being appended to a same text file again with comma delimited ?

View Replies !   View Related
Load Data From Comma Delimited Text File
Hello, i need to load some data from a long comma delimited text file, How can a i do that, using t-sql?, thanks for your help!!!!!

View Replies !   View Related
Issues Importing A Text File (tab Delimited) To A SQL Table
I have a text file I am trying to import to a table. This text file is in a tab delimited format. I am using DTS to import the data to a new table I made. The fields are varchar and are set to allow nulls & allow 8,000 characters per field.

The error I am getting is that the data exceeds the allowed amount (or something like that) in col4.

Now I have checked everything in column 4 and nothing exceeds 5,000 spaces/characters combined. I have checked the entire sheet (in excel) for that fact, and there is not one single column/row/cell that exceeds 5,000 spaces/characters combined.

What the heck could be causing SQL to tell me I am trying to import too much data in one column when there is nothing that even comes close to 8,000 characters & spaces combined?

View Replies !   View Related
Bulk Load From Text Delimited File To SQL Table
Hi,
 
I am new to SSIS but i have avg working knowledge in sql. 
My problem is as follows ,I have a text pipe dilimited file in some folder and the number of columns and the name of the column is not consistant. It can have N number of column and it can have any column names. I need to load this text file data into a sql table. All that i want is to load this file to SQL Database with some temp name. Once i get the table in SQL Database, i can match the column names of both taget table and this temp table and only push those column which matches with the target table. For this i can frame Dynamic SQL. This part is clear to me.
 
Now the problem is , I developed a SSIS pacakge to push the text file to SQL Table. I am able to do this. But if i change the column names or added new column SSIS is not able to push the new columns. Is this functionality available in SSIS, is it can be dynamic like this?
 
I hope i am clear with my prob... if need any clarification please let me know
 
thanks in advance
 
Mike

View Replies !   View Related
Exporting Data To A Comma Delimited Text File, FORMAT Function
Hi. Im new to SQL and I need to export a SQL table as a comma delimited text file which is straight forward. However two of the fields are integers and I need these to be right justified with zero's.
In Access I would use something like format(columnname, "00000000") to get it to work, but SQL Server doesn't like this.
How can I do this?

View Replies !   View Related
Problem Importing Csv Delimited Text File Into A Sql Server 2005 Table
I am using the Bulk Insert command and trying to import a CSV delimited text file into a table and I am having problems with the quote field delimiters ", "  The command below works but it takes in all the "" quotes as well and the field delimiter comma , works only if the commas are the separators only.  If I have a comma within a address field for example then the data gets imported into the wrong fields.  What can I use to identify that the text qualifier is ".  I don't see where I can use the bulk insert command to determine this. Is there another command that I can use or am I using this command incorrectly.  I thank you in advance for any response or suggestion you may have.

BULK INSERT AdventureWorks.dbo.MbAddress

FROM 'a:mbAddress.txt'

WITH (

DATAFILETYPE = 'char',

FIELDTERMINATOR=',',

ROWTERMINATOR='',

CODEPAGE = '1252',

KEEPIDENTITY,

KEEPNULLS,

FIRSTROW=2)

Here is a sample ascii file I am importing as well you can see that 6330 has a extra comma in the address line.

"AddressAutoID","Memkey","Type","BadAddress","Address1","Address2","Address3","City","State","Zip","Foreign","CarrierRoute","Dpbc","County","CountyNo","ErrorCode","ChangeDate","UserID"
6317,26517,1,0,"1403  W.  Kline  Ave","","","MILWAUKEE","WI","53221","","",0.00,"MILWAUKEE",79,"",1/25/2006 0:00:00,"admin"
6318,26225,1,0,"501  Dunford  Dr","","","BURLINGTON","WI","53105","","",0.00,"RACINE",101,"",1/25/2006 0:00:00,"admin"
6319,20101,1,0,"2115  Cappaert  Rd   #35","","","MANITOWOC","WI","54220","","",0.00,"MANITOWOC",71,"",1/25/2006 0:00:00,"admin"
6320,23597,1,0,"728  Woodland Park Dr","","","DELAFIELD","WI","53018","","",0.00,"WAUKESHA",133,"",1/25/2006 0:00:00,"admin"
6321,23392,1,0,"7700  S.  51st  St","","","FRANKLIN","WI","53132","","",0.00,"MILWAUKEE",79,"",1/25/2006 0:00:00,"admin"
6322,26537,1,0,"W188 S6473 GOLD DRIVE","","","MUSKEGO","WI","53150","","",0.00,"WAUKESHA",133,"",1/26/2006 0:00:00,"admin"
6323,25953,1,0,"3509  N.  Downer Ave","","","MILWAUKEE","WI","53211","","",0.00,"MILWAUKEE",79,"",1/26/2006 0:00:00,"admin"
6324,19866,1,0,"10080  E.  Mountain View Lake Rd.    #145","","","SCOTTSDALE","AZ","85258","","",0.00,"MARICOPA",13,"",1/27/2006 0:00:00,"admin"
6325,25893,1,0,"W129  N6889  Northfield Dr. Apt 114","","","MENOMONEE FALLS","WI","53051-0517","","",0.00,"WAUKESHA",133,"",1/27/2006 0:00:00,"admin"
6326,26569,1,0,"8402    64th  Street","","","KENOSHA","WI","53142-7577","","",0.00,"KENOSHA",59,"",1/27/2006 0:00:00,"admin"
6327,24446,4,0,"83  Sweetbriar  Br","","","LONGWOOD","FL","32750","","",0.00,"SEMINOLE",117,"",1/30/2006 0:00:00,"admin"
6328,19547,1,0,"4359 MERCHANT AVENUE","","","SPRING HILL","FL","34608","","",0.00,"HERNANDO",53,"",2/8/2006 0:00:00,"admin"
6329,26524,1,0,"264 Lakeridge Drive","","","OCONOMOWOC","WI","53066","","",0.00,"WAUKESHA",133,"",2/10/2006 0:00:00,"admin"
6330,23967,1,0,"3423 HICKORY ST","100 Tangerine Blvd., Brownsville, TX 78521-4368","Texas Phone Number: 956-546-4279","SHEBOYGAN","WI","53081","","",0.00,"SHEBOYGAN",117,"",2/15/2006 0:00:00,"admin"
6331,25318,1,0,"3960 S. Prairie Hill Lane Unit 107","","","Greenfield","WI","53228","","",0.00,"MILWAUKEE",79,"",2/20/2006 0:00:00,"admin"
6332,24446,1,0,"83  Sweetbriar  BR","","","LONGWOOD","FL","32750","","",0.00,"SEMINOLE",117,"",2/21/2006 0:00:00,"admin"
6333,26135,1,0,"P.O. Box 8   127  Main Street","","","CASCO","WI","54205","","",0.00,"KEWAUNEE",61,"",2/21/2006 0:00:00,"admin"


 

 

View Replies !   View Related
SSIS: Populate Database From A Space Delimited File
The problem is that I have (for example) following data

AAAA AA ALH
B BBB MIL
CCCCCC CAC Q
D D P

in space delimited file and i want to populate a database using SSIS and it is not working. Any suggestions will be appreciated.


P.S. the only way to recognize colums is through spaces b/w the data.

View Replies !   View Related
How To Eliminate Extra Tab In The Tab Delimited File When Transfering To Db Table Using SSIS
 

Hi,
 
I have a tab delimited file I need to transfer to a table using SSIS.  Columns can have NULL value and there might be extra tabs in each row also.  How can I do this?  Maybe fuzzy lookup?
 
Thanks

View Replies !   View Related
New Line Character In The Text File.
I am writing a string  to a text file using the below code:

DECLARE @MyText nvarchar(500)
SET @MyText = 'type This is my text >> c:MyLog.txt'
exec master..xp_cmdshell @MyText


What I want to know is. how do I write multiple lines to MyLog.txt without having to call out xp_cmdshell each time I want a new line in my log?  Do I use newline character in @MyText like SET @MyText = 'type This is my text line 1</n>This is line2 >> c:MyLog.txt'
 
Expected output:
 
type This is my text line 1
This is line2

View Replies !   View Related
SSIS: Populate Data From Space Delimited File To SQL Server 2005
The problem is that I have (for example) following data

AA AA LH
BB BB ML
CC CC QA
DD DD PS

in space delimited file and i want to populate a database using SSIS and it is not working. Any suggestions will be appreciated.

View Replies !   View Related
Special Character
How can I insert by asp a string containing 'That's the stringAsp code:SQL = "insert into tablename (columnA) values ('" & variable & "')"The problem is when variable contains a ' (single quote), it stops thestring definition and get an error.Can I do something?Thanks--Posted via Mailgate.ORG Server - http://www.Mailgate.ORG

View Replies !   View Related
Special Character
Hi,

i have the following problem:

I like to extract all special characters in one table with many fields.
How can I handle this without using 'replace' for each field and many characters ?

Thx. for ur suggestions.

dajm

View Replies !   View Related
How To Export 'text' Data Having &> 8192 Character Into File
Hi,

I have a table with text data with more than 8192 character in it. When I tried to select or into a file I could only see the first 8192 after setting 'Max character = 8192' properties. Please tell me how to select the entire data.

Thanks
John Jayaseelan

View Replies !   View Related
Special Character;strange
I had to update pricelist at local-db on the basis of prices in another SQL by matching the description. When i JOIN the tables, no record gets displayed, but the descrptions at both tables were exactly the same i believed. Took a lot of time in identifying the discrepancy; the data was like this:

CREATE TABLE #ABC (price money, DESCRIPTION VARCHAR(200))
GO
INSERT #ABC SELECT 19999,'SQL2000
'
GO
SELECT * FROM #ABC WHERE DESCRIPTION ='SQL2000'
--(0 row(s) affected)
SELECT * FROM #ABC WHERE LTRIM(RTRIM(DESCRIPTION))='SQL2000'
--(0 row(s) affected)
select * from #ABC WHERE REPLACE(DESCRIPTION,'
','')='SQL2000'
--(1 row(s) affected)
GO
DROP TABLE #ABC
:rolleyes: I believe, ENTER has some value within a field.

View Replies !   View Related
Replace Special Character
I'm trying to remove the special character ÿ from a varchar

select replace(my_col,'ÿ',' ')

this works, but the problem is that it also seems to replace the 'normal' y

(Database is case sensitive)

Why is that?

View Replies !   View Related
Special Character Replacement
shailendra writes "Hi,everbody
lets come to my problem.I have one variable of type "ntext" which contain character "". i want to remove it or replace it with a blank character.

very urgent.....
waiting for the reply
shail"

View Replies !   View Related
Would Special Character Cause Insertion Problem?
Hi,SQL env: sql server 2000Target column of insertion: varchar(15)Case, a var is made up of a character of the following characters(random selection):A,B,C,D,E,$,!,%,^,F,G,H,I,J,K,L,M,N,O,P,Q,R,S,T,U, V,W,X,Y,Z,&,*,(,)and a few numbers (random selection), and thenvar = ran1&ran2I'm experiencing intermit sql errorIs it because I did not include the string with quotes likevar = "ran1 & ran2"or the first random seletion may include special character and thatmay cause insertion error randomly?Probably the former is more likely. And I've added quotes for thevar (programming language shouldn't really matter, int type usuallywithout quotes while strings need quotes).What's your thought?Thanks.

View Replies !   View Related
Special Character '[' In MSSQL 2005
Hi,

I am using MSSQL 2005 with the compatability mode set to 2000.Now, one of my String columns in a table has data in the following format:

ABC[123][XYZ]

Now issuing the following SELECT query in the table does not work :

Select * from TAB1 where col1 like 'ABC[%'

ie, no row gets selected.However, the following query works:

Select * from TAB1 where col1 like 'ABC%'

Looks like '[' is a special character :( Now, how do i work around this so that i can select strings starting with "ABC[".Please help.

Thanks.

View Replies !   View Related
To Find The Special Character Inside A Name
CHALASANI writes "I have a name in my table which is "ra_ja" I want to find the name with the help of the'_' character.

Can you please help with the querry

Sorry with my english
Thank you"

View Replies !   View Related
Special Character In Dynamic TSQL
Hello all
 
 

I am trying to update a colunm with the value (Dynamically). 

 

'UPDATE ' + @TABLE + ' SET '+ @FIELD + '  =  '''+ @VALUE +'''
 
well It works fine until @Value contains quotation for instance @value = O'hare Airport
results in termination of the statement because of single quote after O in O'Hare. Is there any way I can see it works
 
 
Also suppose if its updating a field which can be say 10 charcters long and when @value has say 15 characters, it terminates. Is there anyway i can avoid this.
 
 
 

 

View Replies !   View Related
Patindex To Find Special Character And Next 6 Digits
I am creating a view to pull data for a UPS integration I am doing. I currently have this view where I pull my data from. All is well accept for my PATINDEX,

Currently I have this PATINDEX So when a user puts this into delivery instructions "#999999" UPS_FINAL returns 999999 which is good for me to use. But The PATINDEX will also grab all kinds of neat stuff out of the delivery instructions field when I really only want whatever is after the # sign in the field.

I am new to sql and dont quite understand how this search is working but I think I am in need of a better way to search the field



Code:


WHEN SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) IS NULL

THEN dbo.Address_Table.ups_code

ELSE SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) END AS UPS_FINAL


-------------------------------------------------------------------------------------
What I am looking to accomplish is a SUBSTRING that will search delivery_instructions



Code:


SELECT

TOP (100) PERCENT P21PLAY.dbo.p21_view_oe_pick_ticket.pick_ticket_no,
P21PLAY.dbo.p21_view_oe_hdr.order_no, P21PLAY.dbo.p21_view_oe_hdr.customer_id,
P21PLAY.dbo.p21_view_oe_hdr.ship2_name, P21PLAY.dbo.p21_view_oe_hdr.ship2_add1,
P21PLAY.dbo.p21_view_oe_hdr.ship2_add2,
P21PLAY.dbo.p21_view_oe_hdr.ship2_city,
P21PLAY.dbo.p21_view_oe_hdr.ship2_state,
P21PLAY.dbo.p21_view_oe_hdr.ship2_zip,
P21PLAY.dbo.p21_view_oe_hdr.po_no,
P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id AS Carrier,
P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id AS Supplier,

P21PLAY.dbo.p21_view_oe_hdr.cod_flag,
P21PLAY.dbo.p21_view_oe_hdr.terms,
P21PLAY.dbo.p21_view_oe_hdr.ship2_country,
P21PLAY.dbo.p21_view_oe_hdr.ship_to_phone,
P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
dbo.Address_Table.ups_code,

-----------Looks for special character and returns next 6 spaces as UPS_Shipper----------

SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions, PATINDEX('%[^a-z ]%',

P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6)
AS UPS_Shipper,

------------------Checks view for email address or assigns alternate------------------
(CASE WHEN charindex('@', p21_view_contacts.email_address) > 0 THEN p21_view_contacts.email_address ELSE

'email@domain.com' END) AS alternate_address,

'Y' AS QVN, 'email@domain.com' AS failureaddress,

P21PLAY.dbo.p21_view_contacts.email_address,

------------When carrier_id is not one of these # then Null; else ------------------------------

CASE WHEN P21PLAY.dbo.p21_view_oe_pick_ticket.carrier_id NOT IN (105188, 105191, 105194, 105197, 105200,

105203, 105206, 105209, 105212) THEN NULL

----------------- Looks for special Character in delivery_instructions; if NULL then ups_code;
ELSE return value from delivery_instructions as UPS_Final--------------------

WHEN SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) IS NULL
THEN dbo.Address_Table.ups_code
ELSE SUBSTRING(P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions,
PATINDEX('%[^a-z ]%', P21PLAY.dbo.p21_view_oe_hdr.delivery_instructions) + 1, 6) END AS UPS_FINAL


FROM dbo.Address_Table INNER JOIN
P21PLAY.dbo.p21_view_oe_pick_ticket INNER JOIN
P21PLAY.dbo.p21_view_oe_hdr ON P21PLAY.dbo.p21_view_oe_pick_ticket.order_no =

P21PLAY.dbo.p21_view_oe_hdr.order_no ON
dbo.Address_Table.id = P21PLAY.dbo.p21_view_oe_hdr.customer_id LEFT OUTER JOIN
P21PLAY.dbo.p21_view_contacts ON P21PLAY.dbo.p21_view_oe_hdr.contact_id = P21PLAY.dbo.p21_view_contacts.id

WHERE
(P21PLAY.dbo.p21_view_oe_hdr.completed <> 'Y')
AND (P21PLAY.dbo.p21_view_oe_hdr.delete_flag <> 'Y')
AND (P21PLAY.dbo.p21_view_oe_hdr.will_call <> 'Y')
ORDER BY P21PLAY.dbo.p21_view_oe_pick_ticket.pick_ticket_no



Hope this makes since

View Replies !   View Related
BCP Import Text File With Blank Line At End Of File
I am trying to bcp a text file that is written out of a Javaapplication. The text file has important order information that I needto bcp into a SQL Server 2000 database. The problem is that whenrecords are written out by the Java application the last item in eachrecord has a new line character attached to it so that a new line isstarted for the next record. BCP gives me an error and doesn't importanything if there is this blank line at the end of my text file. If Idelete the last blank line then my text file imports without anyissues. Anyone have any ideas how I can fix this issue?Thanks,Eric

View Replies !   View Related
Problems Character Sets / Special Characters Dot.Net &<-&> Mssql-server
Hi,I come from the "dark side" php/mysql and there often problems withcharacter sets (utf-8, latin...) and storing data in datebase.Exists in the world of dot.net and ms-sql-server similiar problems?To precise: I have to store xml-data in database. Maybe its better toencode (like base64) the strings?Perhaps there are some links to read?Thanks.klaus.

View Replies !   View Related
Multiple Rows Combined Into Onerow And Onecolumn Separated By A Special Character
Hi All :CREATE TABLE TABLEA(Person Varchar(20), Country Varchar(20), SubjectVarchar(20), Type Char(1))INSERT INTO TABLEA VALUES ('Einstein', 'Germany', 'Physics', 'P')INSERT INTO TABLEA VALUES ('Kant', 'Germany', 'Philosophy', 'Q')INSERT INTO TABLEA VALUES ('Kafka', 'Germany', 'Writer' , 'W')INSERT INTO TABLEA VALUES ('Aristotle', 'Greece', 'Philosophy', 'Q')INSERT INTO TABLEA VALUES ('Archimedes', 'Greece', 'Physics', 'P')INSERT INTO TABLEA VALUES ('Homer', 'Greece', 'Writer' , 'W')SELECT * FROM TABLEAI am on SQL 2000.I need an output where i have to have a resultset grouped on Type, butthe results in one row.In the resultset I needTypeP PersonType P Country, Type Q Person, Type Q Country, TypeW Person Type W Country---------------------------------------------------------------------------------------------------------------------Einstein:ArchimedesGermany:GreeceKant:Aristotle Germany:GreeceKafka:HomerGermany:Greece************************************************** *************I have written a puesdo-cursor code to do the same, but if there is away to do as a set operation, that would be greatPlease select as a whole and past in query analyser as the resultsetis all overlaid when i paste in this box.Thank youRS

View Replies !   View Related
DTS Import Of A Text File
I have created a DTS that imports a text file to by data table. I geterrors when ever I run this since there are fields in the table thatare numeric. I understand that I need to create an activeX script toimport those fields. DOes anyone have any guidance?

View Replies !   View Related
Text File Import With DTS
Does anyone know if it's possible to use the wizard or DTS Designer toaccept a source file with the following simplified format:<field1label>: <record1field1value><field2label>: <record1field2value>- - - - - - -<fieldNlabel>: <record1fieldNvalue><field1label>: <record2field1value><field2label>: <record2field2value>etc.i.e. each input record is delimited by {LF}{LF}, and each column by {LF}. Orwill it be necessary to write a Perl script (say) to convert it first into a..csv file?Thanks,Dave--************************************************** **********************Dave Stone e-mail: Join Bytes!Computing Services Telephone: +44 131-650-3314University of Edinburgh Internal ext: 503314Main Library, George Square FAX: 0131-650-3308Edinburgh EH8 9LJ************************************************** **********************

View Replies !   View Related
Text File Import
Quick advice question. I import lots of text files -- many with 50 plus data columns. Few come with a table layout other than perhaps the first row having a set of column names.

When I go to pull them into SQL server the columns default to varchar 8000. Is anyone aware of a tool (as a part of SQL Server or otherwise) that can scan a column of data and recommend a data type and size.

Appreciate any recommendations.

Ray

View Replies !   View Related
DTS Import From A Text File
Hi All,
I'm having a problem in importing an Excel file into SQL server 2000.Here is the scenario with my data.
One of the column has got the mixed data which is putting null's in the SQL server table in some rows.I found in the MSFT Technet that it is a bug in SQL server 7.0/2000.The workaround for it ( according to MSFT ) is to get the data into text file and import into SQL server.
Now the question is , my data contains some currency fields and numeric fields in addition to the char and date fields. When I'm importing the table using DTS wizard , it is failing. I'm trying to use conversion functions like cdate and clng etc . Still the DTS is failing.
What I noticed is when I try to import into a table with data type varchar for all columns, it is working fine.But the data is of no use.
I would appreciate if any one can help me out in solving this problem.
Thanks,
Sammy.

View Replies !   View Related

Copyright © 2005-08 www.BigResource.com, All rights reserved