Import Csv File

6 March 2012

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.

Connect

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.

Columns

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

Preview

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.

Schema Map

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.

SQL Table Wizard

SQL Table Wizard

SQL Table Wizard

Step 3 - Import the Data

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

Project

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.

Comparex

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

| |