How to Import Delimited Text Files into a SQL Server 2005 database

This step-by-step goes through the process of importing delimited text files into a SQL Server 2005 database. The text files that I used while writing this article were the recently released tab delimited AOL search data (See: How to Import Delimited Text Files into Microsoft Excel). The following example should help you better understand how to import different types of flat files into SQL Server 2005.

1. Open Microsoft SQL Server Management Studio.
2. Click the plus sign next to the Databases folder in the Object Explorer window and find the database you want to use to Import files into.
3. Right click on your database and select Tasks > Import Data… 
4. The SQL Server Import and Export Wizard dialog box will pop-up. Click Next.
5. Click on the Data Source drop-down box, and choose Flat File Source from the list. Then click Browse… and select the Delimited text file you want to import. See Figure 1.
6. Keep the Format: drop-down box as Delimited, and change the Header row delimiter, Header rows to skip, to correspond with your text file. Click the checkbox if column names are in the first data row.
7. Now click on Columns, located in the left-hand side list box. Make sure the Row and Column delimiters are set correctly. For instance, I am using tab delimited text file so I want the Column delimiter to be set to Tab {t}.
8. Now click on Advanced, located in the left-hand side list box. Make sure all of your columns show up in column list box. If they don’t all show up (due to some columns in your text file being null), then click on the New button to add a column. Now configure the properties of each column to correspond with the data you're importing. Check each column and change the OutputColumnWidth, ColumnDelimiter, DataType and Name to match up with the delimited columns in the text file you are importing.  See Figure 2.
9. Now click on Preview, located in the left-hand side list box and double check to make sure you are importing the data how you want, with the correct amount of columns.
10. Click on Next, and then choose the Database you want to import the data into.
11. Click Next to move to the Select Source Tables and Views. See Figure 3.
12. Under the Destination header, you can click on the default table to change it to an existing table in your database. Leave the default if you want to create a new table, but if you are appending data to an existing table you will want to change it to the table you want to use.
13. Now click on the Edit Mappings button and make sure the mapping properties are what you want, make any changes necessary. See Figure 4. Click OK when finished.
14. Click Next, and leave Execute immediately checked.
15. Click Next, and then click Finish, to start performing the import operation.

If all goes well you won't receive any errors during the import operation and the delimited text file data will have been successfully imported into a SQL Server 2005 database table.