Seven 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.

20 REPLIES
Twelve 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
Seven 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

Twelve 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
Seven 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 

Twelve 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
Seven 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?

Twelve 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
Seven 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.

Twelve 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
Twelve Stars

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

@samisyed80,please find the out file too.

Manohar B
Seven 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?

Twelve Stars

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

@samisyed80,mybe output having issue.

Manohar B
Twelve Stars

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

@samisyed80,what is existing output file extention?

Manohar B
Seven 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

Seven 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.

Twelve 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
Seven 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.

Seven Stars

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

Hi Mano, did you received my attached xlsx file?

Twelve 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
Seven 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?