Import Csv File

Overview

This article describes how to import a file such as CSV into a new SQL Server Table with Data Synchronisation Studio. Data Synchronisation Studio can also import other files :-

• CSV
• TAB
• XLS, XSLX (Microsoft Excel Files)
• MDB (Microsoft Access Database Files)
• SDF (SQL Server Compact)
• XML
• • many more

Step 1 - Open the CSV Data File

Choose the Connect Data Source on Data Source A in Data Synchronisation Studio to open the connection dialog. Locate the CSV File provider and select it. Then enter the path to the CSV file in the Filename property.

Once Data Sync loads the CSV File to discover the columns in your file you will be presented with the schema and a default schema map containing your columns.

You can now preview the data to check that it loads ok.

Now we need to adjust the schema to include the columns we want and setup the data types. We adjust the map like this below where we define the ID column as a Key and set the Data Type as an Integer.

Step 2 - Create SQL Database Table

We now use the Create SQL Table Wizard from the Tools menu to create a SQL Table to import the data. You can also connect to an existing SQL Table or SharePoint List etc.

Step 3 - Import the Data

Now when the Table is created the Schema Map is mapped automatically ready to compare and load the data.

Selecting the Compare A->B toolbar option compares the Data and creates a Change Set to apply to the Target. Simply click Synchronise and the data will be copied into the SQL Table. You can now save the project and quickly and easily run it again at any time.

You can download and use a fully featured version of Data Sync Studio for 15 Days to see how this SQL CSV Import solution works. Click here to go to the Downloads