Redigera

Dela via

Import Flat File to SQL Wizard

  • Artikel

Applies to: SQL Server Azure SQL Database Azure SQL Managed Instance

For content related to the Import and Export Wizard, see Import and Export Data with the SQL Server Import and Export Wizard.

Import Flat File Wizard is a simple way to copy data from a flat file (.csv, .txt) to a new table in your database. The Import Flat File Wizard supports both comma-separated and fixed width format files. This overview describes the reasons for using this wizard, how to find this wizard, and a simple example to follow.

Why would I use this wizard?

This wizard was created to improve the current import experience leveraging an intelligent framework known as Program Synthesis using Examples (PROSE). For a user without specialized domain knowledge, importing data can often be a complex, error prone, and tedious task. This wizard streamlines the import process as simple as selecting an input file and unique table name, and the PROSE framework handles the rest.

PROSE analyzes data patterns in your input file to infer column names, types, delimiters, and more. This framework learns the structure of the file and does all of the hard work so users don't have to.

Prerequisites

This feature is available on SQL Server Management Studio (SSMS) v17.3 or later. Make sure you're using the latest version. You can find the latest version here.

Getting Started

To access the Import Flat File Wizard, follow these steps:

  1. Open SQL Server Management Studio.
  2. Connect to an instance of the SQL Server Database Engine or localhost.
  3. Expand Databases, right-click a database (test in the following example), point to Tasks, and select Import Flat File above Import Data.

Screenshot of Import Flat File menu.

To learn more about the different functions of the wizard, refer to the following tutorial:

Tutorial

For the purposes of this tutorial, feel free to use your own flat file. Otherwise, this tutorial is using the following CSV from Excel, which you're free to copy. If you use this CSV, title it example.csv and make sure to save it as a csv in an easy location such as your desktop.

Screenshot of Excel.

Overview:

  1. Access Wizard
  2. Specify Input File
  3. Preview Data
  4. Modify Columns
  5. Summary
  6. Results

Step 1: Access Wizard and Intro Page

Access the wizard as described here.

The first page of the wizard is the welcome page. If you don't want to see this page again, feel free to select Do not show this starting page again.

Screenshot of Import Flat File Wizard Introduction menu.

Step 2: Specify Input File

Select browse to select your input file. At default, the wizard searches for .csv and .txt files. PROSE detects if the file is comma-separated or fixed-width format regardless of file extension.

The new table name should be unique, and the wizard doesn't allow you to move further if not.

Screenshot of Import Flat File Wizard Specify Input File menu.

Step 3: Preview Data

The wizard generates a preview that you can view for the first 50 rows. If there are any problems, select cancel, otherwise proceed to the next page.

Screenshot of Import Flat File Wizard Preview Data menu.

Step 4: Modify Columns

The wizard identifies what it believes are the correct column names, data types, etc. Here's where you can edit the fields if they're incorrect (for example, data type should be a float instead of an int).

Columns where empty values are detected will have "Allow Nulls" checked. However if you expect nulls in a column and "Allow Nulls" isn't checked, here's where you can update the table definition to allow nulls in one or all columns.

Proceed when ready.

Screenshot of Import Flat File Wizard Modify Columns menu.

Step 5: Summary

This is simply a summary page displaying your current configuration. If there are issues, you can go back to previous sections. Otherwise, selecting finish attempts the import process.

Screenshot of Import Flat File Wizard Summary menu.

Step 6: Results

This page indicates whether the import was successful. If a green check mark appears, it was a success, otherwise you might need to review your configuration or input file for any errors.

Screenshot of Import Flat File Wizard Results menu.

Troubleshooting

The Import Flat File Wizard detects the data types based on the first 200 rows. In scenarios where data further in the flat file doesn't conform to the automatically detected data types, an error occurs during import. The error message would be similar to the following:

Error inserting data into table. (Microsoft.SqlServer.Prose.Import)
The given value of type String from the data source cannot be converted to type nvarchar of the specified target column. (System.Data)
String or binary data would be truncated. (System.Data)

Tactics to alleviate this error:

  • Expanding the data type sizes in the Modify Columns step, such as the length of a nvarchar column, might compensate for variations in the data from the remainder of the flat file.
  • Enabling error reporting in the Modify Columns step, especially by a smaller number, will reveal which rows in the flat file contain data that doesn't fit the selected data types. For example, in a flat file where the second row introduces an error, running the import with error reporting with a range of 1 provides a specific error message. Examining the file directly at the location can provide more targeted changes to the data types based on the data in the identified rows.

Screenshot of an error in the Import Flat File Wizard reporting results. 

Error inserting data into table occurred while inserting rows 1 - 2. (Microsoft.SqlServer.Prose.Import)
The given value of type String from the data source cannot be converted to type float of the specified target column. (System.Data)
Failed to convert parameter value from a String to a Double. (System.Data)

Currently, the importer uses encoding based on the system's active code page. On most machines this defaults to ANSI.

Related content

Learn more about the wizard.