Вернуться   AXForum > Microsoft Dynamics AX > DAX Blogs
Забыли пароль?
Зарегистрироваться Правила Справка Пользователи Сообщения за день Поиск Все разделы прочитаны

Опции темы Поиск в этой теме Опции просмотра
Старый 02.08.2007, 03:50   #1  
Blog bot is offline
Blog bot
23,557 / 800 (74) +++++++
Регистрация: 28.10.2006
dynamicsusers: DL Tips And Tricks: ADO Database Reader (Jet way) - Excel Example

here is the first tip and first post of mine in this blog for reading any data file/database using ADO and jet drivers. You can find proper connection strings on
As an example we will discover how to read Excel file using Jet driver. It's not as flexible as using direct Excel Automation (using Automation you can access field properties and so on), but if You would like to read plain data - it's much faster solution and easy to implement.
Let's look at code:
define next variables:

NameDataTypeSubtypeLengthConnectionAutomation'Microsoft ActiveX Data Objects 2.8 Library'.ConnectionRecordsetAutomation'Microsoft ActiveX Data Objects 2.8 Library'.RecordsetFieldsAutomation'Microsoft ActiveX Data Objects 2.8 Library'.FieldsFieldAutomation'Microsoft ActiveX Data Objects 2.8 Library'.FieldfilenameText250SheetnameText250SELECTSTRINGCONSTSELECT * FROM [%1$]Now let's discover code in a codeunit (OnRun trigger):

//Here we must define a filename to read from
1: filename:='c:.xls';
//Here we must define sheetname of excel file to read
2: sheetname:='Sheet1';

//Now we are creating Connection automation
3: CREATE(Connection);
//Here we define a connection string: in our case it‘s a filename as Data Source
4: Connection.ConnectionString:='Data Source='+filename;

//Define provider (in out case it‘s Jet.OLEDB driver), and additional properties related to Excel
//option (using version 8.0), HDR - means is first row// a header to skip it, if your excel file has a header in a first row - use this option to skip it// "IMEX=1;" tells the driver to always read data as text.
5: Connection.Provider:='Microsoft.Jet.OLEDB.4.0;Extended Properties="Excel 8.0;HDR=No;IMEX=1";';
//Opening the connection
6: Connection.Open();
//Creating Recordset variable
7: CREATE(Recordset);
//Opening connection and read data using SELECT * FROM SheetX way, so we are reading all the
// data from the sheet
8: Recordset.Open(STRSUBSTNO(SELECTSTRING,sheetname),Connection,1,1);
//Now we will start reading Excel file using a loop
//Loop started
//while not end of recordset - read data
//Now we have row data in Fields variable
11: MESSAGE(FORMAT(Fields.Item(i).Value));
//Output Column (i) value in a message box- where i variable is an integer
12: Recordset.MoveNext;
//Move to the next row in a recordset
//Loop finished
//Stop Reading Excel file
//Closing Recordset
14: Recordset.Close;
//Clearing Recordset variable
15: CLEAR(Recordset);
//Closing Connection
16: Connection.Close();
//Clearing Connection automation variable
17: CLEAR(Connection);
Vuala JSo as You can see – it‘s very easy to use, and fast to implement. Don‘t forget that You can use also other drivers to read databases such as (Excel2007, Access, different Sql servers and so on, look at the site provided above).This way of reading data from a database is very useful while creating complex reports inside SQLServer Navision database - so You can use all the power and flexibility of SQL syntax provided by SQLServer. Much higher perfomance/speed comparing to C/AL reporting processing large data amounts.
Also - stored procedures... but it's in next article :)

Objects to this article could be found here: Source

Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору.

Похожие темы
Тема Автор Раздел Ответов Посл. сообщение
Solutions Monkey: Convergence session: Enterprise Portal Deployment Tips and tricks Blog bot DAX Blogs 0 26.03.2009 08:05
Solutions Monkey: Microsoft Dynamics AX 2009 Enterprise Portal / Role Centers - Deployment Tips-n-Tricks – 3 Blog bot DAX Blogs 0 27.10.2008 08:05
Solutions Monkey: Microsofty Dynamics AX 2009 Enterprise Portal / Role Centers - Deployment Tips-n-Tricks - 2 Blog bot DAX Blogs 0 30.09.2008 07:07
Issues concerning X++: X++ Tips and Tricks webinar made available right here... Blog bot DAX Blogs 0 31.07.2007 08:51
Опции темы Поиск в этой теме
Поиск в этой теме:

Расширенный поиск
Опции просмотра

Ваши права в разделе
Вы не можете создавать новые темы
Вы не можете отвечать в темах
Вы не можете прикреплять вложения
Вы не можете редактировать свои сообщения

BB коды Вкл.
Смайлы Вкл.
[IMG] код Вкл.
HTML код Выкл.
Быстрый переход

Часовой пояс GMT +3, время: 20:58.
Powered by vBulletin® v3.8.5. Перевод: zCarot
Контактная информация, Реклама.