Pull the data from MSSQL database and load it in Excel file

Eight Stars

Pull the data from MSSQL database and load it in Excel file

Hello,

 

My task is to pull the data from MSSQL database and load it in a particular sheet of Excel file. This Excel file is having more than 10 sheets. When i tried to load the data using tfileoutputexcel, i am able to do it but the header in the existing sheet of a excel file and other sheets are getting lost. I have posted many queries regarding this in Talend community but no proper answer from anyone. Is it possible to reach Talend Admin thru Call? I want to know whether its possible in Talend or not.

Forteen Stars

Re: Pull the data from MSSQL database and load it in Excel file

@samisyed80,have you ticked Include Header in Basic settings ofUntitled.png tFileOutputExcel?

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Eight Stars

Re: Pull the data from MSSQL database and load it in Excel file

Thanks Manohar for replying back. I have tried this, but its pulling only that input tables header but I want the header which is there in the excel file. Also, other sheets are getting lost. Its a xlsx file with some macro codings in it. Yes, i have used tFileOutputExcel

Forteen Stars

Re: Pull the data from MSSQL database and load it in Excel file

@samisyed80,Did you ticked Append existing file in Basic settings of tFileOutputExcel?

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Eight Stars

Re: Pull the data from MSSQL database and load it in Excel file

Yes, i have tried append existing file option too. Still, the same erroneous result 

Forteen Stars

Re: Pull the data from MSSQL database and load it in Excel file

@samisyed80,if you want to append data into the existing sheet means you have to follow below setting and working fine.

Untitled.png

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Eight Stars

Re: Pull the data from MSSQL database and load it in Excel file

Before going to the main question. I am facing an issue when i select "Write excel2007 file format (xlsx)" option. The entire system gets hanged. Please help me with this.

 

Secondly, i have given the settings like you gave. Other sheets are gone missing. Did you faced the similar issue when you did a test run?

Forteen Stars

Re: Pull the data from MSSQL database and load it in Excel file

@samisyed80,i am not at all faciing the issue wih when i select "Write excel2007 file format (xlsx)" option. it could your hardware issue.

I have not faced any issue and the data was appending to the existing sheet and not removing Headers also.

 

if you need job export please let me know.

 

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Eight Stars

Re: Pull the data from MSSQL database and load it in Excel file

Yes please , i need job export. 

 

Also, can you tell me the size you are using in Xmx and Xms

 

I have also used tMap in between as i need to shrink the column names in the output excel.

Forteen Stars

Re: Pull the data from MSSQL database and load it in Excel file

@samisyed80,please find the attached export. and i have used below allocation.

-Xms512m
-Xmx1536m

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Forteen Stars

Re: Pull the data from MSSQL database and load it in Excel file

@samisyed80,please find the out file too.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Eight Stars

Re: Pull the data from MSSQL database and load it in Excel file

When i tested with new excel (xlsx) then transmission is getting completed successfully. But, when i use the macro-ed xlsx file its throwing an error. Looks like there's some issue with the output file. What do you think?

 

Edited Message

 

Oops the new xlsx is throwing some error.

 

"The maximum number of Cell Styles was exceeded. You can define up to 64000 style in a .xlsx Workbook"

 

what could be this issue?

Forteen Stars

Re: Pull the data from MSSQL database and load it in Excel file

@samisyed80,mybe output having issue.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Forteen Stars

Re: Pull the data from MSSQL database and load it in Excel file

@samisyed80,what is existing output file extention?

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Eight Stars

Re: Pull the data from MSSQL database and load it in Excel file

existing output file extn is xlsx.... the test data output is also in xlsx.

 

It ran once without any issue, now its throwing error

Eight Stars

Re: Pull the data from MSSQL database and load it in Excel file

if i click the "Append Existing file" or "Keep existing cell format" option in tfileoutputexcel then the graph run is getting hanged. May i know the reason? I am going to load a data to excel file which has more sheets. Please help me resolve this.

Forteen Stars

Re: Pull the data from MSSQL database and load it in Excel file

@samisyed80,is it possiable to share the excel file,which was not appending to you?

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Eight Stars

Re: Pull the data from MSSQL database and load it in Excel file

Thanks for your prompt response. Please find the attached file in which i am trying to load data from database.

 

When i opened this file for the first time, it shown me some error. Then i removed the first sheet which is problematic. Then i tried to upload the "Demographics" data from database. Though i got successful couple of time, but i am losing the other sheets and the top row information in "Demographics" sheet. Please help in this regard.

Eight Stars

Re: Pull the data from MSSQL database and load it in Excel file

Hi Mano, did you received my attached xlsx file?

Forteen Stars

Re: Pull the data from MSSQL database and load it in Excel file

@samisyed80,yes i have recived but i am getting below error. i know that for this error java heap size need to be increase.

 

Exception in thread "main" java.lang.OutOfMemoryError: GC overhead limit exceeded
at org.apache.xmlbeans.impl.store.Cur$CurLoadContext.attr(Cur.java:3044)
at org.apache.xmlbeans.impl.store.Cur$CurLoadContext.attr(Cur.java:3065)
at org.apache.xmlbeans.impl.store.Locale$SaxHandler.startElement(Locale.java:3263)
at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.reportStartTag(Piccolo.java:1082)
at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseAttributesNS(PiccoloLexer.java:1822)
at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseOpenTagNS(PiccoloLexer.java:1521)
at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseTagNS(PiccoloLexer.java:1362)
at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.yylex(PiccoloLexer.java:4682)
at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yylex(Piccolo.java:1290)
at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yyparse(Piccolo.java:1400)
at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.parse(Piccolo.java:714)
at org.apache.xmlbeans.impl.store.Locale$SaxLoader.load(Locale.java:3479)
at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1277)
at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1264)
at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.parse(SchemaTypeLoaderBase.java:345)
at org.openxmlformats.schemas.spreadsheetml.x2006.main.WorksheetDocument$Factory.parse(Unknown Source)
at org.apache.poi.xssf.usermodel.XSSFSheet.read(XSSFSheet.java:194)
at org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead(XSSFSheet.java:186)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:354)
at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:166)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:240)
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:87)
at org.talend.ExcelTool.prepareXlsxFile(ExcelTool.java:110)
at atos.testjamal_0_1.testJamal.tFixedFlowInput_2Process(testJamal.java:537)
at atos.testjamal_0_1.testJamal.runJobInTOS(testJamal.java:1045)
at

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Eight Stars

Re: Pull the data from MSSQL database and load it in Excel file

there was another communication i have created for this java heap size error. DId you deleted the first sheet which contains only story? if not then delete it and then try. It will run. I just want to know, these issues are due to excel or any other reason?

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog

How Media Organizations Achieved Success with Data Integration

Learn how media organizations have achieved success with Data Integration

Read

Agile Data lakes & Analytics

Accelerate your data lake projects with an agile approach

Watch