Open topic with navigation

Learn more about Toad for SQL Server at Quest.com Find solutions and downloads at the Toad for SQL Server Support Portal

Import Data

You can import data from a single file, from multiple files of the same type in a folder, from multiple files in different folders, from a SQL query, or from a SharePoint list. You can import data into a new or existing table.

Note: Attempting to import data that was exported to an Excel instance where the fractional seconds were truncated results in an error.

Considerations When Importing Data

Review the following information before importing data.

To import data

1. Select the database connection on which you want to import data.

   Note: The Import Wizard will import data to the currently-active open connection.

2. Select Tools | Import | Import Wizard.

3. Use the Import Wizard to specify a data source and import options. Review the following for additional information:

   Import File Page	Description
   Import objects list	Step one: Select one of the following types of objects to import: Add File—Select a single file to import. If you want to import multiple files, use the Add Folder button. Add Folder—Add multiple files of the same file type from a directory. For example, you can select xls or csv files, but you cannot select a combination of the two. Add Query—Open a SQL file or enter a query. SharePoint—Select a SharePoint list to import. Step two: Select the source data and specify import options. If you need help specifying the import options for the object you selected, please see the specific instructions in the sections that follow. Step three: You can add additional objects to the Import objects list by selecting another import object type. Note: You can add multiple objects to the Import objects list to import all at one time. However, you can only import to one target—the currently active open connection.
   Save Settings as Template Page	Description
   Save template	Select this checkbox and specify a file name so you can use this against a similar database or in an automation script. See Use Import and Export Templates for more information.

4. Click Finish to import the data and close the wizard.

To import one file at a time

1. Select Tools | Import | Import Wizard.
2. Click Add File and select a file.

3. Click the following to review additional information:

   Excel

   File Preview Page	Description
   Column name as header	Select this checkbox if the first row of the file uses column headings, and the actual data begins on the following row.
   	Select to see Excel preview and to specify a range of cells to be imported manually.
   Transpose All	Select this checkbox to switch the order of columns so that they display down the first, left set of rows, while data from the rows displays in columns.
   Normalize Columns	Select this checkbox to group several column headers as values under other header and to put their values in the next column. Press to specify the required columns. See example: Without normalizing Normalized by Q1, Q2, Q3, Q4 columns.
   Start column	Select the column where you want to begin importing data. By default, this begins at the first column. Tip: Click to open the file in Excel and select a range of columns and rows to import.
   Column count	Select the number of columns you want to include in the import. By default, all columns are imported.
   Start row	Select the row where you want begin importing data. By default, this begins at the first row. Tip: Click to open the file in Excel and select a range of columns and rows to import.
   Row count	Select the number of rows you want to include in the import. By default, all rows are imported.
   Tip: You can filter data for each column by clicking beside the column header. You can also click to include or exclude individual fields and values.

   Select Target Page	Description
   A single existing table	Select to add data to an existing table.
   A single new table	Select to create a new table. Default On Error—Select a default value. When data fails to import because of an error, Toad enters this value in the cell in place of the data.
   Processing row count	Enter a value for the number of rows to process at one time when importing data.
   Identity inserts	Select to enable importing into identity columns.

   CSV, XML, Text, XSD,

   File Preview Page	Description
   Column names as header	Select this checkbox if the first row of the file uses column headings, and the actual data begins on the following row.
   File encoding	Note: When importing data that contains international characters, make certain that your default character set is UTF-8. You must also select Unicode in the File encoding field.
   Use current regional settings	Select to interpret imported data using your regional settings. Deselect to interpret imported data using "United States" regional settings. Note: You can change the regional settings using your PC's Control Panel. See your system's documentation for more information.

   Select Target Page	Description
   A single existing table	Select to add data to an existing table.
   A single new table	Select to create a new table. Default On Error—Select a default value. When data fails to import because of an error, Toad enters this value in the cell in place of the data.
   Processing row count	Enter a value for the number of rows to process at one time when importing data.
   Identity inserts	Select to enable importing into identity columns.

   Fixed Width File (.fil)

   File Preview Page	Description
   Column names as header	Select this checkbox if the first row of the file uses column headings, and the actual data begins on the following row.
   Note: The preview is based on the first 50 rows in the file.

To import a folder

1. Select Tools | Import | Import Wizard.

2. Click Add Folder.

   Notes: 

   • This adds multiple files of the same file type from a directory. For example, you can select xls or csv files, but you cannot select a combination of the two.
   • The filter you select on the File Directory page is applied at execution time, so Toad will use the latest files in the directory, including new files that match the filter criteria.

To import a query

1. Select Tools | Import | Import Wizard.

2. Click Add Query.

3. Open a SQL file or enter a query.

   Notes: 

   • If you open an existing SQL file, you must select the connection associated with that file from the drop-down list. Toad uses your current connection when importing the data.
   • If you are entering a query, you can press CTRL+PERIOD to use Code Completion to select objects.

To import a list from SharePoint

1. Select Tools | Import | Import Wizard.

2. Click SharePoint.

   Note: You must have a SharePoint URL defined in Tools | Options | Environment | SharePoint.

3. Select a SharePoint list to import.

4. Select Exclude create/modify info to exclude the Modified, Created, Created By, and Modified By columns from import.

   Note: Toad supports SharePoint 2010 only.

Related Topics

Export Data with the Export Wizard

Export Data with One Click Export