Dynamic Internal table

[Nkrish]

I have seen all the answers pertaining to this topic, but I still have some doubts.

I have an excel file which has some columns which will be fixed but some columns which would vary. I need to create these varied fields based on the data that I get only after I upload this file with fixed values.But I am unable to understand how will I get the values of these columns without uploading.

Thanks

Narayani

[Former Member]

Hi,

Could you please elaborate the structure of the files that you will get? Please correct me if I am wrong, you are facing a situation where a column may or may not exist in the Excel file and you want to develop a single program to handle that file.

There can be various apporaches to accomplish this:

1. Outside SAP: develop a template file that contains all possible columns. The columns exist but may or may not contain a value. Hence the strucutre of your internal table remains fixed and you can process fields that contain a value

2. Inside SAP: If possible, the first row of the file contains column names. Based on this header, you can create an internal table as mentioned in previuos postings. The idea is to use metadata to create the internal table and then use it to manipulate data. The metadata might exist as the first row or as another file containing column names.

If the file contains data only, then one of the ways could be to save the file as CSV and then upload that initially in a table of type LCHAR(500) lets say. Then you loop at that table and SPLIT the string at ',' into another table line-by-line. You can then process the data as you need.

Thoughts???

Regards

[Nkrish]

Hi,

Thanks for the reply. The structure of my file is something like this:

I have material,plant,from date,to date.

Now based on the from to date , I can calculate the number of columns .

But this from to date would be available to me only after I upload the file.

Secondly the data for quantities would be filled for some dates.

I could use the csv option but how do I know from where the quantity columns start.

Thanks

Regards,

Narayani

[Former Member]

Hi,

As you mentioned you can figure out the number of columns based on the from to date. I think a subtle use of field symbols might help. Could you please post some sample data? (use the code /code tag to keep formatting)

Regards

Message was edited by: Shehryar Khan

[Nkrish]

Material    Plant From date  To	      Qty1  Qty2
070000150   1100  1032005   28052006  500   600
7966565	    1100  1032005   28052006        200 

If you look at the above sample data. I would get all these fields through my excel data and I can create an internal table with these fields. Now the problem comes in the quantity fields , if you notice the qty1 and qty2 have some values now the quantities would not pertain to the From to dates.Here also I can manage if I know how many columns have been filled - find the max and work accordingly. The only thing that bothers me is how many columns for quantities shld I take , I can take upto 52 columns but that is not the efficient way.

" I tried the CSV format that works fine" .

Thanks

Regards,

Narayani

Message was edited by: Narayani

[athavanraja]

use FM <b>KCD_EXCEL_OLE_TO_INT_CONVERT</b>

this would return the result in a structure like below.

ROW           COL            VALUE
1              1              material
1              2              plant
1              3              from date
1              4              to
1              5              Qty1
1              6              Qty2
2              1              070000150
2              2              1100

From this structure you can find the no. of columns do build the dynamic internal table and then you can move this values to the new itab.

Hope this helps.

Regards

Raja

[Nkrish]

Hi,

Thanks for all the tips i have figured out a way to get the quantities but now the problem is how to dynamically find from where the quantity values start as the header of the quantities may be qty1 or d1 or 02.2005 or 09.2004 or in some cases may also be missed. I am giving the elaborate structure down below.

Material    Plant From date  To	      Qty1  Qty4 
070000150   1100  1032005   28052006  500   600    200
7966565	    1100  1032005   28052006        200 

1) Now if you see above the last column doesnot have any heading how would I figure out the heading.

2) How do I figure out from the internal table format that there is a column missing in between qty1 and qty4.

I can manipulate it by writing some code by hard coding but I wanted a way by which i can make my program robust.

Thanks

Narayani