How to import data from multiple CSV files but only for some specific subject IDs

[nightale]

Hi,

I have to run analyses on a clinical database that is still a work in progress. That means that right now every aspect of a subject assessment is in an individual CSV file. That also means that data queries are a mess to deal with because data are not connected. For instance, there is a csv file for "general medical history", another one for "neurological exam", one for "medication", etc. Each and every subject has a unique PAT_ID identifier (e.g. 3008, 3009) and that identifier is in a column in every file. That PAT_ID is the only way to connect data from one csv to another.

I was provided with a main xlsx file that has 700+ subjects file with data merged from other csv files (for instance, age, education, etc.). Basically, what I need to do is to import data from other CSV files into that “main” xlsx file. Sounds simple enough, but…

Here’s my problem: I need to import data only for the subjects ID (PAT_ID) that are in the main xlsx file. Individual csv files have more patients that the main file, because these patients discontinued the study or met an exclusion criteria and the assessment stopped right there at screening. So my main file only has file for those who passed screening and are still in the study. How do I query separate csv files for only specific columns of data and only for the PAT_ID that are included in the main file?

e.g.
Main file = xlsx file
PAT_ID; Age; Educ; …
3001;35;12
3003;45;8
3008;47;18
…

CSV file = Diagnosis csv file
PAT_ID; PrimDx; DATEDx
3001;1;2015-02-18
3002;1;2014-05-12
3003;2;2015-01-05
3004 ….
….

So in that example, I’d like to import data from the csv diagnosis file to the main xlsx file, but only for those patients that are recorded in the main file. In that example, my query will have to include PAT_ID #3001 and #3003 , but ignore 3002 and 3004 because they are not in the main “master” file. Also, let’s say I only want to import the PrimDX variable from that file. How do I do that?

Would you be so kind to give me any pointers? Let me know if I wasn’t clear.

Best regards.

[nightale]

I've found a workaround as there are only 15-16 separate csv files.

I've imported manually each of them to a new sheet within my master workbook. Then, from the master sheet (#1), I've used the INDEX:MATCH functions to retrieve only the data I need whilst using the PAT_ID as a lookup value.

Any way I could have done that better? The only problem I see in the long term is that when we'll add new records, that'll be a lot of job to do...