Bulk copy of information manually to the SQL Server Management Studio is time-consuming and easy to make errors. There are ways in SQL Server to import data from CSV formatted file to the database. The approaches getting the data into the database are built-in the SQL Server Management Studio. No additional installation of software is required.We have worked out several tutorials to teach our customers to use SQL Server Management Studio to connect to SQL Server and optimize SQL databases. Today, we begin to explain the steps by steps on how to import CSV file using SQL Server Management Studio.
Steps to Import CSV File Using SQL Server Management Studio
In order to import CSV file using SQL Server Management Studio, you need to create a sample table in the SQL Server Management Studio. The table is important for the import of the CSV file. The screen shot below only focuses on particular columns of the table.
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.
