如何将数据从.txt存储到表 [英] how to store data from .txt to table

问题描述

我有一个.txt文件，其中包含宽度为533的固定长度数据，例如，我的文件包含aaaaaaa .... aa(以垃圾数据为例)，现在我要将这些数据存储在表中
例如Table1(c1 char(1)
C2 Char(10)，
C3 char(15)，
C4 char(1)，
....
Cx char(10)
)

现在如何拆分此平面文件数据，以使(a)进入长度为1的c1，10(a)进入长度为10的c2列.
我尝试在SSIS中将Flat文件用作源，但它只允许285宽度的列用于固定长度的数据，但我的数据宽度为533.

我当时正在考虑将这些数据放入临时表，然后使用来分离数据，然后将其放入table1.
但是我需要编写查询的帮助来做到这一点吗?

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?

推荐答案

默认情况下，平面文件连接管理器将字符串列的长度设置为50个字符.在平面文件连接管理器编辑器"对话框中，您可以评估示例数据并自动调整这些列的大小，以防止数据被截断或过多的列宽.另外，除非您随后在平面文件源或转换中调整列长的大小，否则字符串列的列长在整个数据流中都保持不变.如果这些字符串列映射到较窄的目标列，则警告会出现在用户界面中.此外，在运行时，由于数据截断可能会发生错误.为避免错误或截断，您可以调整列的大小以与平面文件连接管理器，平面文件源或转换中的目标列兼容.若要修改输出列的长度，请在高级编辑器"对话框的输入和输出属性"选项卡上设置输出列的长度"属性.

如果在添加和配置了使用连接管理器的平面文件源之后，在平面文件连接管理器中更新列的长度，则不必在平面文件源中手动调整输出列的大小.打开平面文件源"对话框时，平面文件"源提供了一个用于同步列元数据的选项.
参考:
http://msdn.microsoft.com/en-us/library/ms140266.aspx [ ^ ]

如果您不满意，则可以使用脚本组件创建自己的文本文件阅读器:请遵循: http://msdn.microsoft.com/en-us/library/ms136060.aspx [

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[^]

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();

    }

}

使用 OPENROWSET() [ ^ ]和

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

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

这篇关于如何将数据从.txt存储到表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！