Use the data connection wizard to import data into visio

In Visio, you import external data so that it can be added directly to shapes in a drawing. Use a drawing to monitor information in a visual manner, such as a network diagram that displays downtime statistics or a flowchart that displays progress bars and meters to summarize project tasks. The following instructions are for text files and SQL Server data sources. You can also import Excel worksheets, Access tables or queries, and SharePoint lists directly into a file without using a connection file. For more information, see Import data from Excel, SQL Server, SharePoint sites, and other external sources.

Newer versions Office 2013- 2010



Use an OLE DB provider to import a text file

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. On the Data menu, click Custom Import.

2. On the first page of the Data Selector Wizard, click Other OLE DB or ODBC data source and click Next.

   The Data Connection Wizard appears.

3. Click Other/Advanced and click Next.

   The Data Link Properties dialog box appears.

   Note: 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 Connection 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.

15. Click Finish.

    The Data Selector Wizard reappears.

16. In the Select Data Connection page, do one of the following:

    • To use the .odc file that you just created and to accept all the rows and columns in the data source, click Finish.

    • To continue using the Data Selector Wizard, so that you can select specific columns and rows, and so that you can create a unique identifier, click Next.

      For more information on each page in the Data Selector Wizard, press F1.

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

Use an ODBC driver to import a text file

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. In the Windows search box type Administrative Tools and press Enter.

2. In the Administrative Tools folder, click the ODBC Data Sources icon corresponding to the version of Windows you are running whether 32-bit or 64-bit. For example, if you are running Windows 64-bit, then you should click ODBC Data Sources (64-bit).

3. In the ODBC Database Administrator dialog box, on the User DSN tab, click Add.

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

5. Enter a name in the Data Source Name.

6. Clear the Use Current Directory check box.

7. Click Select Directory.

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

9. Click OK twice.

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

Import the file

1. On the Data menu, click Custom Import.

2. On the first page of the Data Selector Wizard, click Other OLE DB or ODBC data source and click Next.

   The Data Connection Wizard appears.

3. In the Welcome to the Data Connection Wizard page, click ODBC/DSN and click Next.

4. In the Connect to ODBC Data Source page, select the User DSN that you created in the previous section, 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.

8. Click Finish.

   The Data Selector Wizard reappears.

9. In the Select Data Connection page, do one of the following:

   • To use the .odc file that you just created, and to accept all the rows and columns in the data source, click Finish.

   • To continue using the Data Selector Wizard, so that you can select specific columns and rows, and so that you can create a unique identifier, click Next.

     For more information on each page in the Data Selector Wizard, click Help.

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

Use an OLE DB provider to import data from a SQL Server database

1. On the Data menu, click Custom Import.

2. On the first page of the Data Selector Wizard, click Microsoft SQL Server database.

   The Data Connection Wizard appears.

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

4. Click Next.

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

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.

8. Click Finish.

   The Data Selector Wizard reappears.

9. In the Select Data Connection page, do one of the following:

   • To use the .odc file that you just created, and to accept all the rows and columns in the data source, click Finish.

   • To continue using the Data Selector Wizard, so that you can select specific columns and rows, and so that you can create a unique identifier, click Next.

     For more information on each page in the Data Selector Wizard, press F1.

Use an ODBC driver to import data from a SQL Server database

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. In the Windows search box type Administrative Tools and press Enter.

2. In the Administrative Tools folder, click the ODBC Data Sources icon corresponding to the version of Windows you are running whether 32-bit or 64-bit. For example, if you are running Windows 64-bit, then you should click ODBC Data Sources (64-bit).

3. In the ODBC Database Administrator dialog box, in the User DSN tab, click Add.

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

5. Enter a data source name in the Name box.

6. Optionally, enter a description of the data source in the Description box.

7. Enter the database server name in the Server box.

   If the database is on your computer, enter (local).

8. Click Next.

9. 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 a login ID and password entered by the user, and then enter the database login ID and password in the appropriate boxes.

10. Click Next twice, and then click Finish.

11. To ensure that you entered the correct information, click Test Data Source .

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

13. 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. On the Data menu, click Custom Import.

2. On the first page of the Data Selector Wizard, click Other OLE DB or ODBC data source and click Next.

   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 (the user DSN) 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.

8. Click Finish.

   The Data Selector Wizard reappears.

9. In the Select Data Connection page, do one of the following:

   • To use the .odc file that you just created, and to accept all the rows and columns in the data source, click Finish.

   • To continue using the Data Selector Wizard, so that you can select specific columns and rows, and so that you can create a unique identifier, click Next.

     For more information on each page in the Data Selector Wizard, press F1.

Use an OLE DB provider to import a text file

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. On the Data menu, click Link Data to Shapes.

2. On the first page of the Data Selector Wizard, click Other OLE DB or ODBC data source and click Next.

   The Data Connection Wizard appears.

3. Click Other/Advanced and click Next.

   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.

15. Click Finish.

    The Data Selector Wizard reappears.

16. In the Select Data Connection page, do one of the following:

    • To use the .odc file that you just created and to accept all the rows and columns in the data source, click Finish.

    • To continue using the Data Selector Wizard, so that you can select specific columns and rows, and so that you can create a unique identifier, click Next.

      For more information on each page in the Data Selector Wizard, press F1.

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

Use an ODBC driver to import a text file

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 the Windows Control Panel, double-click the Administrative Tools icon, and then double-click the Data Sources (ODBC) icon.

2. In the ODBC Database 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. On the Data menu, click Link Data to Shapes.

2. On the first page of the Data Selector Wizard, click Other OLE DB or ODBC data source and click Next.

   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.

8. Click Finish.

   The Data Selector Wizard reappears.

9. In the Select Data Connection page, do one of the following:

   • To use the .odc file that you just created, and to accept all the rows and columns in the data source, click Finish.

   • To continue using the Data Selector Wizard, so that you can select specific columns and rows, and so that you can create a unique identifier, click Next.

     For more information on each page in the Data Selector Wizard, press F1.

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

Use an OLE DB provider to import data from a SQL Server database

1. On the Data menu, click Link Data to Shapes.

2. On the first page of the Data Selector Wizard, click Microsoft SQL Server database and click Next.

   The Data Connection Wizard appears.

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

4. Click Next.

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

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.

8. Click Finish.

   The Data Selector Wizard reappears.

9. In the Select Data Connection page, do one of the following:

   • To use the .odc file that you just created, and to accept all the rows and columns in the data source, click Finish.

   • To continue using the Data Selector Wizard, so that you can select specific columns and rows, and so that you can create a unique identifier, click Next.

     For more information on each page in the Data Selector Wizard, press F1.

Use an ODBC driver to import data from a SQL Server database

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 the Windows Control Panel, double-click the Administrative Tools icon, and then double-click the Data Sources (ODBC) icon.

2. In the ODBC Database 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. On the Data menu, click Link Data to Shapes.

2. On the first page of the Data Selector Wizard, click Other OLE DB or ODBC data source and click Next.

   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.

8. Click Finish.

   The Data Selector Wizard reappears.

9. In the Select Data Connection page, do one of the following:

   • To use the .odc file that you just created, and to accept all the rows and columns in the data source, click Finish.

   • To continue using the Data Selector Wizard, so that you can select specific columns and rows, and so that you can create a unique identifier, click Next.

     For more information on each page in the Data Selector Wizard, press F1.