如何在本地数据库中导入多个csv文件？ [英] How to import multiple csv files in local DB?

问题描述

大家好，



我需要将多个文件从文件夹导入到本地数据库表中。列结构相同。每个文件的行数从25到25万不等。



哪个工具可以用来更快地完成它？喜欢Sql server，oracle或Mysql



请发表您的意见。



在此先感谢



我尝试了什么：



我试过MySQL。只是从向导导入，没有使用任何代码。它死得很慢

Hi All,

I have a requirement of importing multiple files from a folder into a local DB table. column structures are same. Row count varies from 25 k to 2 lakhs in each file.

which tool can be used to do it faster? like Sql server, oracle or Mysql

Please post your opinions.

Thanks in Advance

What I have tried:

I tried MySQL. just import from wizards, have not used any code. It was dead slow

推荐答案

虽然SQL服务器上没有批量复制和其他批量导入选项，但您可以将CSV格式的文件导入数据库使用SQL Server Management Studio。



首先，在数据库中创建一个表格，您将导入CSV文件。创建表后：

1.使用SQL Server Management Studio登录数据库。

2.右键单击数据库，然后选择任务 - >导入数据...

3.单击下一步>按钮。

4.对于数据源，选择平面文件源。然后使用浏览按钮选择CSV文件。花点时间配置数据导入，然后单击下一步>按钮。

5.对于Destination，选择正确的数据库提供程序（例如，对于SQL Server 2012，您可以使用SQL Server Native Client 11.0）。输入服务器名称;选中使用SQL Server身份验证，输入用户名，密码和数据库，然后单击下一步>按钮。

6.在选择源表和视图窗口中，您可以在单击下一步>之前编辑映射。按钮。

7.立即运行并单击下一步>按钮。

8.单击完成按钮运行包。

While bulk copy and other bulk import options are not available on the SQL servers, you can import a CSV formatted file into your database using SQL Server Management Studio.

First, create a table in your database into which you will import the CSV file. After the table is created:
1.Log in to your database using SQL Server Management Studio.
2.Right click the database and select Tasks -> Import Data...
3.Click the Next > button.
4.For Data Source, select Flat File Source. Then use the Browse button to select the CSV file. Spend some time configuring the data import before clicking the Next > button.
5.For Destination, select the correct database provider (e.g. for SQL Server 2012, you can use SQL Server Native Client 11.0). Enter the Server name; check Use SQL Server Authentication, enter the User name, Password, and Database before clicking the Next > button.
6.In the Select Source Tables and Views window, you can Edit Mappings before clicking the Next > button.
7.Check Run immediately and click the Next > button.
8.Click the Finish button to run the package.

--------------------------------------------------------------------
SQL Query for importing CSV File 

To handle rows which aren't loaded into table because of invalid data or format, could be handle using ERRORFILE property, specify the error file name, it will write the rows having error to error file. code should look like.

BULK INSERT SchoolsTemp  --Table Name
    FROM 'C:\CSVData\Schools.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    ERRORFILE = 'C:\CSVDATA\SchoolsErrorRows.csv',
    TABLOCK
    );

这篇关于如何在本地数据库中导入多个csv文件？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！