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

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

Anonymous — Mon, 07 May 2018 05:17:01 GMT

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.

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

manodwhb — Mon, 07 May 2018 05:34:02 GMT

@samisyed80,have you ticked Include Header in Basic settings of tFileOutputExcel?

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

Anonymous — Mon, 07 May 2018 05:40:59 GMT

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

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

manodwhb — Mon, 07 May 2018 05:48:57 GMT

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

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

Anonymous — Mon, 07 May 2018 05:52:25 GMT

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

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

manodwhb — Mon, 07 May 2018 06:25:32 GMT

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

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

Anonymous — Mon, 07 May 2018 06:34:24 GMT

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?

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

manodwhb — Mon, 07 May 2018 06:39:37 GMT

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

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

Anonymous — Mon, 07 May 2018 06:45:34 GMT

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.

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

manodwhb — Mon, 07 May 2018 06:53:01 GMT

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

-Xms512m
-Xmx1536m

testJamal.zip

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

manodwhb — Mon, 07 May 2018 06:54:49 GMT

@samisyed80,please find the out file too.

Test_data.xlsx

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

Anonymous — Mon, 07 May 2018 07:01:04 GMT

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?

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

manodwhb — Mon, 07 May 2018 07:03:29 GMT

@samisyed80,mybe output having issue.

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

manodwhb — Mon, 07 May 2018 07:06:51 GMT

@samisyed80,what is existing output file extention?

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

Anonymous — Mon, 07 May 2018 07:25:48 GMT

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

It ran once without any issue, now its throwing error

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

Anonymous — Mon, 07 May 2018 10:59:40 GMT

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.

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

manodwhb — Mon, 07 May 2018 11:09:23 GMT

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

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

Anonymous — Mon, 07 May 2018 11:18:32 GMT

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.

OCM Measure Tool Sheet.xlsx

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

Anonymous — Mon, 07 May 2018 12:00:45 GMT

Hi Mano, did you received my attached xlsx file?

OCM Measure Tool Sheet.xlsx

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

manodwhb — Mon, 07 May 2018 12:04:44 GMT

@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