count columns in excel

[Anonymous]

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

[Anonymous]

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.

[Anonymous]

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?

[Anonymous]

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

[Anonymous]

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

[Anonymous]

Oh... yes...

[Anonymous]

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

[Anonymous]

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...
http://www.dreamincode.net/forums/topic/182398-using-excel-java-excel-api/
You can load the library needed using the tLibraryLoad component

[Anonymous]

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

[Anonymous]

Hi

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