Import data from Text (TXT)

TXT is a file format used to store plain text that can be opened in any text editor on any operating system.

Import data from TXT

1. Open the Data Import wizard in one of these ways:

On the Start Page, select Data Pump > Import External Data.
In Database Explorer, right-click the required database or server connection and select Tasks > Import Data.
In Database Explorer, right-click the required table or view and select Import Data.
On the menu bar, select Database > Tasks > Import Data.

2. On the Source file page, select the file format and specify the source document, then click Next.

2.1. Under Import Formats, select Text.

2.2. In File Name, click the ellipsis (…) and select the file.

Tip

Click Load Template to load import options from a previously saved template file.

Note

To move between pages, click Next or select a page directly from the navigation pane on the left.

To return to the previous step and review or change your settings, click Back.

To close the wizard without saving your changes, click Cancel.

Select the TXT format

3. On the Destination page, select the target table for the data import, then click Next.

3.1. In Connection, select the server connection for the database. You can also create a new connection or update an existing one.

Tip

To create or update a server connection:

1. In Connection, select Manage.

2. In the Connection Manager dialog, click New to create a connection or Edit to modify an existing one.

3. In the Database Connection Properties dialog, configure connection settings, then click Connect.

4. In the Connection Manager dialog, click OK to use the connection for the import.

3.2. In Database, select the database.

3.3. In Schema, select the appropriate database schema.

3.4. Select the target table:

New table – Enter the name of a new table.
Existing table – Select an existing table.

Select the destination table

Tip

To find tables, enter a search query in the search box.

To update data in the grid, click Refresh.

4. On the Options page, configure import options, then click Next.

4.1. Specify text formatting options for the data in the source file.

Encoding – Specifies the character encoding.
Field Quote – Specifies the character for enclosing field values.
Skip Lines – Specifies the number of skipped lines. For example, if you set the option to 3, the first three lines are skipped. In the Preview area, skipped lines are highlighted in red.
Header Position – Specifies the line number where the header is placed. In the Preview area, it is highlighted in blue.

Note

Import even lines only is turned off by default. To import data from even-numbered rows in the document, select the option.

4.2. Under Splitting, configure how field values should be separated.

Manual – Separates field values. In the Preview area, double-click and drag the markers to define field boundaries. You can add as many boundary markers as needed to separate the data. To remove a marker, double-click it.
Fixed width – Specifies the number of characters allocated to each field.
Text Separator – Specifies the character used to separate field values in the source file: Tab, Space, Custom, or Auto defined.

Note

Auto defined is selected by default. To use a specific character, clear the checkbox, then select Text Separator and select the field delimiter or enter a custom one.

The result of your configuration appears in the Preview area.

Configure import options

5. On the Data formats page, set the data import formats, then click Next.

5.1. On the Common Formats tab, change the default format settings for all columns: Null String, Thousand separator, Decimal separator, Boolean, and Date and Time.

5.2. On the Column Settings tab, change the default format settings for specific columns: Null String, Left Quote, Right Quote, and Date and Time.

Note

Autodetect Date and Time format is selected by default. To change the format, clear the checkbox, then select the required format in Date and Time.

If a format mask is not set, dbForge Studio automatically identifies date and time values.

Specify data formats

6. On the Mapping page, map columns between the source and target tables, then click Next.

For data import to a new table: In Source column, select the required column from the dropdown list.
For data import to an existing table:

1. In Source column, select the required column from the dropdown list.

2. In Default Value, specify a default value for each column.

3. Optional: In Key, select one or more key columns that uniquely identify each row for the data import. The key is available only when you import data into an existing table.

Note

If no key column is selected, only the Append and Repopulate modes are available.

For manual data import, columns are mapped automatically. To undo the mapping, click Unmap.

The result of your configuration appears in the Preview area.

Mapping

Note

When you import data to a new table, dbForge Studio for SQL Server automatically creates and maps all columns.

When you import data to an existing table, columns with matching names are mapped automatically, while the rest must be mapped manually. If no columns with matching names are found, they are mapped sequentially, for example, the first source column to the first target column and the second to the second.

Tip

To cancel the mapping of all columns, click Clear Mappings.

To restore the mapping, click Fill Mapping.

To remove a column, select it and click Delete.

To move a column to a specific position, click or , or drag the column to the desired location.

To add a column, click New Column. In the Column Properties dialog, specify column properties, then click OK.

To edit a column, select it and click Edit. In the Column Properties dialog, specify column properties, then click OK.

7. On the Modes page, select a data import mode, then click Next.

Append – Adds new records from the source table to the target table. Existing records in the target table are not modified.
Update – Replaces records in the target table with matching records from the source table.
Append/Update – Updates existing records in the target table if a match is found. If no match exists, it inserts a new record.
Delete – Deletes records in the target table that match records in the source table, then inserts the corresponding records from the source table.
Repopulate – Deletes all records from the target table and repopulates it with records from the source table.

Note

When you import data to a new table, only the Append mode is available.

By default, Use a single transaction and Use bulk insert are selected. When Use a single transaction is selected, the tool runs the data import within a single database transaction. When Use bulk insert is selected, import performance (especially for large datasets) is improved. To turn off the option, clear the checkbox.

Import modes

8. On the Output page, select the output option, then click Next.

Open the data import script in the internal editor – Opens the script in dbForge Studio after the data import is complete.
Save the data import script to a file – Saves the import script to a file. Click the ellipsis (…) and specify the path to the file where the script will be saved.

To add the date and time to the file name, select Add timestamp to the file name.

To open the file in the internal editor after the import completes, select Open in the internal editor.
Import data directly to the database – Inserts data directly into the target database.

Output

9. On the Errors handling page, choose how to process errors and how they should be logged.

Abort at the first error – Stops import immediately when the first error occurs.
Prompt a user for an action – Stops the import when an error occurs and prompts the user to choose the action.
Ignore all errors – Continues the data import, skipping any errors without interruption.

Tip

To create a log file, select Write a report to a log file, then click the ellipsis (…) and select the folder where you want to save the file.

Errors Handling

Note

To save your import settings as templates for recurring scenarios, expand the Save menu and select Save Template. You can then use the templates to import data from the command line.

10. Click Import.

11. On the Finish page, select one of the following actions:

Show log file – Opens the log file.
Import more – Starts another import.

Import Completed

12. Click Finish to close the wizard.

Bulk import using a PowerShell script

You can use a PowerShell script to import data from multiple tables into new tables.

1. Create a folder on your computer for the TXT files that you want to import.

Note

If a file name contains a dot, it is replaced with an underscore in the table name after the bulk import.

2. Create a configuration file with the connection properties for the target database:

2.1. Open any text editor.

2.2. Enter the database connection properties for the target database. Separate each value with a comma.

2.3. Save the .txt file, for example, as databases_config_sqlserver.txt.

Server – The server name where the target database is located.
Database – The name of the target database.
Authentication – False for SQL Server authentication, and True for Windows authentication.
Login – The SQL Server login name.
Password – The SQL Server login password.

3. Create the import template file:

3.1. On the menu bar, select Database > Tasks > Import Data.

3.2. Select TXT and load a TXT source file that you have already placed in the folder. Then click Next.

3.3. Optional: Configure data import options.

3.4. Click the arrow next to Save and select Save Template.

Save Template option

4. Open Windows PowerShell.

5. In the editor, enter the following script. Replace the parameters with your actual values.

# Set-ExecutionPolicy -ExecutionPolicy RemoteSigned

$configFile = "<path_to_config_file_with_database_connection_properties>" 

$diffToolLocation = "<path_to_dbforge_studio_for_sql_server_file>"

$importPath = "<path_to_folder_with_txt_files>"

$importTemplate = "<path_to_import_template_file>"

$databases = Get-Content $configFile | ForEach-Object {
    $fields = $_ -split ","
    [PSCustomObject]@{
        Host     = $fields[0]
        Database = $fields[1]
        Security = $fields[2]
        User     = $fields[3]
        Password = $fields[4]
    }
}
# Extract database configuration
$MasterDB = $databases[0]


$fileNames = @(Get-ChildItem -Path "$importPath" -Filter *.txt | Select-Object -ExpandProperty Name)
Write-Output "File:" $filenames 
   
     foreach ($fileName in $fileNames) {  
              Write-Output "Processing file: $fileName"
              $inputfile = "$importPath$fileName"
              $table = $fileName.Substring(0, $fileName.Length - 4) 
              $tableName = $table.Replace(".", "_") 
 
              #Command-line string for importing data into your SQL Server database
              $process = Start-Process -FilePath $diffToolLocation -ArgumentList "/dataimport /templatefile:`"$importTemplate`" /connection:`"Data Source=$($MasterDB.Host);Initial Catalog=$($MasterDB.Database);Integrated Security=$($MasterDB.Security);User ID=$($MasterDB.User);Password=$($MasterDB.Password)`" /inputfile:`"$inputfile`" /table:`"$($MasterDB.Database).dbo.$tableName`" /errormode:abort /create " -PassThru -Wait  -windowstyle hidden     
         
               if ($process.ExitCode -eq 0) {
                    Write-Output "File import $fileName completed successfully into table $tableName"             
               } else {
                    Write-Output "Errors occurred while importing files $fileName"             
               }
              Write-Output "ExitCode:" $process.ExitCode            


    #If you need to process the tables one by one to reduce server load - uncomment it.
    #Start-Sleep -Seconds 10                        
      }

<path_to_config_file_with_database_connection_properties> – The path to the configuration file that contains the database connection properties.
<path_to_dbforge_studio_for_sql_server_file> – The path to the dbForge Studio for SQL Server configuration file.

The default path to this executable depends on where dbForge Studio for SQL Server is installed. If you installed dbForge Studio:

As a standalone tool: C:\Program Files\Devart\dbForge Studio for SQL Server.
As a part of the dbForge Edge bundle: C:\Program Files\Devart\dbForge Edge\dbForge Studio for SQL Server.
<path_to_folder_with_txt_files> – The path to the folder that contains TXT files.
<path_to_import_template_file> – The path to the import template file.

6. Run the script.

Result of imported tables