Import Data From A Text File Into SQL Server Database

Here are some easy steps to transfer text file data into SQL server.

Firstly, we need a text file by which we want to transfer data into the database, so we have to create a text file first and save it.

Step 1: Create a Text File.

In this first step we are going to make a text file from which we want to import data into the database table and save the file into any drive with a name.

We created  a text file and saved that file with DataFile name in the D: drive.


                                                            Figure 1: TextData

Step 2: Create Database

After the end of the first step, go for the second step wherein we are going to create a new database.
Now go to SQL server and create a database in SQL server. You can create a SQL server database through query or manually, it is your choice. Here we are creating a database by query.


                                                                     Figure 2: Database

Step 3: Create A Database Table.

Now we can create a database table with 4 columns and 6 rows, it is optional because SQL server wizard automatically created a table when we import data.


                                                            Figure 3: CreateTable

Step 4: Import Data.

Now, start importing data

Go to database Oct_7_database and right click on the database. After that select Tasks, then select import data by pressing enter key.


                                                                  Figure 4: SelectImport

Step 5: Welcome to Wizard.

Now after clicking on Import data option, a new window “Welcome to SQL Server import and Export Wizard” will open. Here select “Next Button”:


                                          Figure 5: Welcome to Wizard

Step 6: Choose a Data Source.

Here in the data source option select “Flat File Source”.


                                                            Figure 6: FlatFile

Step 7: Select header row delimiter.

After selecting “Flat File Source”, it will open some textboxes. In this window, select File name and browse the file where your file saved. After selecting flat file, select “Tab{t}” in “Header row delimiter” and uncheck the “Column name in the first data row”. After that click on next button.


                                                            Figure 7: Select text file

After selecting the text file as in the following image, click next.


                                                            Figure 8: Click next


                                                   Figure 9: Click next button

Step 8: Choose a Destination.

In this section select a “Destination”. In the destination we need to select “Microsoft OLE DB Provider for SQL Server”.


                                             Figure 10: Select OLE DB Provider

Now after selecting ”Microsoft OLE DB provider for SQL Server” select Server name, here my server name is “MCNDESKTOP16” because this is my local server name. You are selecting your server name and now the option is “Authentication”. Here select an option where you will login your SQL Server.

If you Login Windows Authentication, select first radio button “Use Windows Authentication” and if you login with SQL Server Authentication, then select “Use SQL Server Authentication”. Here I login with SQL Server authentication, so provide the “user name” and “ password” and select the next button.


                                                 Figure 11: Authentication

Step 9: Select Source Tables and views.

After authentication process we will go to “Select Source Tables and Views” window. In this section, wizard automatically creates a table, here the default table name is “[dbo].[DataFile]” . If you want to import data in the default table, then you can insert, otherwise you are selecting your database table.

Select -> [dbo].[Import_Data] data table.

After selecting your table, click on “Edit Mapping” button. Here new window will open with the name “Column Mappings”.

Here “Append rows to the destination table” radio button is already selected  and mapped. After that click ok.


                                                               Figure 12: Mapped

You can check your table preview on clicking “Preview” Button. Now click on next button


                                                         Figure 13: Preview

Step 10: Save and run Package.

After clicking on the next button, a new windows will be visible with the name “Save and Run Package”. In this section you can save packages, then select checkbox “Save SSIS Package” and if you did not saved a package then select check box “Run Immediately”. Here we did not saved a package so select Run immediately checkbox and click on “Next Button”.


                                                Figure 14: Run Immediately

Step 11: Complete the Wizard.

Now we are on final stage, here a windows “Complete the Wizard” will be visible. Now click on Finish Button.


                                                Figure 15: Complete Wizard

Step 12: After clicking on the “finish button” we can see the next window.

“Performing Operation”: Here operation is in progress.


                                                         Figure 16: Progress

When all operations are successful, then your import operation completes properly and your file has been successfully imported.


                                                         Figure 17: Successful

Step 13: This is the final step of this article, now go to SQL Server and check that your data successfully inserted or not.


                                                                 Figure 18: Final output