Use the Data Connection Wizard to import data into Publisher

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

For more information about importing data securely, see Office data connections overview

Use the Data Connection Wizard to import external data

In Microsoft 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 merge.

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.

    Notes: If you are using Publisher 2007, on the Tools menu, click Mailings and Catalogs > Mail Merge, E-mail Merge, or Catalog Merge, and then do one of the following:

    • In the Mail Merge task pane, click Create or connect to a recipient list.

    • In the E-mail Merge task pane, click Create or connect to a recipient list.

    • In the Catalog Merge task pane, click Create or connect to a product 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

[Categories.txt] Format=Delimited(;)

Example: Specify a tab character as the delimiter

[Products.txt] Format=TabDelimited

Example: Specify a fixed-width file

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

Top of Page Top of Section

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 clickSystem 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.

    Notes: If you are using Publisher 2007, on the Tools menu, click Mailings and Catalogs > Mail Merge, E-mail Merge, or Catalog Merge, and then do one of the following:

    • In the Mail Merge task pane, click Create or connect to a recipient list.

    • In the E-mail Merge task pane, click Create or connect to a recipient list.

    • In the Catalog Merge task pane, click Create or connect to a product 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 ODBCDSN.

  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

[Categories.txt] ColNameHeader=True

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

[Categories.txt] Format=Delimited(;)

Example: Specify a tab character as the delimiter

[Products.txt] Format=TabDelimited

Example: Specify a fixed-width file

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

Top of Page Top of Section

  1. Click Mailings > Select Recipients > Use Existing List.

    Notes: If you are using Publisher 2007, on the Tools menu, click Mailings and Catalogs > Mail Merge, E-mail Merge, or Catalog Merge, and then do one of the following:

    • In the Mail Merge task pane, click Create or connect to a recipient list.

    • In the E-mail Merge task pane, click Create or connect to a recipient list.

    • In the Catalog Merge task pane, click Create or connect to a product 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.

Top of Page Top of Section

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.

    Notes: If you are using Publisher 2007, on the Tools menu, click Mailings and Catalogs > Mail Merge, E-mail Merge, or Catalog Merge, and then do one of the following:

    • In the Mail Merge task pane, click Create or connect to a recipient list.

    • In the E-mail Merge task pane, click Create or connect to a recipient list.

    • In the Catalog Merge task pane, click Create or connect to a product 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 ODBCDSN.

  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.

Top of Page Top of Section

Top of Page

See also

Office data connections overview

Use the Data Connection Wizard to import data into Visio

Use the Data Connection Wizard to import data into Word

Expand your skills
Explore training
Get new features first
Join Office Insiders

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×