On Jun 13, 2022, at 4:28 PM, charles meyer <[log in to unmask]> wrote:
> 
> Hi my esteemed listmates,
> 
> I’m launching a new library contest.
> 
> I’d like to be able to sort participants by their given and surnames and the names of their submitted project names alphabetically and for all the data included in the respective columns.
> 
> I was contemplating this…
…
> Say I wish to sort alphabetically by last or first name or by project name or by if they’re a 1st time participant, etc.
> 
> I’ve learned with Excel it’s best to plan your columns for data before you start inputting their data.

That’s true of any data modeling effort.  Basically, you want to ask yourself “what sort of questions do I want to be able to ask about the data”, “what data do I need to be able to store?”, and “are there any weird cases that I need to deal with?”.

Once you come up with your model, go through and check to make sure it meets the requirements.  But you’re always going to have edge cases that you didn’t plan for if the system lasts long enough.

For instance, I was just going through our Library Friends mailing list last week, maintained by our secretary in a spreadsheet, and because the records are per family, we had the case where we had email addresses for more than one family member, which meant I had to handle that case individually.  In your case, if someone can submit more than one project, would you enter that as multiple records, or as 


> Would creating a Word Table in 365 be a better tool than Excel for this?

Excel.  If there’s a spreadsheet in 365, use that, because you might want to share it with other staff members.

You could also use Google Sheets if 365 doesn’t have one.


> I’m also trying to ensure that if I have the benefit of an online program (participants register on) I can export that data as a .csv file and that it will import all these fields into the correct respect columns?

Google Forms lets you feed data into a spreadsheet, but there may be some privacy issues surrounding using it if you don’t have a library account for it.  I don’t know if 365 has anything similar.



> Ex. How do I ensure when I export the online file as a .csv that when it imports it doesn’t dump all the data into Column A but rather imports the last name into Column A, first name into Column B, etc.?

CSV is more than just commas.  The full format is double quoted strings, (with backslash escapes), commas between fields, and line returns between records.  In something like Excel, there’s the “import wizard” that lets you define how to map the fields in the CSV file to columns in your spreadsheet.

So long as you defined them as two separate fields, it shouldn’t be an issue to bring them into two separate columns.

> Have you had to take extra measures to ensure all the data from an online program exports correctly into the Columns you’ set up in Excel or tables in Word?

I’ve never had problems with Excel so long as the person didn’t use ‘merge cells’.  I’ve never tried to export data out of MS Word programmatically.  (I copy & paste the table data into Excel, then work on it)

I don’t know if this would be useful for not, but I have a presentation on some work that I was doing with ingesting tabular data many years ago.  If you turn on presenter notes, and skip to the end, I had a few slides of things not to do:

https://sdac.virtualsolar.org/catalogs/jhourcle_SPD_2008.ppt

-Joe