AV7076.doc - Importing a Database in Avery DesignPro 4.0 using
Contents
1. Should you receive an error message such as Error saving attributes from nul to nul you can safely ignore this Click OK and proceed The ODBC text driver now understands the file structure For the fix for incorrect data type recognition please refer to the next topic Please note that incorrect data detection can cause various error messages so it is always a good idea to verify the data types for all fields Bar Codes or Postal Codes from a Text Data Merge File are Displayed Incorrectly Problem 3 The numeric bar code data is ina CSV or TXT file as a field This numeric data appears in decimal or scientific notation and therefore generates errors for the bar codes Solution Please follow the steps from the previous topic In addition to the field separator symbol you can define the data type of any field in your list This is important for example if you have numeric bar code data or postal codes which the ODBC driver would normally interpret as numeric but must use this as text Set the field format in ODBC to type Char Character for correct results After you changed settings for a field type length be sure to confirm the changes by clicking Update Modify otherwise your changes will be lost Error Messages when Merging Microsoft Excel and Microsoft Access Files Problem 4 When trying to merge a data file in DesignPro 4 0 error messages are received about invalid data in columns or fields Solution Some2. MyD atabase System My Customer Database Visual FoxPro Database User Visual FoxPro Tables User Machine Data Source is specific to this machine and cannot be shared User data sources are specific to a user on this machine System data sources can be used by all users on this machine or by a system wide service In the Select Table File dialog box a Microsoft Access database has one or more data tables and a number of internal system tables Note If working with a Microsoft Excel spreadsheet one or more worksheets are listed MSysRelationships Options 13 Select a table from the dialog box The database is now connected to your current DesignPro 4 0 design file AVERY Technical Bulletin AV7076a Copyright 2003 Avery Dennison 012907 2j kw Page 4 Avery Dennison Avery Software Support 888 835 8379 Note Click Options and uncheck all check boxes except Tables to display only the data tables in this dialog box 14 Click OK 15 To insert one or more fields into the design select Database gt Insert Field 16 Highlight a field name and click Insert The field name is inserted in a text box on your design You can add line breaks and apply formatting to the design as usual 17 To view the merged database with the design select Database gt Display Field Contents Use the Next Record button on the tool bar to view the individual records Note If you want to disconnect th
3. Zip Code Fields Merging Incorrectly Problem 5 When merging a Microsoft Excel spreadsheet into DesignPro 4 0 using ODBC a decimal and a zero is added to the zip code numbers or leading zeros are dropped for zip codes beginning with zero Solution A possible solution would be to add an apostrophe prior to each zip code number in each cell within Microsoft Excel This would treat each cell as text Unfortunately you cannot simply reformat the cells as text fields Each cell must individually contain an apostrophe prior to the zip code number To automate the addition of the apostrophes a macro could be written in Microsoft Excel Consult the Microsoft Excel User Manual for more information on macros Important Note If you plan on using bar codes the above solution will not work Because the zip code fields are now treated as text they cannot be interpreted as numbers which is required for the bar code feature in DesignPro We recommend that you save the Microsoft Excel spreadsheet as a dBase III file and use the Open a dBase III Database option in DesignPro If you need further assistance please call Avery Software Support at 888 835 8379 Avery Zweckform and all other Avery brands product names and codes are trademarks of Avery Dennison Corporation or Avery Dennison Zweckform Office Products Europe GmbH All other brands and product names are the trademarks of their respective companies AVERY Technical Bulletin AV7076a
4. Copyright 2003 Avery Dennison 012907 2jkw Page 7
5. Data Source Name O OE field For future use with other DesignPro 4 0 designs or Cancel 7 p Database ee other applications ODBC will list this database in the Database Help a e Select Data Source dialog box r System Database ae 8 Enter a description for your database in the Description Penis field System Database Options gt gt 9 Click Select to connect the physical database file to the data source 10 Navigate to your Microsoft Access mdb file and select it Database Name Directories 1 dbt mdo Cis c my documents 2a Click OK Help I Read Only a T Exclusive x E cS E gt My Documents List Files of Type Drives Access Databases mr 7 c Network E aa 1 11 Click OK to confirm your data source definition The data Data Source Name MyDatabase OK songs EE Csonelonee source definition is now complete Cancel m Database Database c My Documents db1 mdb Help Create Repair Compact Advanced System Database None C Database System Database Options gt gt Select Data Source tkd 12 Select your database and click OK File Data Source Machine Data Source Data Source Name Type Description dBASE Files User dBase Files Word User Excel Files User FoxPro Files Word User MOIS System MSMO Information Server Database MS Access Database User
6. applications allow the definition of field names which don t meet the standards required by the Microsoft ODBC driver This is even true for Microsoft applications such as Microsoft Excel and Microsoft Access Typical problems are spaces or special characters in field names Microsoft Excel spreadsheet field names need to have no spaces or special characters Field names are in the first row of the spreadsheet In some cases it is better to Save As a CSV comma separated file in some countries the semicolon is used as a separator and then open with the Text Files option instead of the Microsoft Excel Files option on the Machine Data Source tab AVERY Technical Bulletin AV7076a Copyright 2003 Avery Dennison 012907 2j kw Page 6 Avery Dennison Avery Software Support 888 835 8379 A work around for non standard Microsoft Access field names is to create a Microsoft ACCESS query to redefine the field names and then merge the query Example You have two fields in a Microsoft Access database and they are e First Name e Last Name Microsoft Access accepts these as valid field names but they cause trouble for the Microsoft ODBC driver Define a query in Microsoft Access Design Mode and define the two fields in the Field row as follows FirstName First Name LastName Last Name Note You can create field names without spaces or use the underscore _ character to create field names such as First_Name
7. this machine only Selecting System Data Source creates a data source which is specific to this machine and usable by any user who logs onto this machine EEE 5 Select an ODBC driver for the file type to be merged In ec aa eed el de this example to connect a Microsoft Access database Microsoft Paradox Driver db 4 00 4202 00 Microsoft Text Driver txt csv 4 00 4202 00 Lie 4 00 420200 select Microsoft Access Driver md b i CI ick Next Microsoft dBase Driver dbf 4 00 4202 00 Microsoft dBase YFP Driver dbf 6 00 8440 00 Mictosot FoxPro VFP Diver dbi 6 00 8440 00 ae l DA Microsol ODBC for Oracle 2573420200 Note For additional assistance consult your Microsoft Office I 400 4202 00 manual or contact your System Administrator for additional pan 270 ne an i information e Create New Data Source x 6 The ODBC system confirms the selection Click Finish to create the data source In the next step you will define the details for the data source When you click Finish you will create the data source which you have just configured The driver may prompt you for more information System Data Source Driver Microsoft Access Driver mdb AVERY Technical Bulletin AV7076a Copyright 2003 Avery Dennison 012907 2j kw Page 3 Avery Dennison Avery Software Support 888 835 8379 buaSouceNane WERE 7 Enter a name for your database in the
8. Avery Dennison Avery Software Support 888 835 8379 Opening a Database in Avery DesignPro 4 0 using ODBC What is ODBC Why should you Open an External Database using ODBC How to Open and Link a Database to a DesignPro 4 0 Project using ODBC Troubleshooting Problem 1 Error message No columns found in data file Problem 2 Merging a CSV or TXT data file does not work An error message is displayed saying that no columns could be identified in the file Problem 3 The numeric bar code data is ina CSV or TXT file as a field This numeric data appears in decimal or scientific notation and therefore generates errors for the bar codes Problem 4 When trying to merge a data file in DesignPro 4 0 error messages are received about invalid data in columns or fields Problem 5 When merging a Microsoft Excel spreadsheet into DesignPro 4 0 using ODBC a decimal and a zero is added to the zip code numbers or leading zeros are dropped for zip codes beginning with zero AVERY Technical Bulletin AV7076a Copyright 2003 Avery Dennison 012907 2jkw Page 1 Avery Dennison Avery Software Support 888 835 8379 What is ODBC ODBC stands for Open DataBase Connectivity It is a tool that takes databases from different programs and puts them in a standard format This is accomplished by selecting a driver associated with a particular file type An appropriate ODBC filter must be installed on your computer for the D
9. e database from your design select Database gt Deactivate Troubleshooting When working with database files and importing information from databases certain settings have to be exact in order for the merge to work correctly The following are examples of incorrect settings or invalid data errors and the solutions to correct them Problem 1 Error message No columns found in data file Important note In case of unusual error messages it is very important to first verify that the filename of your data merge file does not contain e Additional dot characters except for the separator to the filename extension For example mergefile txt is acceptable but merge file txt will fail e Accented or extended characters These are issues with the Microsoft ODBC drivers that may be experienced with some ODBC and Microsoft Windows versions They cannot be fixed by making any changes to DesignPro 4 0 Problem 2 Merging a CSV or TXT data file does not work An error message is displayed saying that no columns could be identified in the file This message is caused by the ODBC driver which interprets the merge file DesignPro 4 0 receives the error condition and is unable to understand the file format This must be solved by setting up the ODBC Text Driver with appropriate options for the data source The ODBC driver cannot detect fields columns in the data merge file There are conditions where ODBC does not use a default
10. esignPro Open an External Database via ODBC feature to work Important Note Installation of an ODBC filter usually occurs as part of the normal Microsoft Office installation Consult your Microsoft Office manual or contact your System Administrator for additional information Why should you Open an External Database using ODBC When using the Database gt Open feature of DesignPro two options are present Open a dBASE Ill Database or Open an External Database via ODBC Consumers should select the ODBC option to link their database to a DesignPro 4 0 project when e Their database was created in a program that does not provide a Save As dBASE III option e They wish to update their database i in the original application which automatically updates the linked database information in DesignPro When a database is opened in DesignPro 4 0 using the ODBC option the data content and field names are directly linked with the original application such as Excel or Access Therefore changes made in the original database are updated in the DesignPro project which is linked to that database The following two sections are provided in this document e Howto Open and Link a Database to a DesignPro 4 0 Project using ODBC e Troubleshooting How to Open and Link a Database to a DesignPro 4 0 Project using ODBC The following illustrates the steps for opening and linking a database from within DesignPro using the ODBC filter on
11. field separator symbol in Windows named List Separator Symbol which is normally either the comma for the U S Canada the UK Australia or the semicolon for Continental Europe The list separator symbol depends on the Microsoft Windows International Settings in the Windows Control Panel A second reason for this problem may be incorrect data type recognition The ODBC driver may detect data to be numeric and assigns a numeric data type to a field while the actual data needs to be read as a text string character type Solution To solve the first problem set the needed options in the ODBC configuration as follows In the ODBC Text Setup dialog box where you enter the data source name and description do the following AVERY Technical Bulletin AV7076a Copyright 2003 Avery Dennison 012907 2j kw Page 5 Avery Dennison Avery Software Support 888 835 8379 Note Please note that some operating systems and ODBC versions may not have the same options or the options listed below may have slightly different names In these cases review the dialog box for options with similar meaning 1 Click Options 2 Deselect Use current directory 3 Click Browse Directory and navigate to the directory where your merge file is located 4 Click Define Format 5 Select your file from the Table list 6 Select Format gt User defined and enter the list separator symbol e g type the comma into the field 7 Click OK to confirm
12. your computer In this example a Microsoft Access database is opened Note Microsoft Excel databases can also be opened using the ODBC feature following the same steps Open Database 1 Select Database gt Open 2 The Open Database dialog box appears Click Open an Open a dBASE Ill Database External Database via ODBC Note These steps are required only once for each data file whei using this merge feature in DesignPro 4 0 Open an External Database via ODBC Cancel Help AVERY Technical Bulletin AV7076a Copyright 2003 Avery Dennison 012907 2j kw Page 2 Avery Dennison Avery Software Support 888 835 8379 Select Data Source 20x 3 Select the Machine Data Source tab If you previously created a Data Source Name listed here select it and File Data Source Machine Data Source BAEfes Ue click OK Then proceed to step 13 pain Use If you never created a Data Source Name click New Continue with step 4 FoxPro Files Word User MOIS System MSMO Information Server Database MS Access Database User Visual FoxPro Database User Visual FoxPro Tables User A Machine Data Source is specific to this machine and cannot be shared User data sources are specific to a user on this machine System data sources can be used by all users on this machine or by a system wide service 4 Select System Data Source Click Next Select a type of data source User Data Source Applies to
Download Pdf Manuals
Related Search