[转帖] PB如何连接到Excel

ehxz

免费乐捐，感谢支持

	PB如何连接到Excel
	This document describes how to prepare and define an EXCEL data source in order to connect to it using the Intersolv/Merant ExcelWorkbook ODBC driver. It is a supplement to the connection information found in the "Connecting to your database" manual provided with the PowerBuilder documentation. To prepare an Excel Data source for use within PowerBuilder In order for PowerBuilder or InfoMaker to access an Excel data source, the Excel file must be a database. An Excel database is an Excel XLS workbook file that contains one or more named lists. An Excel list is a labeled series of worksheet rows that contain similar information (table). When you use an Excel workbook as a database, each list is analogous to a database table, the list rows correspond to database records, and the list columns correspond to database fields. When you connect to an Excel database in PowerBuilder or InfoMaker, the list names display in the Select Tables list in the Database painter. The first 'row' should be your 'column' names. Carefully look at all of the column headings that you intend to import into the database. SQL rules of column names are more strict than Excel. Therefore you need to modify all column headings to comply with the SQL standards. In most cases this involves removing any embedded blanks and non alphanumeric characters. For example the column name First...Name or First Name will cause errors. The column names should be changed to firstname or first_name. If you can not change the column or table names as suggested above, you can put DelimitIdentifier = 'YES' in the DBParm when you create a profile in PowerBuilder. Make sure your Excel workbook file contains one or more named lists. Your list name should follow standard SQL naming conventions. In order to define your list: In order to define your list, select (highlight) the rows and columns that are to be imported. Select Insert->name->define from the Excel menu bar to define the current workbook selection as a database table or type a name (uppercase) in the Name Field and hit < enter >. It is advised to always use all uppercase characters when naming tables and columns that you want to access in PowerBuilder and InfoMaker and avoid using database-specific reserved words. To define an Excel data source for the Intersolv/Merant ExcelWorkbook In PowerBuilder select the Configure ODBC toolbar item. If this is not on your Toolbar, press on the right mouse button on the powerbar, click on customize and drag the ODBC toolbar item down to the current toolbar. Once you click on the configure ODBC toolbar you will get a dialog box containing the installed ODBC drivers. Scroll down the list and select the Intersolv or Merant ExcelWorkbook and click the Create command button You will be presented with another dialog box. Give your data source a name and enter the full path for the excel database file in the Database workbook including the excel database file. Then click on the DB Profile toolbar. Select the Excel profile. It will display a window with title 'Choose an Excel Workbook as database'. Select the Excel workbook where you have saved your table, click "Open" and connect to the newly created excel data source. Click on the database icon and you should see your saved excel spreadsheets as tables. If there are no tables in the list: Make sure the directory where the spreadsheets are located is set correctly in the ODBC datasource. Makes sure you highlighted the rows and columns in the spreadsheet. Makes sure you followed the above described instructions on how to create one or more lists in your workbook. Common Error messages Cannot open workbook nnnn.xls , OLE error code (32) Excel is still open, close Excel and you should be able to connect. Select error : SQLSTATE = S1000 [INTERSOLV] [ODBC EXCEL DRIVER] Cannot open workbook nnnn.xls , OLE error code (80) The Excel file was created with a previous version of Microsoft excel. You should update it to current MS Excel format. Select error : SQLSTATE = S0002 [INTERSOLV] [ODBC EXCEL DRIVER] the table nnnn was not found in the workbook nnnn Table names do not comply with the SQL standards. Use uppercase characters to name your table OR set DelimitIdentifier = 'YES' in the DBParm, in the database Profile for your Excel connection. Do not use database-specific reserved words (eg: Naming an excel table 'DATABASE'). Select error : SQLSTATE = S0022 INTERSOLV] [ODBC EXCEL DRIVER] Field not found: nnnn Column names do not comply with the SQL standards. Change them to follow the SQL standards OR set DelimitIdentifier = 'YES' in the DBParm, in the DataBase Profile for your Excel connection. table nnnn has no columns, possible invalid table format Intersolv driver does not recognize input as a valid Excel file.

 

looks

可否增加一个中文的呢?