Application Development Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Showing results for 
Search instead for 
Did you mean: 

From Excel to Database Table

Sujeet_Acharya
Advisor
Advisor
0 Kudos
172

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

7 REPLIES 7

Former Member
0 Kudos
104

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

0 Kudos
104

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

0 Kudos
104

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

Former Member
0 Kudos
104

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

0 Kudos
104

Wow! Will try to remember that function's name

0 Kudos
104

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

Former Member
0 Kudos
104

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