如何从.txt文件导入数据以填充SQL Server中的表 [英] How to import data from .txt file to populate a table in SQL Server

问题描述

每天，带有客户数据的PPE.txt文件(用分号分隔并且始终具有相同的布局)存储在特定的文件目录中.

Every day a PPE.txt file with clients data, separated by semicolon and always with the same layout is stored to a specific file directory.

每天都有人必须根据此PPE.txt从我们的数据库中更新特定的表.

Every day someone has to update a specific table from our database based in this PPE.txt.

我想通过SQL脚本自动化此过程

I want to automate this process via a SQL script

我想将是一种解决方案，就是通过脚本将文件从此.txt文件导入到创建的表中，然后执行更新.

What I thought would be a solution is to import the data via a script from this .txt file into a created table, then execute the update.

到目前为止，我有

IF EXISTS (SELECT 1 FROM Sysobjects WHERE name LIKE 'CX_PPEList_TMP%')
   DROP TABLE CX_PPEList_TMP
GO

CREATE TABLE CX_PPEList_TMP  
(
    Type_Registy CHAR(1),
    Number_Person INTEGER,
    CPF_CNPJ VARCHAR(14),
    Type_Person CHAR(1),
    Name_Person VARCHAR(80),
    Name_Agency VARCHAR(40),
    Name_Office VARCHAR(40),
    Number_Title_Related INTEGER,
    Name_Title_Related VARCHAR(80)
)

UPDATE Table1
SET SN_Policaly_Exposed = 'Y'
WHERE Table1.CD_Personal_Number = CX_PPEList_TMP.CPF_CNPJ
  AND Table1.SN_Policaly_Exposed = 'N'

UPDATE Table1
SET SN_Policaly_Exposed = 'N'
WHERE Table1.CD_Personal_Number NOT IN (SELECT CX_PPEList_TMP.CPF_CNPJ 
                                        FROM CX_PPEList_TMP)
  AND Table1.SN_Policaly_Exposed = 'Y'

我知道我没有给多少钱，但这是因为我还没有多少钱.

I know I haven't given much, but it is because I don't have much yet.

我想通过脚本用PEP.txt文件中的数据填充CX_PEPList_TMP临时表，因此我只需执行此脚本即可更新数据库.但是我不知道在我的研究中找不到我无法使用的任何命令.

I want to populate the CX_PEPList_TMP temp table with the data from the PEP.txt file via a script so I could just execute this script to update my database. But I don't know any kind of command I can use neither have found in my research.

提前谢谢！

推荐答案

使用OPENROWSET

您可以使用 OPENROWSET 选项(首先必须启用临时查询)

SELECT * FROM OPENROWSET('MSDASQL',
'Driver={Microsoft Text Driver (*.txt; *.csv)};
DefaultDir=C:\Docs\csv\;',
'SELECT * FROM PPE.txt')

使用OLEDB提供程序

SELECT 
    * 
FROM 
OPENROWSET
        ('Microsoft.ACE.OLEDB.12.0','Text;Database=C:\Docs\csv\;IMEX=1;','SELECT * 
FROM PPE.txt') t

使用大容量插入

您可以将文本文件数据导入到临时表并从中更新数据:

Using BULK INSERT

You can import text file data to a staging table and update data from it:

BULK INSERT dbo.StagingTable
FROM 'C:\PPE.txt'
WITH 
  (
    FIELDTERMINATOR = ';', 
    ROWTERMINATOR = '\n' 
  )

这篇关于如何从.txt文件导入数据以填充SQL Server中的表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！