Extract variable location data from multiple sheets

[Aaron_Tram]

I couldn't find an answer to this question but if it is out there, please point me in the right direction.

I have data from scientific experiments that is generated and the output is stored in .csv files. Each file represents a different experiment. Most measurements are obtained in every experiment, but some vary and, thus, the location within the .csv file of each data point is variable.

The key parts of this data is extracted by hand and I know there must be an easier way!

Unfortunately, the data is stored in a difficult format (this is the way the instrument/software does it). I attached an example. Note that the data fields are stored not in columns but rows and the numerical data output is usually a couple cells to the right of the field title.

I am unsure how to start and would appreciate some guidance. For example, if I have multiple of these .csv files and wanted to extract the numerical data for "Aorta1, Aorta2 and Aorta3" lengths, how would I go about that?

I think this may be challenging to set up but once done will save a TON of time.

Thanks in advance for your help!

[JBeaucaire]

It's probably just as easy (quicker) to read the data in from the actual text/csv files instead of the imported version. Assuming you have a folder full of text files you need to get those three piece of info from?

Can you post a couple of the CSV files and then a workbook showing the format of the extracted data you desire from those two CSV files?

[Aaron_Tram]

Okay, I've attached 2 of the .csv files. This forum won't allow me to attach a .csv file, so I saved them in .xls format (WL260_4.xls, WL266_40.xls). I also attached the master file (Echo_Master.xlsx). I also highlighted a couple of points of data (yellow and green) in each file to show you the from-to flow.

The master file was not made by me and is a compilation of many separate .csv files (each contributes to one row). The data from the each .csv is copied to the master file (by hand). Once in the master file, the data is manipulated by formulas and we use the output.

Thanks!

[Aaron_Tram]

See my post below.

[JBeaucaire]

You can always create a ZIP file of all your files and upload them together, that will get around the CSV error issue.

1) All the CSV files in a specific folder need to be imported to the master, yes?
2) The CSV filename should be entered in column A, then the data added to the columns where you've noted
3) Will the master have these filenames already so we need to update existing rows, or will the CSV always be new rows added to the existing data?
4) We can assume that row 2 will always have the formulas we need so we can just copy those down, yes?

[Aaron_Tram]

Thanks for the tip about .zip files. To answer your questions...

1) Yes, all the raw data files (.csv's) are organized by experiment and are easy to lump into one folder. This can be the folder with the master file or a separate one. Usually different experiments are grouped on different sheets within the same master .xlsx file, but I only included one for the example.
2 & 3) The master file won't necessarily have the filenames already, but we could manually enter that. There should never be a duplicate filename for the .csv files containing raw data. Each new .csv file of raw data will contribute to a single new row of data in the master file.
4) I assume that you are referencing the formulas that manipulate the data (diameter -> area, etc). Yes, those are in row 2 of the master and are filled down the spreadsheet as rows are added.

[JBeaucaire]

OK, make sure any new sheets start with a key row2 that has all the formulas, and A2 shouldn't be empty either.

Please Login or Register  to view this content.

[Aaron_Tram]

Ok. Pardon my ignorance with this. The above code, do I use this in Excel VBA to INSERT -> MODULE and paste it there? Also, how do I activate it?
Thanks in advance.

[JBeaucaire]

That is correct, insert it into an empty code module.

Bring up the correct sheet onscreen to collect the data into, then you can bring up the list of installed macros by pressing Alt-F8.

[Aaron_Tram]

Okay, this works for most files, but on others it fails. I am attaching an example. I hope you can help solve this.

I may not have made it clear enough, but the master .xlsx file is always consistent in where data is stored, however the source files (.csv's) are variable. For example I've attached 3 .csv files and the master .xlsx file. The macro gets 2 of the imports correct but misses on the 3rd.

As an example, I will use Aorta Length which is columns B, C, D in the master spreadsheet (Echo_Master.xlsx) but this is also a problem for all other import fields.

I started with the Echo_Master.xlsx file having only rows 1-3. I then ran the macro and the 3 .csv files are imported appropriately. However, data from 270_4.csv is imported incorrectly. This is because data in that source file is shifted relative to the prior format which was used in construction of the macro. For example, Aorta1 is imported from cell C59 in the two files (269_44.csv and 271_6.csv), however the same measurement is stored in cell C58 in 270_4.csv and therefore the macro imports the wrong cell into the master file.

While the location can change between .csv files, the thing that remains constant is that the correct numerical data is stored in column C next to "Aorta1" and "Length" in columns A and B. Is there a way to make the macro flexible enough to recognize this? I am not that experienced with INDEX-MATCH and VLOOKUP, but it seems that VLOOKUP probably wouldn't work for this.

Thanks again!

[Aaron_Tram]

Attached is the .zip file.

[JBeaucaire]

It's a lot more code (just repetitive code) to search column A for specific values and copy in the values from specific cells to the right.

Please Login or Register  to view this content.

[Aaron_Tram]

Thanks you so much for your help. This is extremely valuable and will save a lot of time.

I noticed one thing that bothered me and I believe I fixed it on my own. If the macro did not find an element, for example, if only "Aorta 1" and "Aorta 2" were collected, but not "Aorta 3", then the value that ended up in the master sheet was simply a copy from the row above. I modified each segment to input "Not Found".

Also, I believe I correctly added a sequence that allows you to choose the .csv file source folder.

Let me know if anything I did looks stupid, but it seems to work. This has definitely been a good learning experience. I've never explicitly used VBA before.

Please Login or Register  to view this content.

[JBeaucaire]

Certainly the best "test" is to put a set of CSV files into a folder that accurately represents all the "variations" you encounter and run it. If it works, there you go!

If that takes care of your original query, please select Thread Tools from the menu above and mark the thread as solved. Thanks.

[Aaron_Tram]

May I ask why you used the Col Cnt commands for "Heart Rate" 1-3 rather than keeping it like the above sections?

That part seems to fail and I may change it to be similar to the other segments.

[JBeaucaire]

The two sample CSV files showed something interesting. One had these tests:

Heart Rate 1
Heart Rate 2
Heart Rate 3

No problem, we'll slip those into the Heart Rate columns, yes? Nope. The second file had this:

Heart Rate 1
Heart Rate 3
Heart Rate 4

Oh my... so if there could be any/skipped heart rate tests, I tried to loop through possible tests Heart Rate 1-10 and put the first 3 found in the 3 heart rate columns.