Database Folk - Sanity Check

I wonder if anyone familiar with databases could give me a sanity check?

I have a excel document with around 100 columns the data is from a survey tool, I need to get multiple answer columns into one, I also need to make the adjacent column equal the question posed which is the column label in the source xsls.

I have a manual way to do this but is a pain as there are 20 + columns and there not contiguous.


As we are a mac household I have been considering using mysql but before committing the time to learn this I wanted to check if its posable?


The output I'm after would look like:
Column1 some demographic data,
Column2 some demographic data,
Column3 Answer,
Column4 Question (This is the column name from source of the previous column),
Column5 Question # (I'm assuming a second table and look up Column4 and return its question #),


Ideally I could have a single query that would do this for multiple answer columns, e.g. columns 4,6,9,12,23,24 all are free form text responses, hence the adjacent report column is the question so it can be evaluated by a human.

I'd then export this back to excel for someone else to analyse and mark as relevant or not.


db's aren't my thing (obviously) so apologies if I've asked a clunky question or used the wrong terminology.

TIA for any and all replies.

Its difficult for me to picture. Is it 20 columns and 100 rows, or actually 100 columns with data on.a single line? If your data table is 3rd normal form, you should be able to do something with a pivot table. If your data isn’t, then the data model is probably questionable.
If you can send over a sample, I could take a look. I expect that there are others in here more knowledgeable who could help you.

so I hit my first issue which is our Mac’s are not on the right version of macOS to allow MySQL and current we are tied to current os due to other software compatibility.

When I get the full data set it would be aprox 90 columns that are question fields, the questions are a mix of quantitative and qualitative responses.
In this case I’m expecting there will be ~50 respondents so ~50 rows.

I need to split the quantitative and qualitative answers and handle them differently.

For the qualitative data I need the first two columns as these are demographic data, then the first qualitative column, then the header cell of the first qualitative column as this gives the question, finally the question number.
This gives the first 50 rows of the qualitative report, then the next 50 rows would be the same thing but the second qualitative column And associated question and question number.

In the end the report would be 5 columns by ~1000 rows

can you do a smaller table with similar examples of the manipulation you want? I don't quite understand what you need.

Should all be possible with just excel using HLOOKUP and VLOOKUP (it can span different sheets in different files too)

or consider microsoft access

Quote:

---

Originally Posted by Xantiagib

can you do a smaller table with similar examples of the manipulation you want? I don't quite understand what you need.

Should all be possible with just excel using HLOOKUP and VLOOKUP (it can span different sheets in different files too)

or consider microsoft access

---

I was just about to suggest that too. It's a much more qualified database manager than EXCEL. VBA and SQL can be better used in ACCESS as can Queries across multiple linked tables.

Quote:

---

Originally Posted by Griswold

I was just about to suggest that too. It's a much more qualified database manager than EXCEL. VBA and SQL can be better used in ACCESS as can Queries across multiple linked tables.

---

AFAIK Access isn’t available on Macs (unless running Windows but that’s further complication).

Hard to see exactly what is required but can’t OP start another sheet and have functions to drag in the columns you want in the correct order etc.

Quote:

---

Originally Posted by Kingstepper

AFAIK Access isn’t available on Macs (unless running Windows but that’s further complication).

Hard to see exactly what is required but can’t OP start another sheet and have functions to drag in the columns you want in the correct order etc.

---

I wasn't aware of that, not being a Mac user.

It is possible to use VBA behind the scenes within EXCEL but it is not a straightforward as it is with linked tables and Queries in ACCESS. Pivot Tables, as suggested by someone earlier may be a possible solution; but in the end I don't think EXCEL is the best solution for what the OP is trying to achieve. Is there an equivalent DB program to ACCESS in the Mac world?

Quote:

---

Originally Posted by Griswold

I wasn't aware of that, not being a Mac user.

It is possible to use VBA behind the scenes within EXCEL but it is not a straightforward as it is with linked tables and Queries in ACCESS. Pivot Tables, as suggested by someone earlier may be a possible solution; but in the end I don't think EXCEL is the best solution for what the OP is trying to achieve. Is there an equivalent DB program to ACCESS in the Mac world?

---

Not Mac user either but looked into this when thinking of switching. Think there may be one with OpenOffice.

Thanks for all the replies I haven’t worked through them all yet but they are appreciated and I’ll dig into them over the weekend.

After a aborted attempt to use awk (I’d forgotten how much a pain moveing files from csv and Linux/macOS cod line and EOL markers can be...) I’ve fallen back to writing a macro in vba and its a touch klunky but does the job.

I still suspect that running this via a db would be better long term but my short term need is covered.

I can’t quite fathom what you are wanting - I have many years experience administering databases of various types - both structured and unstructured data. If you did want a simple relational database like MYSQL you can download the community edition (for free) from the website - you can run it on macOS or Linux or windows.

https://dev.mysql.com/downloads/mysql/

if that is not compatible with your macOS (saw your comment above ).

One we used with a lot of success is this one:

https://www.postgresql.org/download/macosx/

Excellent product and will run on 10.7 and above (from their literature).

designing your database is key to success.

i don’t quite follow you saying Col 4 heading is a data value - please explain more?

marty.

ill add to the request of an example of what you require using just 3 or 4 records, its difficult to visualize with the current descriptions, and i cant be sure what i am visualizing is what you are trying to articulate.

I'm not a db programmer but I have a few db's (mysql) running on my Linux servers for various tasks.

When I had to put together my own 'personal' db a year or so ago to handle roughly 2,000 rows by 200 columns I just used sqlite3 which is found as standard I believe on OS X. As far as I know it is SQL compatible with mySQL/mariaDB but you won't need to install it - it's probably plenty quick enough for your task.

To respond to those who are interested in visualising my data set here are some images and I’ve also made the files available on google drive if your really interested.


Sample Source File
The source file (Sample-survey-export.xlsx) is truncated in both row and columns but should give you a jist of the source data. The actual data set runs around 90 columns by 55 rows.

Column Description:
A = ID
B = Demographic Question
C = Demographic Question
D = Quantitative Question
E-H = Qualitative Questions
I = Quantitative Question
J-M = Qualitative Questions

Handling and extracting the Quantitative answers is easy, the qualitative answers are the harder set.

Sample-survey-export.xlsx
https://i.postimg.cc/SjjTMbZK/Sample-survey-export.png


Heres the qualitative answer report I generate via an macro in excel.

Column Description:
A = Count Column so I can return to original sorting if needed
B = Demographic Answer
C = Demographic Answer
D = All the qualitative answers in a single column
E = The question posed to the answer in column D
F = Question Number
G = Include
H = Possibly Include

This report allows a human to review the responses to each question and mark those that should be included in a report and those that might be of interest but need more though.

https://i.postimg.cc/PNc6bVcQ/Sample...tative-RPT.png

Heres the google drive link for those really interested.
https://drive.google.com/open?id=1JP...GVOieHuNJ0Jakk


Hopefully that will allow you to visualise the challenge if not let me know and I’ll try and clarify

You should be able to find a version of MySql for your version of MacOs here: https://downloads.mysql.com/archives/ if that is a path you still want to pursue.

Just installed a version compatible with High Sierra on my Mac.

Can't you just pivot it but leave the sum/count off? It may be a bit repetitive dragging the feilds but shouldn't take too long.

Otherwise I think it may be possible anyway using to index & indirect functions.

I don't mind having a look if you like but I am away at the moment so may not be able do anything until the middle of next week.
95% of my working life in the last 15 years has been solid excel use but I am still learning new tricks.

Also, you mentioned a hundred columns, but how many rows?

Quote:

---

Originally Posted by jmitch

Can't you just pivot it but leave the sum/count off? It may be a bit repetitive dragging the feilds but shouldn't take too long.

Otherwise I think it may be possible anyway using to index & indirect functions.

I don't mind having a look if you like but I am away at the moment so may not be able do anything until the middle of next week.
95% of my working life in the last 15 years has been solid excel use but I am still learning new tricks.

---

around 90 columns by 55 rows.

No need to as I said I’ve written a macro to do it now, I’m thinking of a dB for future runs, but we might also move the survey to a different platform so I may well wait until that’s settled. Thanks for the offer though.