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

You can import this as a single column and then use a script transform to cycle through your row and break it down into the appropriate columns. There are various examples of doing this relating to uneven / unbalanced / dynamic number of columns...

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/07/handling-flat-files-with-varying-numbers-of-columns.aspx

|||I am a fan of the approach above , but you can also just use a flat file connection manager. Go to the Columns page in the editor, and put the symbol into the Column Delimiter field. That works fine for me. The approach above is usually only necessary when dealing with flat files with missing columns or delimiters.|||Learn something new every day... I figured since it was a drop down that you could only use the values present, I didn't realize you could type there... Let's just say this approach is MUCH easier :-)

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

You can import this as a single column and then use a script transform to cycle through your row and break it down into the appropriate columns. There are various examples of doing this relating to uneven / unbalanced / dynamic number of columns...

http://agilebi.com/cs/blogs/jwelch/archive/2007/05/07/handling-flat-files-with-varying-numbers-of-columns.aspx

|||I am a fan of the approach above , but you can also just use a flat file connection manager. Go to the Columns page in the editor, and put the symbol into the Column Delimiter field. That works fine for me. The approach above is usually only necessary when dealing with flat files with missing columns or delimiters.|||Learn something new every day... I figured since it was a drop down that you could only use the values present, I didn't realize you could type there... Let's just say this approach is MUCH easier :-)

How to import Data from Excel to SQL Server Express

Hi,
please any one is expert in Data Base help me, i would like to import two Data columns from Excel file 2003 instead of two columns in a table in Sql Server Express 2005 , How Can i do that? and what is the way if I have Sql Developer 2005, thank you .

Your query is not clear.. you wish to copy data from Excel to SQL Server Express or You wish to read Excel data from ASP.Net application:

If you want to know, how to read Excel data in ASP.Net Application, visit the follocing web link:

http://weblogs.asp.net/scottgu/archive/2006/05/29/Reading_2F00_Writing-Excel-Spreadsheets-with-ADO.NET.aspx

|||

Hi,

You may try this SQL fonction OPENROWSET, suppose you have an Excel file in the C:\ drive of the SQL Server database, and the data is on a Worksheet called Sheet1 and your two columns are called Column1 and Column2.

INSERT INTO MyTable

SELECT Column1, Column2
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
 'Excel 8.0;Database=C:\MyTable.xls',
 'SELECT * FROM [Sheet1$]')

The first row of your Excel file should coutains the colums names.

 Hope this answer your question. 

Dominic 

|||

hi, exactly i would like to copy two columns of data from Excel file instead of two columns of data in sql express,thank u

How to I retrieve the first value from a table in T-SQL?

I want to do something similar to ExecuteScalar in ADO.net but instead in T-SQL.

Basically I want to do a query that will return the first value in the table queried and put it into a variable. How do I do this?

Thanks in advance.

SELECT

top 1 yourCol

FROM

T1

ORDER

BYyourCol|||Well that returns a table with the first record BUT I wanted the thing returned as a value.

Say I declared a integer variable with

DECLARE @.x, I wanted the integer variable to be equal to the first record which would be an "age" column in the recordset

SELECT age FROM PEOPLE

I can't do @.x=SELECT age FROM people|||

CREATE

PROCEDURE [dbo].[sp__top1Name]

@.myAge

intOUTPUTAS

BEGIN

SET @.myAge=(SELECTtop 1 Agefrom T1ORDERBY Age)

END

In your code, you need to grab this value from stored procedure OUTPUT parameter which is the age you are looking for.