topic Re: count columns in excel in Talend Studio

count columns in excel

Anonymous — Sat, 16 Nov 2024 12:37:09 GMT

how can i do to retrive the number of columxn readed in a excel file ? i saw TFileRowCount but not the column count...

Re: count columns in excel

Anonymous — Tue, 18 Oct 2011 13:45:41 GMT

Hi Pellegrino,
With a little bit of simple coding. Just use a tFileExcelInput and set only one column as dynamic type.
Then using the dynamic type functions you should be able to count the number of columns that it retrieved.

Re: count columns in excel

Anonymous — Tue, 18 Oct 2011 13:58:20 GMT

thank you vgalopin,
but where should i set dynamic coloumns?
in the component-> dynamic settings i have all buttons disabled. is becouse i'm using the TOS?

Re: count columns in excel

Anonymous — Tue, 18 Oct 2011 14:39:40 GMT

Just edit the schema of the component, and add one column, and for type choose Dynamic.

Re: count columns in excel

Anonymous — Wed, 19 Oct 2011 11:38:35 GMT

maybe i'm noob, but i cant find the option dynamic on the type of the column..
are you using the TIS? i have the tos...

Re: count columns in excel

Anonymous — Wed, 19 Oct 2011 14:27:04 GMT

Oh... yes...

Re: count columns in excel

Anonymous — Wed, 19 Oct 2011 16:13:42 GMT

so is not possible to cout the column of a file with the tos? -.-"

Re: count columns in excel

Anonymous — Wed, 19 Oct 2011 16:30:10 GMT

Actually you could, but you would have to type some java code for that.
See some info here:
http://stackoverflow.com/questions/6663367/how-to-count-the-number-of-columns-in-excel-file-that-are-occupied-by-data-usin
http://www.dreamincode.net/forums/topic/182398-using-excel-java-excel-api/
You can load the library needed using the tLibraryLoad component

Re: count columns in excel

Anonymous — Thu, 27 Oct 2011 13:42:34 GMT

The Excel file is as below
---------------------------------
CPT ICDCOVERED
87088 003.1
038.0, 038.10-038.11, 038.19, 038.2, 038.3, 038.40-038.44, 038.49, 038.8, 038.9
276.2
276.4
286.6
288
288.01
288.02
288.03
288.04
288.09
288.8
306.53
306.59
518.82
570
580.0-580.9
583.0-583.9

My input is Excel file (attached)
The Expected Output is to load the values into temp table (SQL SERVER).
I am attaching the Excel file, the values in the first column "CPT" and the values in the second cloumn ICDCOVERED , I need to load these values.
The first cloumn and the second cloumn have 1 to many relationship,
Problem 1:
In the Excel file when does not a value in the first column, that it show continue to load the data until it comes across the next value.
Problem 2:
In the second column (ICDCOVERED), some cell values are individual values like
(003.1), some cells have multiple values like (606.02, 607.01) and some cells have values ranges for example: (503.02-503.09).

My objective is to load the data one value in each row of the temp table, when it is multiple values in the cloumn it should load values into seperate records, and when it is a range it should query the range from the database table get the range of values and load the values into seperate records in the Temp Table, this is regarding the ICDCOVERED colum of the Excel file.
Now for the CPT column of the Excel file it should maitain the 1-many relationship
while loading the data.
Hope I am clear now.
I am really not able undersatnd the way I can accomplish this.
Sorry I am not able to pose this question on the forum, I am confused where I have to type
my question in the forum.
Please I would appreciate if you can reply back to this e-mail address.
Ali

Re: count columns in excel

Anonymous — Mon, 20 Jan 2020 08:31:57 GMT

If you want to count no. of columns in the Excel file then please follow the below instructions.

1. Use tfileinputExcel component.

2. Add some basic instruction in the component with limit 1 but important is "Put one name in Schema 'excel_header' (You can use any name) and change its type to 'Dynamic' ". It will fetch the full row from the excel file.

3. Now Use second component 'tjavaRow'. Link your excel component through the 'main' row to tjavarow component.

4. Syn your schema in tjavarow component and put below mentioned code.

System.out.println("Counter ::: "+input_row.excel_heade.getColumnCount());

5. Now your job will count columns for you.

Note: You can store this counter value in any context variable and can use it.

Thank you

Sanjay