# How increases the performance between excel to datasource

**URL:** <https://www.dynamicsuser.net/t/how-increases-the-performance-between-excel-to-datasource/45121>\
**Category:** User Forum\
**Created:** [April 5, 2013, 1:12pm UTC](https://www.dynamicsuser.net/t/how-increases-the-performance-between-excel-to-datasource/45121 "2013-04-05T13:12:20Z")\
**Posts on this page:** 8\
**Page:** 1

<div class="post-metadata">

**Author:** ![Manimaran](https://avatars.discourse-cdn.com/v4/letter/m/8e7dd6/32.png) [@Manimaran](https://www.dynamicsuser.net/u/Manimaran)\
**Post date:** [April 5, 2013, 1:12pm UTC](https://www.dynamicsuser.net/t/how-increases-the-performance-between-excel-to-datasource/45121/1 "2013-04-05T13:12:20Z")

</div>

I have a excel with 1000 records i need to fetch that and insert in to my table i used the normal coding and also insert\_set concept but my performace of that program is too slow…

Could any one Can Help me to increases the performance of data transfer between excel to table

---

<div class="post-metadata">

**Author:** ![Faisal\_Raja](https://avatars.discourse-cdn.com/v4/letter/f/c68b51/32.png) [@Faisal\_Raja](https://www.dynamicsuser.net/u/Faisal_Raja)\
**Post date:** [April 5, 2013, 1:18pm UTC](https://www.dynamicsuser.net/t/how-increases-the-performance-between-excel-to-datasource/45121/2 "2013-04-05T13:18:58Z")

</div>

Can you show us the code which have you used for data importing ?

Regards,

Faisal Raja J

---

<div class="post-metadata">

**Author:** ![Manimaran](https://avatars.discourse-cdn.com/v4/letter/m/8e7dd6/32.png) [@Manimaran](https://www.dynamicsuser.net/u/Manimaran)\
**Post date:** [April 5, 2013, 1:33pm UTC](https://www.dynamicsuser.net/t/how-increases-the-performance-between-excel-to-datasource/45121/3 "2013-04-05T13:33:13Z")

</div>

Lev\_CompanyDetails Table,tabletmp;  
sysexcelapplication application;  
sysexcelworkbooks workbooks;  
sysexcelworkbook workbook;  
sysexcelworksheets worksheets;  
sysexcelworksheet worksheet;  
sysexcelcells cells;  
sysexcelrange range,totrange;  
comvarianttype type;  
filenameopen fopen;  
dialog dia;  
dialogfield diafield;  
int row,i;  
#excel  
#avifiles  
str COMVariant2Str(COMVariant \_cv, int \_decimals = 0, int \_characters = 0, int \_separator1 = 0, int \_separator2 = 0)  
{  
switch (\_cv.variantType())  
{  
case (COMVariantType::VT\_BSTR):  
return \_cv.bStr();

case (COMVariantType::VT\_R4):  
return num2str(\_cv.float(),\_characters,\_decimals,\_separator1,\_separator2);

case (COMVariantType::VT\_R8):  
return num2str(\_cv.double(),\_characters,\_decimals,\_separator1,\_separator2);

case (COMVariantType::VT\_DECIMAL):  
return num2str(\_cv.decimal(),\_characters,\_decimals,\_separator1,\_separator2);

case (COMVariantType::VT\_DATE):  
return date2str(\_cv.date(),123,2,1,2,1,4);

case (COMVariantType::VT\_EMPTY):  
return “”;

default:  
throw error(strfmt("@SYS26908", \_cv.variantType()));  
}  
return “”;  
} ;  
tabletmp.setTmp();  
dia=new dialog(“Select the File”);  
diafield=dia.addField(typeid(filenameopen));  
if(dia.run())  
{  
fopen=diafield.value();  
info(strfmt(“Select File is :%1”,fopen));  
application=sysexcelapplication::construct();  
workbooks=application.workbooks();  
workbooks.open(fopen);  
info(“Your File Successfully Opened…”);  
workbook=workbooks.item(1);  
worksheets=workbook.worksheets();  
worksheet=worksheets.itemFromNum(1);  
cells=worksheet.cells();  
totrange=cells.range(#ExcelDatarange);  
[//totrange=worksheet.cells](https://totrange=worksheet.cells)().range(#ExcelDataRange);  
range = totrange.find("\*", null, #xlFormulas, #xlWhole,#xlByRows,#xlPrevious);  
info(strfmt(“Number Rows to be copied is: %1”,range.row()));  
row=range.row();  
for(i=2;i\<=row;i++)  
{  
tabletmp.Company\_id= str2int(COMVariant2Str(cells.item(i,1).value()));  
tabletmp.Company\_Name=comvariant2str(cells.item(i,2).value());  
tabletmp.insert();

info(strfmt("%1 row inserted",i));  
}  
insert\_recordset table (Company\_id,Company\_Name) select Company\_id,Company\_Name from tabletmp;  
info(“Process Completed”);  
application.workbooks().close();  
application.quit();

}

---

<div class="post-metadata">

**Author:** ![Manimaran](https://avatars.discourse-cdn.com/v4/letter/m/8e7dd6/32.png) [@Manimaran](https://www.dynamicsuser.net/u/Manimaran)\
**Post date:** [April 5, 2013, 1:43pm UTC](https://www.dynamicsuser.net/t/how-increases-the-performance-between-excel-to-datasource/45121/4 "2013-04-05T13:43:11Z")

</div>

sry faisal i can’t send full progrAM

Lev\_CompanyDetails Table,tabletmp;  
filenameopen fopen;  
dialog dia;  
dialogfield diafield;  
int row,i;  
#excel  
#avifiles  
str COMVariant2Str(COMVariant \_cv, int \_decimals = 0, int \_characters = 0, int \_separator1 = 0, int \_separator2 = 0)  
{  
switch (\_cv.variantType())  
{  
case (COMVariantType::VT\_BSTR):  
return \_cv.bStr();

case (COMVariantType::VT\_R4):  
return num2str(\_cv.float(),\_characters,\_decimals,\_separator1,\_separator2);

case (COMVariantType::VT\_R8):  
return num2str(\_cv.double(),\_characters,\_decimals,\_separator1,\_separator2);

case (COMVariantType::VT\_DECIMAL):  
return num2str(\_cv.decimal(),\_characters,\_decimals,\_separator1,\_separator2);

case (COMVariantType::VT\_DATE):  
return date2str(\_cv.date(),123,2,1,2,1,4);

case (COMVariantType::VT\_EMPTY):  
return “”;

default:  
throw error(strfmt("@SYS26908", \_cv.variantType()));  
}  
return “”;  
} ;  
tabletmp.setTmp();  
dia=new dialog(“Select the File”);  
diafield=dia.addField(typeid(filenameopen));  
if(dia.run())  
{  
fopen=diafield.value();  
info(strfmt(“Select File is :%1”,fopen));  
application=sysexcelapplication::construct();  
workbooks=application.workbooks();  
workbooks.open(fopen);  
info(“Your File Successfully Opened…”);  
workbook=workbooks.item(1);  
worksheets=workbook.worksheets();  
worksheet=worksheets.itemFromNum(1);  
cells=worksheet.cells();  
totrange=cells.range(#ExcelDatarange);  
range = totrange.find("\*", null, #xlFormulas, #xlWhole,#xlByRows,#xlPrevious);  
info(strfmt(“Number Rows to be copied is: %1”,range.row()));  
row=range.row();  
for(i=2;i\<=row;i++)  
{  
tabletmp.Company\_id= str2int(COMVariant2Str(cells.item(i,1).value()));  
tabletmp.Company\_Name=comvariant2str(cells.item(i,2).value());  
tabletmp.insert();

info(strfmt("%1 row inserted",i));  
}  
insert\_recordset table (Company\_id,Company\_Name) select Company\_id,Company\_Name from tabletmp;  
info(“Process Completed”);  
application.workbooks().close();  
application.quit();

}

---

<div class="post-metadata">

**Author:** ![Manimaran](https://avatars.discourse-cdn.com/v4/letter/m/8e7dd6/32.png) [@Manimaran](https://www.dynamicsuser.net/u/Manimaran)\
**Post date:** [April 5, 2013, 2:00pm UTC](https://www.dynamicsuser.net/t/how-increases-the-performance-between-excel-to-datasource/45121/5 "2013-04-05T14:00:35Z")

</div>

help me any one pls

---

<div class="post-metadata">

**Author:** ![Manimaran](https://avatars.discourse-cdn.com/v4/letter/m/8e7dd6/32.png) [@Manimaran](https://www.dynamicsuser.net/u/Manimaran)\
**Post date:** [April 5, 2013, 2:00pm UTC](https://www.dynamicsuser.net/t/how-increases-the-performance-between-excel-to-datasource/45121/6 "2013-04-05T14:00:20Z")

</div>

help me any one help

---

<div class="post-metadata">

**Author:** ![Faisal\_Raja](https://avatars.discourse-cdn.com/v4/letter/f/c68b51/32.png) [@Faisal\_Raja](https://www.dynamicsuser.net/u/Faisal_Raja)\
**Post date:** [April 5, 2013, 2:21pm UTC](https://www.dynamicsuser.net/t/how-increases-the-performance-between-excel-to-datasource/45121/7 "2013-04-05T14:21:29Z")

</div>

Why are you using temptable for inserting record into your table?

Why can’t you insert directly into your table ?

Share me your email Id, I will send you an xpo of sample data import.

Regards,  
Faisal Raja J

---

<div class="post-metadata">

**Author:** ![Manimaran](https://avatars.discourse-cdn.com/v4/letter/m/8e7dd6/32.png) [@Manimaran](https://www.dynamicsuser.net/u/Manimaran)\
**Post date:** [April 5, 2013, 2:57pm UTC](https://www.dynamicsuser.net/t/how-increases-the-performance-between-excel-to-datasource/45121/8 "2013-04-05T14:57:09Z")

</div>

i used that also faisal hat also takes same timing to complete the process

anyway give ur idea man

[manimaran.kasi@levergent.com](mailto:manimaran.kasi@levergent.com)