how to represent a complex object using Excel

mmingfeilam used Ask the Experts™
i want to use Excel to load data so i can do bulk insert.  if i have the following class:

class Person
List<Address> addresses;

what is the best way to represent this data in an Excel file?  currently, we are saving it as a flat structure, i.e. address1, address2, etc.  but this is not flexible.  can someone offer some ideas on how to represent the data in a more flexible way?  thanks.
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Where are you loading the data?  Is someone manually typing it into excel?

Assuming you are restricted to using Excel...I would make it so there are two columns (PersonName, Address) and that way each "Person" will have one row for each address.  (multiple rows for multiple addresses obviously)

However, it is not clear to me if you are trying to get the data INTO excel, or OUT OF excel?  There are a lot better ways to store the data if you're looking for storage of data that you will be reading and writing to (a database such as SQL for instance). However, a simple spreadsheet as described above would be a suitable place to export some data to I suppose.


both, we use the Excel file so our user can input it, then we will map data from Excel file to a list of objects before saving to DB.  if you are using one row for each address, then the first row will be full, but the subsequent rows will be empty except for the address column.  how are you going to be able to tell when the first set of data end and the second person's data begins?  by person ID or name?  if the user wants to add an additional address to the Person object, he will have to insert an empty row between the first set of data and the beginning of the second set of data?

something like this:

Name1 | ID1 | Address1A
             |       | Address1B
              |       | Address1C
Name2 | ID2| Address2A
Well, a couple of options would be to have the user re-enter the name and id on any new line with the new address, which would make it easy to find and insert into the DB. could have a user column, and then one additional column for each address.  It would not be too hard to code the routine to pull the data out of a row like that (each row would have different number of columns potentially).  So in that case, the data would look like....

Name1 | ID1 | address1a | address1b
Name2 | ID2 | address2a
Name3 | ID3 | address3a | address3b | address3c

Lastly, I suppose each "person" could have a separate worksheet with addresses on each line.  That would be very cumbersome with a lot of people though.

Bottom line is that Excel is not very good at representing "complex" types I guess.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial