Import Data From CSV File To SQL Server With Import And Export Wizard

There are many ways to import data from csv files to SQL Server but the easiest is using SQL Server's Import And Export Wizard. The wizard is very very versatile as it can do both import and export from various different sources and destinations such Microsoft Access, Micorosft Excel, Flat File (csv,text files), and SQL Server among others.
Here's the steps to follow to use the Wizard for importing a csv file to SQL Server:

1. Open SQL Server Management Studio and connect to the destination database. Right Click the database, select Tasks and click  Import Data...
   
   
   
   
2.  On the opening screen, click Next.
   
   
   
   
3.  On the "Choose A Data Source Screen", select a Data Source in the combobox. In our case, we're going to choose Flat File Source because we're importing from a csv file. Click "Browse" and find the csv file to be imported. Check the checkbox if the csv data contain columns names in the first row.
   
   
   
   
   
4. Click Columns. Here you can specify the row delimiter and column delimiter. Since we're using a csv file, column delimiter is a comma and row delimiter is a new line.
   
   
   
   
5. Click Advanced. Here, you can change the data type for each columns of the csv file. The Wizard detects the type of data and automatically set it for you. But if you think the Wizard is incorrect, you can set the data type manually.
   
   
   
   
   
6. Click Preview to see the preview and check if the settings you have made are correct. Then click Next.
   
   
   
   
   
7. In the "Choose A Destination" window, select a destination in the combobox. In our case, since we are importing to a SQL Server database, we're selecting SQL Server Native Client. Enter the Server Name and Authentication credentials needed for accessing the database. Then click Next.
   
   
   
   
   
8. In the "Select Source Tables and Views" window, check the source you want to import and on the right column select a destination table. For the destination table, you can either select an existing table or a new table.
   
   
   
   
9. Click "Edit Mapping". In the "Column Mappings" window, there are 3 options available. "Create destination table" option is selected if you're creating a new table as the destination. If you're importing to an existing table, you can choose either to "Delete rows in existing table" or "Append rows to the destination table". You can also enable to insert identity data - this should be enabled if your source csv file contains Identity data that you want inserted as the row ID in the table. Then you need to check and modify accordingly the Column Mappings between the csv file and destination table. Then click OK and click Next.
   
   
   
   
   
10. In the "Review Data Type Mapping", you can check the mapping results and determine if there are problems and inconsistencies so you can always click Back and modify your settings accordingly. When all is OK, click Next.
    
    
    
    
11. In the Run Package, check Run immediately. This will execute Import immediately when you click Finished. If you're using SQL Server version that is higher than the Express edition, you can have the other option of saving the Import steps so you can have the luxury of running it again in the future without the need to perform the previous steps again.
    
    
    
    
12. Click Next. In the "Complete the Wizard" steps, the import summary steps are displayed. If these steps are correct, then you can proceed to click Finish to begin importing your data
    
    
    
    
    
    
    
    If you want to be able to save this Import steps, you need to upgrade to a higher version of SQL Server. The saved step is called SSIS package and you can choose to run this package periodically using SQL Server Agent. But that topic is worth another blog page. I'll write about it next time.