topic Re: Configuration of tFileInputDelimited in Talend Studio

Configuration of tFileInputDelimited

idembel2 — Sat, 16 Nov 2024 09:18:21 GMT

Hello All,

I have another chalenge, so may somebody help me please.

I have a csv file which look like that

ID	Name	Comment	Poste
1	toto	tre;gtt	director
2	titi	dftgrtg	ceo
3	tata	dfgtg	technician
4	tutut	ytr;nhhc	ttt
5	lolo	dcvdrf	ttt
6	lala	dfdf	ttt

I configure tFileInputDelimited component like in attached file.

My problem is that in comment field, sometime user can put symbol ; in there comment and this impact my output.

So i think the option "Dynamic setting" of this component can help me to solve it, but i don't know how to do it.

Or if somebody can advice me other solution.

Thanks,

Re: Configuration of tFileInputDelimited

TRF — Tue, 12 Sep 2017 20:45:01 GMT

Can you have Comment as the las field?
In this it should be possible without dynamic schema.

Re: Configuration of tFileInputDelimited

idembel2 — Wed, 13 Sep 2017 08:36:58 GMT

Hello TRF,

Make pleasure to read you.

No i can't, this is the input source i got, i have not hand on the structure.

Thanks,

Re: Configuration of tFileInputDelimited

Sid3 — Wed, 13 Sep 2017 09:46:31 GMT

Question 1: Why your CSV file data in TAB delimited format?

Question 2:if ; is present in your COMMNT field, what you want to do ? do you need to replace this character with blank?

If yes use below function
row1.comment.replaceAll(";","")

if No please help us to understand your query?

Thanks,
Sid
-Mark as solution if this resolved your issue

Re: Configuration of tFileInputDelimited

idembel2 — Wed, 13 Sep 2017 11:17:55 GMT

Hello Si4U,

Response1: No it is not TAB delimited, it is in semi-column delimited file. please check my attached file in my first post

Response2: The situation is not to replace or delete data.

I get this CSV file like input and i should split it to many files regards the Poste type (last column)

So my job looks like that:

tFileInputDelimited ---- > tMap ------ > tFileOutputDelimited (x) === > (x) to say 1,2,3 files

I get wrong output regards of semi_column ";" present in comment column

Thanks,

Re: Configuration of tFileInputDelimited

cterenzi — Wed, 13 Sep 2017 13:41:26 GMT

If you have delimiters in a data value and the value is not wrapped in quotes and the delimiter is not escaped somehow, you have malformed data.

Your options are to have the delimiter changed, get quotes around values or have delimiters within a value escaped somehow.

Re: Configuration of tFileInputDelimited

Irshad1 — Wed, 13 Sep 2017 15:32:43 GMT

Hello

Please check the data in data viewer for proper formatting before loading into target.you will get clear picture where is it mis-matching

Thanks!

Re: Configuration of tFileInputDelimited

TRF — Wed, 13 Sep 2017 23:02:27 GMT

Hi,

Place the following code into a tJavaRow and connect it to a tFileOutputDelimited with "@" (for example) as a separator, you'll get what you expect regarding the comment field.

Edit: I missed to tell you to read the input file using tFileInputFullRow.

String line = input_row.line;
output_row.id = line.substring(0, line.indexOf(';'));
line = line.substring(line.indexOf(';')+1);
output_row.name = line.substring(0, line.indexOf(';'));
output_row.poste = input_row.line.substring(input_row.line.lastIndexOf(';')+1);
line = line.substring(line.indexOf(';')+1);
output_row.comment = line.replace(";" + output_row.poste, "");

Edit: you can now retrieve your file using a classical tFileInputDelimited (with "@" as a separator) with no problem due to the ";" included in comment field.

The 2nd part of the chalenge (1 file per poste value) is an other story.

This post https://community.talend.com/t5/Design-and-Development/Splitting-up-into-multiple-CSV-files-based-on-column-values-then/td-p/76071 and @rhall_2_0 post may help for this.

Re: Configuration of tFileInputDelimited

idembel2 — Tue, 19 Sep 2017 13:57:49 GMT

Hello All,

Sorry for all of this time, i mean it is very important to let you know about this case.

@TRF

Your suggestion works, i test it but the structure i put in my post is just an exemple

So in reality, my source file contains after field comment at least 10 fields.

And it is alos possible that in this field, somebody insert during its comment twice ';' , i have not control on it

I decide to inform about file source formated, i am so in attempt of response.

Thanks,

Re: Configuration of tFileInputDelimited

TRF — Tue, 19 Sep 2017 14:05:45 GMT

You can have 10 or more fields after comment field, the impact is just a few more Java lines in the tJavaRow component.
Also, having more than one ";" in the comment field should not be a problem as we remove all fields arround comment before to get its content as is.
From my point of view it should works.

Re: Configuration of tFileInputDelimited

idembel2 — Wed, 04 Oct 2017 14:35:39 GMT

Hello TRF,

Thank you very much for your suggestion, it works

And i am sorry for this long time to give you feedback.

Re: Configuration of tFileInputDelimited

Anonymous — Tue, 10 Dec 2019 12:24:36 GMT

Hi Team,

I need to process tab delimited file. For input - I am using tfileinputexcel (file extension - .xls)

Getting below error -

[statistics] connecting to socket on port 3796
[statistics] connected
Exception in component tFileInputExcel_1 (cd_extract_job_xls)
org.apache.poi.openxml4j.exceptions.NotOfficeXmlFileException: No valid entries or contents found, this is not a valid OOXML (Office Open XML) file
at org.apache.poi.openxml4j.util.ZipArchiveThresholdInputStream.getNextEntry(ZipArchiveThresholdInputStream.java:144)
at org.apache.poi.openxml4j.util.ZipInputStreamZipEntrySource.<init>(ZipInputStreamZipEntrySource.java:49)
at org.apache.poi.openxml4j.opc.ZipPackage.openZipEntrySourceStream(ZipPackage.java:214)
at org.apache.poi.openxml4j.opc.ZipPackage.openZipEntrySourceStream(ZipPackage.java:198)
at org.apache.poi.openxml4j.opc.ZipPackage.openZipEntrySourceStream(ZipPackage.java:172)
at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:153)
at org.apache.poi.openxml4j.opc.ZipPackage.<init>(ZipPackage.java:123)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:233)
at org.apache.poi.openxml4j.opc.OPCPackage.open(OPCPackage.java:166)
at org.apache.poi.ooxml.POIXMLDocument.openPackage(POIXMLDocument.java:89)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:336)
at team_ja_central_demand.cd_extract_job_xls_0_1.cd_extract_job_xls.tFileInputExcel_1Process(cd_extract_job_xls.java:10358)
at team_ja_central_demand.cd_extract_job_xls_0_1.cd_extract_job_xls.runJobInTOS(cd_extract_job_xls.java:53163)
at team_ja_central_demand.cd_extract_job_xls_0_1.cd_extract_job_xls.main(cd_extract_job_xls.java:53013)
Caused by: java.util.zip.ZipException: Unexpected record signature: 0X6E656449
at org.apache.commons.compress.archivers.zip.ZipArchiveInputStream.getNextZipEntry(ZipArchiveInputStream.java:260)
at org.apache.poi.openxml4j.util.ZipArchiveThresholdInputStream.getNextEntry(ZipArchiveThresholdInputStream.java:140)
... 13 more
[statistics] disconnected

Could you please help me with this why I am getting this error