count columns in excel

One Star

count columns in excel

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

Re: count columns in excel

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

Re: count columns in excel

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?
Employee

Re: count columns in excel

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

Re: count columns in excel

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

Re: count columns in excel

Oh... yes...
One Star

Re: count columns in excel

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

Re: count columns in excel

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
One Star

Re: count columns in excel

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