将具有过多列的大型csv文件导入到SQL Server中的表中 [英] Importing big csv file with too many columns into a table in SQL Server

问题描述

我想将四个 .csv 文件(每个具有300,000行和150列且大小超过0.5 GB)导入数据库表中.在SQL Server中最简单的方法是什么?

I want to import four .csv files - each with more than 300,000 rows and 150 columns and size over 0.5 GB - into a database table. What is the easiest way to do this in SQL Server?

我正在使用最新的SQL Server 2017 Express.我有4个大的 .csv 文件，我想将其导入数据库.我打算将这些导入到4个单独的表中.但是，要创建新表，需要定义所有列名和数据类型，这将很麻烦.因此，我想知道如何更好，更轻松地实现这一目标.请注意，我是SQL Server的新手，但对它却不太熟悉.

I am using the latest SQL Server 2017 Express. I have 4 large .csv files which I want to import it into a database. I was planning to import these into 4 separate tables. However, to create new table all the column names and datatypes are required to be defined which would be cumbersome. Hence I wanted to know how can I achieve this in a better and easier way. Note that I am a newbie to SQL Server without much familiarity with it.

推荐答案

大容量插入将为您完成此任务.

BULK INSERT would do this for you.

BULK INSERT dbo.YourTableName
FROM 'C:\Users\Steve\Downloads\yourfiletoIMPORT.csv'
WITH (
FirstRow = 2, --(if skipping a header row)
FIELDTERMINATOR = '\t',
ROWTERMINATOR   = '\n'
)

或者...

BULK INSERT SchoolsTemp
    FROM 'C:\Users\Steve\Downloads\yourfiletoIMPORT.csv'
    WITH
    (
    FIRSTROW = 2,
    FIELDTERMINATOR = ',',  --CSV field delimiter
    ROWTERMINATOR = '\n',   --Use to shift the control to next row
    ERRORFILE = 'C:\Users\Steve\Downloads\errors.csv',
    TABLOCK
    )

或者，如下面的链接所述，只需导入文件.

Or, simply import the file, as described in the link below.

https://host4asp.net/import-csv-file-using-sql-server-management-studio/

使用SQL Server Management Studio导入CSV文件:

Import a CSV File Using SQL Server Management Studio:

Step 1
At the start, please open up the SQL Server Management Studio. Log into the target database, and right click the database. Please note that you shall click on the entire database, rather than a particular table. From the Object Explorer, you shall point to the button of Tasks, and find the Import Data.

Step 2
Please note that the Wizard introduction page might be popped up. When you see such introduction page, please safely click on next. This is the screen prompting the selection of a data source. From the screen, you shall select the Flat File source from the Dropdown box, and the Browse button.

Step 3
From the Windows Explorer, you shall select the designated CSV file. In order to ensure you select the correct file type, it is the best practice to select the filetype as CSV, but not TXT. Therefore, only CSV filetype shall be displayed.

Step 4
After the selection of the CSV file, please allocate some time to configure how to import the data into the database before you click the Next > button. Note that you shall ensure the First Data Row checked, because the file shall then contain the required column names. From the following image, you shall see the Column Names from the SQL Server Management Studio shall try their best to important header row instead.

Step 5
After the review of columns, you shall examine more advanced options. The review is important before you completely import the CSV file. From the image below, by default, the SQL Server set the length of each string to be 50.

Step 6
If you have string that is larger than 50, please request the SQL Server to inspect all columns in the file. The inspection can be done by clicking on the Suggest Types button. SQL Server shall be instructed to examine only the first 100 rows, giving suggested types of each column. Error shall be pointed out during the inspection process. Depending on the file size, you can select to inspect the whole file or just selected the fields.

Step 7
You will be prompted to the Preview section from the Data Source page. That will be the last time to examine columns again.

Step 8
After your review on the import preview, you shall select your destination database.

Step 9
In this step, you shall select your destination database. The SQL Server normally selects the desired table on behalf of you. If it is not the case, please create your table. If you would like to select a different table, please click on the destination column for action.

Step 10
You are required to prompt to the option in order to save as an SSIS package. You can also leave the option unchecked as is. Please click next.

Step 11
Finally, you will be prompted to the verification screen. If you are fine with everything, please run the Import by click the Finish.

这篇关于将具有过多列的大型csv文件导入到SQL Server中的表中的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！