Click here to Skip to main content
15,896,453 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a .txt file containing fixed length data of 533 in width e.g my file contain aaaaaaa....aa (junk data taken as example) now I want to store this data in table
eg Table1 (c1 char(1)
C2 Char(10),
C3 char(15),
C4 char(1),
....
Cx char(10)
)

Now how to split this flat file data so that (a) goes c1 of length 1 , 10 (a) goes to c2 column of length 10.

I tried using Flat file as source in SSIS but it only allow 285 width of column for fixed length data but my data is of 533 in width.

I was thinking of putting this data in temp table and then separate data using , and putting in table1.
But I need help with writing query to do this?
Posted

By default, the Flat File connection manager sets the length of string columns to 50 characters. In the Flat File Connection Manager Editor dialog box, you can evaluate sample data and automatically resize the length of these columns to prevent truncation of data or excess column width. Also, unless you subsequently resize the column length in a Flat File source or a transformation, the column length of string column remains the same throughout the data flow. If these string columns map to destination columns that are narrower, warnings appear in the user interface. Moreover, at run time, errors may occur due to data truncation. To avoid errors or truncation, you can resize the columns to be compatible with the destination columns in the Flat File connection manager, the Flat File source, or a transformation. To modify the length of output columns, you set the Length property of the output column on the Input and Output Properties tab in the Advanced Editor dialog box.

If you update column lengths in the Flat File connection manager after you have added and configured the Flat File source that uses the connection manager, you do not have to manually resize the output columns in the Flat File source. When you open the Flat File Source dialog box, the Flat File source provides an option to synchronize the column metadata.
ref:
http://msdn.microsoft.com/en-us/library/ms140266.aspx[^]

If you are not comfortable with it you can create your own text file reader with Script component: follow: http://msdn.microsoft.com/en-us/library/ms136060.aspx[^]
C#
using System.IO;
public class ScriptMain:
    UserComponent

{
    private StreamReader textReader;
    private string exportedAddressFile;

    public override void AcquireConnections(object Transaction)
    {

        IDTSConnectionManager100 connMgr = this.Connections.MyFlatFileSrcConnectionManager;
        exportedAddressFile = (string)connMgr.AcquireConnection(null);

    }

    public override void PreExecute()
    {
        base.PreExecute();
        textReader = new StreamReader(exportedAddressFile);
    }

    public override void CreateNewOutputRows()
    {

        string nextLine;
        string[] columns;

        char[] delimiters;
        delimiters = ",".ToCharArray();

        nextLine = textReader.ReadLine();
        while (nextLine != null)
        {
            columns = nextLine.Split(delimiters);
            {
                MyAddressOutputBuffer.AddRow();
                MyAddressOutputBuffer.AddressID = columns[0];
                MyAddressOutputBuffer.City = columns[3];
            }
            nextLine = textReader.ReadLine();
        }

    }

    public override void PostExecute()
    {

        base.PostExecute();
        textReader.Close();

    }

}
 
Share this answer
 
Use OPENROWSET()[^] and SUBSTRING()[^] as is shown in an example:

SQL
--create temporary table
CREATE TABLE #tt ([longText] NVARCHAR(1000))

--insert long data in to temporary table
INSERT INTO #tt
SELECT *
FROM OPENROWSET ('MSDASQL', 'Driver={Microsoft Text Driver (*.txt; *.csv)};DBQ=E:\;', 'SELECT * FROM test533.txt');

--get data from temporary table
SELECT SUBSTRING([longText], 1, 1) AS C1, SUBSTRING([longText], 2, 10) AS C2, SUBSTRING([longText], 11, 15) AS C3, 
		SUBSTRING([longText], 25, 1) AS C4, SUBSTRING([longText], 26, 4) AS C5, SUBSTRING([longText], 30, 503) AS C6
FROM #tt

--remove temporary table
DROP TABLE #tt
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900