topic Re: From Excel to Database Table in Application Development and Automation Discussions

From Excel to Database Table

Sujeet_Acharya — Wed, 17 Nov 2004 05:34:07 GMT

Hi,

I want to upload the data in an Excel to a Database Table (Fields of the Table are same as headers of the Excel Sheet)

Is there any method to do so ?

Thanks in advance.

Sujeet

Re: From Excel to Database Table

Former Member — Wed, 17 Nov 2004 06:37:13 GMT

You can use function module 'ALSM_EXCEL_TO_INTERNAL_TABLE' to upload data in an Excel file to an internal table. Give the Import parameters as follows to upload the entire worksheet.

FILENAME - the file name (eg. c:\test.xls)

I_BEGIN_COL - 1

I_BEGIN_ROW - 1

I_END_COL - 256

I_END_ROW - 65536

Once the data is in the internal table, you can use either Insert, Update or Modify statements, according to your requirement, to populate the database table.

Hope this helps.

bye

Madhavan

Re: From Excel to Database Table

Former Member — Wed, 17 Nov 2004 07:13:23 GMT

Hi,

thanks for a hint. My related questions are:

1. What if I can't be sure about the value "i_end_row"? I.e. I have a structure, but such an upload must be executed often and with variable (but not fixed) amount of rows. Any hint(s) about this? Or perhaps there is an another way?

And the 2nd question: is there any similar FM for uploading a csv file into an internal table?

Regards,

Ivaylo

Re: From Excel to Database Table

VXLozano — Wed, 17 Nov 2004 09:50:31 GMT

About your i_end_row number... I think Excel has a max row number of 65536... use it, and your function will only upload to table the filled rows... In any case, if it uploads empty rows, you can use a DELETE sentence to clear it

About CSV files... GUI_UPLOAD them into a internal table with a single field and use string functions to split it into your table...

Another way (faster, but not automatic) is to open your csv file with your excel and save it as a tab delimited one, then use the pointed excel to table function...

Wish it works,

Vic

Re: From Excel to Database Table

Former Member — Wed, 17 Nov 2004 11:30:50 GMT

Hi,

As Vic rightly said, 65536 is the maximum no. of rows that can be uploaded from an Excel worksheet. You can delete off the blank lines from the internal table if at all they are uploaded.

Use function module 'KCD_CSV_FILE_TO_INTERN_CONVERT' for directly uploading csv files to internal tables.

Hope these answers your queries.

bye,

Madhavan

Re: From Excel to Database Table

VXLozano — Wed, 17 Nov 2004 11:35:19 GMT

Wow! Will try to remember that function's name

Re: From Excel to Database Table

ChristianFi — Wed, 17 Nov 2004 11:53:53 GMT

I would like to add a word of caution here.

Keep in mind that you have to care of the appropriate conversion-exit. For instance, depending on the format, excel data could like as if the field has leading zeros but actually did not. If you post that field to a char field with the conversion exit alpha - you will hardly be able to select it again.

Christian

Re: From Excel to Database Table

Former Member — Wed, 17 Nov 2004 14:07:47 GMT

Guys,

although I'm not the former of the thread, I wish to thank all of you for your valuable hints.

I'm curious to myself (is this a correct English sentence? never mind...) why din't I found this forum earlier?!? It would saved me a lot of troubles in my previous projects.

Keep posting, guys

Regards,

Ivaylo