• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

Export specific data

Lenche08

Member
Hello experts
I need some help to make something that will make my daily work easier.

First off all I do not know if that is possible, but if that will became possible then I will be very apreciate to all of you.

What is the problem?
I have some csv file which contains some data. From that csv file I want to extract some data that are important for my results.

What is in the csv file?
Here we can find some data from 3 intvervals.
Intervals are marked in that syntax:
Interval and data points: 1 10
Interval and data points: 2 100
Interval and data points: 3 100

I will need to extract data from Interval and data points 2 and from Interval and data points 3.

What data I need from Interval and data points 2 and from Interval and data points 3?
I will need results from 2 specific columns. Column Shear Rate[ 1/s] and Column Viscosity[mPa·s]

Every point of shear rate[ 1/s] gives the results for Viscosity[mPa·s].

I will need results from column Shear Rate[ 1/s] at specific points of shear rates. In my case I will need 3 points of Shear Rate[ 1/s] for my both intervals. My objective is to get the results for viscosity at specific shear rates.

From interval 2:
Interval and data points 2
Point:20 Shear Rate[ 1/s]= 100 Viscosity:?
Point:40 Shear Rate[ 1/s]= 200 Viscosity:?
Point:60 Shear Rate[ 1/s]= 300 Viscosity:?

From interval 3:
Interval and data points 3
Point:41 Shear Rate[ 1/s]= 300 Viscosity:?
Point:61 Shear Rate[ 1/s]= 200 Viscosity:?
Point:81 Shear Rate[ 1/s]= 100 Viscosity:?

How I am doing that now?

Now I am doing that manually.Opening one file by one ...looking for correct interval and coyping the correct data from specific shear rates and then copying that to another excel table. Closing the prevous file and looking for another file..then again copying the results closing and again..

What I want to manage?
I want to manage: with your help to create some macro- some excel macro-macro that will allow me to so select multiple csv files and then automaticly extract the data points I want in another excel worksheet.
 

Attachments

  • Ball 001 mer1.csv
    29.4 KB · Views: 2
  • Ball 001 mer2.csv
    29.4 KB · Views: 2
  • Ball 001 mer3.csv
    29.5 KB · Views: 2
  • WhatIWantToManage.xlsx
    16 KB · Views: 2
Hello
Yes, this 6 points are always the same.
Well if is not too much I would like to have both sheets? I was thinking to have two sheets becouse in that way i can make fast check to see if the
data in the FinalResutls sheets are correct?

So for my work it would be nice to have both sheets.

But if is not possible then it would be ok also with only one sheet: FInalResults.
thank you
 
In the attached, table at cell A14 of FinalResults sheet.
I think that values in cells D9 and F9 should be swapped?
I put your 3 csv files into a folder on my system called C:\Users\Public\Documents\Chandoo57767 by themselves and that folder path is in cell A13, which is a single-cell named range Path.
Adjust this cell to contain the folder name where your csv files are kept (at first keep the csv files by themselves in that folder, no discrimination of the files in that folder is taking place at the moment).
Then you can right-click on the table at cell A14 and choose Refresh.

This is very much at the work-in-progress stage and needs to be made more robust (and there's a bit of extraneous developmental stuff that needs removing too).
First, can you confirm at least that the data is correct (please test on the 3 csv files you gave us before trying on other csv files)?
I've used the 100, 200, 300 in the Shear Rate column rather than the values 20, 40 etc. in the Point No. column to select rows; is that OK?
The Test Start Date and Time column is also different from your expected results but I expect mine is correct?
 

Attachments

  • Chandoo57767WhatIWantToManage.xlsx
    33.2 KB · Views: 1
Last edited:
Hello

I think that values in cells D9 and F9 should be swapped?
first I have to say that in my excel I made mistake when writing the shear rates for 3 interval.
it must be in thay way. You are correct p45cal, Thank you for noticing that.

ok now I will test the rest.


1727549037681.png
 
First, can you confirm at least that the data is correct (please test on the 3 csv files you gave us before trying on other csv files)?

I try to test excel file you send me and it says me incorect path file? I change the path with my path file and I click refresh and I didnt get any data?

hmmm
what I make wrong? How I have to test it?
 
I try to test excel file you send me and it says me incorect path file? I change the path with my path file and I click refresh and I didnt get any data?

hmmm
what I make wrong? How I have to test it?
Be absolutely sure that the path is correct, with no leading/trailing spaces and no final slash character. Copy/Paste that folder path from somewhere if you can.
If you still can't make it work, could you quote the actual path here?
 
yes
hmmmmmmmmmmm

your path: C:\Users\Public\Documents\Chandoo57767

my path: C:\Users\tenis\OneDrive\Dokumenti\ElenaGlavniDokumenti\UrejanjePodatkov\002
 
That error dialogue shouldn't be trying to look at …\Chandoo57767… it should be looking at what's in cell A13 via cell A13 being a named range in Name Manager called Path. Have you changed something there?
Attach you failing file here so I can check for changes?
 
Yes
I am attaching how I write my path.

One question about testing:
First I delete the old data in table no?
then
First I have to change the path name?
then i refresh?
and then i must see new data in table?
1727550986613.png
 

Attachments

  • Chandoo57767WhatIWantToManage.xlsx
    33.3 KB · Views: 3
One question about testing:
First I delete the old data in table no?
then
First I have to change the path name?
then i refresh?
and then i must see new data in table?
1. No need to. You can, but do not delete the entire table.
2. Yes
3. Yes.
 
Ohhh
I think know what is wrong.
I put your excel file in the same folder with my csv files.
Now I put the excel file in another folderr and I get dataaaaaaaa.
oohhhh it seems is working....

waitt...i will test now more files to see if is that working..
meanwhile...how you do that all that? where you hind the codes to make all that? where is the macro? vba?
hmmmm
thank youu for now
i will be testing more data now
 
hmmm interesting
for now it seems is working...
but what if i need now another shear rates? let say 250, 100, 50? how I will manage to make it work like you make it?
what i have to do?
how i can change that?

Right-click the results table, choose Table, then Edit Query…

I try your sugestion...i get this?

1727552343972.png
 
but what if i need now another shear rates? let say 250, 100, 50? how I will manage to make it work like you make it?
That depends on how often you might want to change things. If it's only very occasionally then we can change those things manually, otherwise it'll be a case of writing it in to the code - perhaps a bit like the path.
 
That's odd. Are you using the web version of Excel, or perhaps you're on a Mac? I don't know.
What version of Excel are you using?
I am using Windows. MS Excel 2016.

That depends on how often you might want to change things. If it's only very occasionally then we can change thos things manually, otherwise it'll be a case of writing it in to the code - perhaps a bit like the path.


Can you show me where I can change that manually if I will need it? Sometimes yes I need different 3 points...but then when is confirmed then these 3 points will be correct then that is finally.

otherwise it'll be a case of writing it in to the code - perhaps a bit like the path.
And how you make that? it takes you a lot of code writing?

and one question more? how much time you spend with that excel to make it working for me?
 
When you will have some time can you show me what I have to do when I want to add some another shear rate or when i want to remove shear rate?
I go to edit query and then i have......

Thank you so much for your time and your solution.:)
Is perfect. :awesome:
 
I think I find what you mean when you say we can change manualy...
I will play with that to see what happen

I am tesing now if I Only have 100 and 200 shear rate
= Table.TransformColumnTypes(#"Expanded Table Column1",{{"Test Start Date and Time:", type datetime}, {"100", type number}, {"200", type number},{"Test:", type text}, {"Interval", type text}})
 
but what if i need now another shear rates? let say 250, 100, 50?
In the attached, it's as before except:
  • I've shifted the named range Path to cell H1
  • I've added a table (a proper named Excel Table (called ShearRates)) at cell H3
  • I've moved the results table to cell J3 (because varying column numbers in the results played havoc with your data in columns A:F)

You:
  • Adjust the file path
  • Adjust the numbers in the new ShearRates table (do not change the header of this table); you can adjust both the numbers themselves and how many there are
  • Refresh the results table.

All still very 'work-in-progress'.

ps.
Obviously we have one results table which changes when you change the csv files and/or the shear rates returned. To preserve the values you need to copy the table and paste it somewhere else, BUT… don't do a plain copy and paste (you'll duplicate the query as well (it doesn't matter but it will make things very untidy)), do a copy|Paste-Special-Values instead.
 

Attachments

  • Chandoo57767WhatIWantToManage_02.xlsx
    34.4 KB · Views: 1
Last edited:
Back
Top