Import a CSV File Via SQL Server Management Studio
Posted on June 7th, 2020
The SQL Server Management Studio is also known as SSMS, and it is a software application launched by Microsoft SQL Server 2005. The SQL Server Management Studio helps to configure, administer, and manage all the components within the server. The SSMS also includes both graphical tools and script editors that work with the features and objects of the server. The object Explorer feature of the SQL Server Management Studio allows the users to select, browse, and act upon any of the objects in the server from a single location. This feature has made the management of objects and features of the SQL database easy.
Copying bulk information into the SQL Server Management Studio is a time-consuming process, and this can sometimes cause errors. You can use the CSV formatted files to copy such bulk information to the database. The SQL Server Management Studio tool has a built-in feature to import CSV formatted files, so there is no need for you to install any additional software.
In this knowledge base article, you will learn the steps to import a CSV file into your database with the help of the Microsoft SQL Server Management Studio.
Import CSV File Using SQL Server Management Studio
Please make sure that a table is already available on the database before importing the CSV file. If there is no table available on the database, then you have to create a sample table in the SQL Server Management Studio tool.
Once you have a table, Let’s understand how to import the data from a CSV file.
First of all, Open the SQL Server Management Studio.
Log in to the target database and right-click on the target database to open the Object Explorer. Please note that you should click the entire database and not any particular table inside that.
From the Object Explorer, hover the Task option and find the ‘Import Data’ option.
Select the ‘Import Data’ option. This step opens the ‘Wizard Introduction’ page.
Click the ‘Next’ button to open the ‘Choose a Data Source’ page.
From the ‘Choose a Data Source’ window, select the Data Source as ‘Flat File Source’ from the drop-down.
Click the ‘Browse’ button next to the File name to open the Windows Explorer page.
From the ‘Windows Explorer’ screen, Select the CSV formatted file that you want to import.
After selecting the file, configure how you want to import the data into the database.
Then, select the checkbox next to the ‘Column names in the first data row’ option. Make sure that the column names match the headings given in the first line of a CSV file you want to import.
Click the ‘Column’ option from the left-hand side of the window and review the column names in the file.
Then, click the ‘Advanced’ option from the left-hand side of the window to examine the advanced options, such as Data type, length of the String, and more. By default, the length of the string is 50.
If your imported file has strings more significant than 50, click the ‘Suggest Types’ button to inspect all the columns in the file. By default, SQL Server only examines 100 rows, so you can click the ‘Suggest Types’ button to examine each column. This option points out errors during the inspection process itself. You can inspect the whole file or selected files using this method.
After that, you can click the ‘Preview’ option from the left-hand side of the ‘Choose a Data Source’ window. You can review all the columns again for the last time before the import.
After the review, click the ‘Next’ button.
On the next page, select the destination database details, such as database name, server name, Authentication type, and destination database type.
Click ‘Next’, and on the next page, you can see that the SQL server has selected the table on your behalf. But if it is not the case, you can create a table or select a different table from the list.
Click the ‘Edit Mappings’ button to modify the table details. After that, click the ‘Next’ button.
Now, the system prompts you to save the imported file as an SSIS package. If you don’t want to save the file as an SSIS package, then you can leave the option unchecked.
Check the ‘Run immediately’ option and click the ‘Next’ button.
The system prompts the verification screen. Review all the details, and if everything is fine, click the ‘Finish’ button to import the CSV formatted file to the selected destination database.
So, this is how you can import a CSV file in SQL Server Management Studio.
The SQL Server Management Studio is also known as SSMS, and it is a software application launched by Microsoft SQL Server 2005. You can use the CSV formatted files to copy bulk information to the database by using the SQL Server Management Studio tool. The built-in feature of the SSMS tool helps you to import CSV formatted files. We hope that this knowledge base was helpful to you. Please comment below for any questions or queries. If you are an InterServer customer, please reach out to our support team for further help.