Use the Data Connection Wizard to import data into Publisher

There is a world of data outside of Publisher program that you can use with your Publisher program, but how do you import the data and secure it? The answer is all about making the right connections.

Use the Data Connection Wizard to import external data

In Publisher, you import external data by using mail merge. Use mail merge to create a large number of documents that are mostly identical but include some unique information, such as a product announcement that you are sending to your top 100 customers. You can also import text files, Excel worksheets, and Access tables or queries directly, without using a connection file. For more information, see Create a mail or e-mail merge in Publisher.

• What do you want to do?
• Use an OLE DB provider to import a text file
• Use an ODBC driver to import a text file
• Use an OLE DB provider to import data from a SQL Server database
• Use an ODBC driver to import data from a SQL Server database

Importing a comma-separated values (CSV) text file (.csv) is straightforward. If your text file is a .csv file that does not use the list separator character that is defined on your machine, or if your text file is not a .csv file, you can use a Schema.ini file to specify the correct file format.

Import the file

1. Click Mailings >Select Recipients >Use Existing List.
2. In the Select Data Source dialog box, do one of the following:
   • To use an existing .odc file, double-click the .odc file. The data is imported, and you are finished.
   • To create a new .odc file, click New Source, and then follow each step in the procedure. The Data Connection Wizard appears.
3. In the Welcome to the Data Connection Wizard page, click Other/Advanced. The Data Link Properties dialog box appears. For more information about different options in the various dialog boxes, click Help.
4. On the Provider tab, select Microsoft Jet 4.0 OLE DB Provider, and then click Next.
5. On the Connections tab, in the Select or enter a database name box, enter the full path to the folder that contains the text file. To help you locate the folder, click the Browse button next to the box.
6. Click the All tab, select Extended Properties, and then click Edit Value.
7. In the Property Value box, enter one of the following:
   • If the text file has column headers, enter Text;HDR=Yes.
   • If the text file does not have column headers, enter Text;HDR=No.
8. Click OK.
9. To ensure that you entered the correct information, click the Connection tab, and then click Test Connection.
10. Do one of the following:
    • If you receive an error message, recheck the values that you entered in the previous steps.
    • If you receive the message "Test connection succeeded", click OK.
11. Click OK. The Data Connection Wizard reappears.
12. In the Select Database and Table page, under the Name column, select the text file that you want to import, and then click Next.
13. In the Save Data Connection File and Finish page, enter a name for the .odc file in the File Name box, and then click Finish. To change the folder location, which is the My Data Sources folder by default, click Browse.
14. Optionally, to make the connection file more discoverable, you can enter additional information in the Description, Friendly Name, and Search Keywords boxes.

Use a Schema.ini file to specify a different list separator character or text file format

A Schema.ini file is a text file that contains entries that override default text driver settings in the Windows registry. In general, to use a Schema.ini file, you must do the following:

• Store the Schema.ini file in the same folder as the text file that you are importing.
• Name the file Schema.ini.
• On the first line of the Schema.ini file, type the name of the text file that you are linking to, surrounded by brackets.
• Add additional information to specify the different text file format.

The following sections show common examples for using the Schema.ini file.

Example: Specify a semicolon character (;) as the delimiter

Example: Specify a tab character as the delimiter

Example: Specify a fixed-width file

[Shippers.txt]Format=FixedLengthCol1=ShipperID Text Width 11Col2=CompanyName Text Width 40Col3=Phone Text Width 24

Importing a text file by using an ODBC driver is, at minimum, a two-step process. First, if necessary, define a user DSN on your computer for the ODBC text driver. Second, import the text file by using the user DSN. If your text file is a comma-separated values (CSV) file that does not use the list separator character that is defined on your machine, or if your text file is not a .csv file, you can use a Schema.ini file to specify the correct file format.

Define a User DSN

1. Open Control Panel and then click System and Security >Administrative Tools >ODBC Data Sources (32-bit) or ODBC Data Sources (64-bit).
2. In the ODBC Data Source Administrator dialog box, on the User DSN tab, click Add.
3. In the Create New Data Source dialog box, select Microsoft Text Driver (*.txt; *.csv), and then click Finish. The ODBC Text Setup dialog box appears.
4. Enter a name in the Data Source Name.
5. Clear the Use Current Directory check box.
6. Click Select Directory.
7. In the Select Directory dialog box, locate the folder that contains the text file that you want to import, make sure that the text file appears in the list below the File Name box, and then click OK.
8. Click OK twice.

For more information about different options in the various dialog boxes, click Help.

Import the file

1. Click Mailings >Select Recipients >Use Existing List.
2. In the Select Data Source dialog box, do one of the following:
   • To use an existing .odc file, double-click the .odc file. The data is imported and you are finished.
   • To create a new .odc file, click New Source, and then follow each step in the procedure. The Data Connection Wizard appears.
3. In the Welcome to the Data Connection Wizard page, click ODBC DSN.
4. In the Connect to ODBC Data Source page, select the User DSN that you just created, and then click Next.
5. In the Select Database and Table page, select the text file under the Name column, and then click Next.
6. In the Save Data Connection File and Finish page, enter a name for the .odc file in the File Name box, and then click Finish. To change the folder location, which is the My Data Sources folder by default, click Browse.
7. Optionally, to make the connection file more discoverable, you can enter additional information in the Description, Friendly Name, and Search Keywords boxes.

Use a Schema.ini file to specify a different list separator character or text file format

A Schema.ini file is a text file that contains entries that override default text driver settings in the Windows registry. In general, to use a Schema.ini file, you must do the following:

• Store the Schema.ini file in the same folder as the text file that you are importing.
• Name the file Schema.ini.
• On the first line of the Schema.ini file, type the name of the text file that you are linking to, surrounded by brackets.
• Add additional information to specify the different text file format.

The following sections show common examples for using the Schema.ini file.

Example: Specify a column header

Example: Specify a semicolon character (;) as the delimiter

Example: Specify a tab character as the delimiter

Example: Specify a fixed-width file

[Shippers.txt]Format=FixedLengthCol1=ShipperID Text Width 11Col2=CompanyName Text Width 40Col3=Phone Text Width 24

1. Click Mailings >Select Recipients >Use Existing List.
2. In the Select Data Source dialog box, do one of the following:
   • To use an existing .odc file, double-click the .odc file. The data is imported and you are finished.
   • To create a new .odc file, click New Source, and then follow each step in the procedure. The Data Connection Wizard appears.
3. In the Welcome to the Data Connection Wizard page, click Microsoft SQL Server.
4. In the Connect to Database Server page, do the following:
   • Enter the name of the database server in the Server Name box. If the database is on your computer, enter (local).
   • Under Logon credentials, do one of the following:
     • To use your Windows user name and password, click Use Windows Authentication.
     • To use a database user name and password, click Use the following User Name and Password, and then enter the database user name and password in the appropriate boxes.
5. Click Next.
6. In the Select Database and Table page, select the database in the Database box, select the table, view, or user-defined function under the Name column, and then click Next.
7. In the Save Data Connection File and Finish page, enter a name for the .odc file in the File Name box, and then click Finish. To change the folder location, which is the My Data Sources folder by default, click Browse.
8. Optionally, to make the connection file more discoverable, you can enter additional information in the Description, Friendly Name, and Search Keywords boxes.

Importing data from a SQL Server database by using an ODBC driver is a two-step process. First, if necessary, define a user DSN on your computer for the ODBC driver. Second, import the data from the SQL Server database.

Define a User DSN

1. Open Control Panel and then click System and Security >Administrative Tools >ODBC Data Sources (32-bit) or ODBC Data Sources (64-bit).
2. In the ODBC Data Source Administrator dialog box, select the User DSN tab, and then click Add.
3. In the Create New Data Source dialog box, select SQL Server, and then click Finish. The Create a New Data Source to SQL Server dialog box appears.
4. Enter a data source name in the Name box.
5. Optionally, enter a description of the data source in the Description box.
6. Enter the database server name in the Server box. If the database is on your computer, enter (local).
7. Click Next.
8. Under How should SQL Server verify the authenticity of the login ID?, do one of the following:
   • To use your Windows user name and password, click With Windows NT authentication using the Network login ID.
   • To use a database user name and password, click With SQL Server authentication using login ID and password entered by the user, and then enter the database login ID and password in the appropriate boxes.
9. Click Next twice, and then click Finish.
10. To ensure that you entered the correct information, click Test Data Source .
11. Do one of the following:
    • If you receive an error message, recheck the values that you entered in the previous steps.
    • If you receive the message "TESTS COMPLETED SUCCESSFULLY!", click OK.
12. Click OK twice.

For more information about different options in the various dialog boxes, click Help.

Import the data from the SQL Server database

1. Click Mailings >Select Recipients >Use Existing List.
2. In the Select Data Source dialog box, do one of the following:
   • To use an existing .odc file, double-click the .odc file. The data is imported and you are finished.
   • To create a new .odc file, click New Source, and then follow each step in the procedure. The Data Connection Wizard appears.
3. In the Welcome to the Data Connection Wizard page, click ODBC DSN.
4. In the Connect to ODBC Data source page, select the data source name that you defined in the previous section, and then click Next.
5. In the Select Database and Table page, select the database in the Database box, select the table, view, or function under the Name column, and then click Next.
6. In the Save Data Connection File and Finish page, enter a name for the .odc file in the File Name box, and then click Finish. To change the folder location, which is the My Data Sources folder by default, click Browse.
7. Optionally, to make the connection file more discoverable, you can enter additional information in the Description, Friendly Name, and Search Keywords boxes.