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


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 :


best regards,

View Replies


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.

View Replies View Related

Import A CSV Delimited Text File Into A Table


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



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

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
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

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,

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.


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?


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?



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


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


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 ***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.


View Replies View Related

Export As Tab Delimited Text File


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">
<Name Language="en-US">TXT (Tab Delimited Text File)</Name>

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,

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

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.

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


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


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'





CODEPAGE = '1252',




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

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


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


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?


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

Copyrights 2005-15, All rights reserved