+ Reply to Thread
Results 1 to 13 of 13

Quality Control Chart from CSV File

  1. #1
    Registered User
    Join Date
    02-06-2005
    MS-Off Ver
    Excell 2016
    Posts
    35

    Quality Control Chart from CSV File

    Hello,

    I have data from a sensor that records pH and temperature every five minutes for monitoring an experiment. I want to create to a quality control chart using excel but I need the ability to import the raw data in csv file format and create a pH/Temp vs time chart with upper and lower control limits. I have included a manual created version of what I am trying to do. I hope someone can help.

    Best,

    Frank

  2. #2
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Quality Control Chart from CSV File

    Not sure I really do understand your problem but I've written a macro for importing csv files to excel.

    Some warnings i.e. in the code I've hard coded a path to the csv file.

    Please Login or Register  to view this content.
    This you have to change to suit your needs. The macro then asks for the file name and you have to add the full file name i.e deviceReport-2017-01-14 16-58-45.csv

    The file is then imported to cell A1 and the part "C~" is stripped from the temp column transferring the values to number. As for the pH value I'm not sure you will get these as numbers. My problem is that the Scandinavian delimiter for number is the "," whereas you seems to use "." so when I import the file I get the dots as it is in the csv fil. Normally a multiplication by 1 is sufficient to convert a number text string to value. This can be added to the macro if you don't get the pH value as a number.

    There is also a possibility of letting the macro open the file browser where the csv files are stored so you should only have to click on a file to select it instead of writing the file name in the input box.

    Please Login or Register  to view this content.
    Alf

  3. #3
    Registered User
    Join Date
    02-06-2005
    MS-Off Ver
    Excell 2016
    Posts
    35

    Re: Quality Control Chart from CSV File

    ALf,

    This great. The date time format is also screwed up as the current format January 16 - 2017 13:15:58 should be 01-2017 13:15:58

    If I could get the macro to convert date values to the correct date format time and the pH and temp values to the the number format. I would then want to plot the data using a custom excel chart.

    Thanks for the help so far.

    Best,

    Frank

  4. #4
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Quality Control Chart from CSV File

    Hi Frank

    date time format is also screwed up as the current format January 16 - 2017 13:15:58 should be 01-2017 13:15:58
    The date formate is what is found in the csv file see jpeg showing raw data so it's not possible to change that

    raw_data.jpg

    so it must be changet after the macro has imported values from the csv file. Looking at your uploaded file I see that the the "Date" (B column) has the same formate as I get when importing values from the csv file. The C column (Date Time) is a custome setting I can't match with a Scandinavian setting. As far as I can see it looks like the formate is "M-D-ÅÅ t:mm;@"

    Since we use Månad and Dag in Scandinavia but timar and minutes I would assume the US / UK formate would be "M-D-YY h:mm;@". Not really sure of the ";" as the English speaking contries uses "," as the formula delimeter. Lookin at the C2 value (your uploaded file) it shows "2017-01-13 16:07:40" in the formula bar but "1-13-17 16:07" as the cell value for C2.

    So can you find out what the custom setting is for the "Date Time" column is as macro could be modified to insert a new column after the B column and set with the proper date time formate.

    How about the pH values are the extracted as numbers or do they come out as string value? If so can you try multiplying one of the values by 1 and see if this transfer the string to a proper number.

    Well I don't think I can do more at the moment before I get some feddback from you. But I would like to have a go at it.

    Alf

  5. #5
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Quality Control Chart from CSV File

    Hi Frank,

    I see Alf got in before me with a macro to bring across the CSV data, so I won't reinvent that wheel.

    I will, however, offer an alternate formula for col C, because the one you have is not accurate.

    =DATE(MID($B2,LEN($B2)-12,4),MONTH(1&LEFT($B2,3)),MID($B2,LEN($B2)-16,2))+TIMEVALUE(RIGHT($B2,8))

    I think this will work with all dates, but I can't really test it because of the small date range in the test data.

    I hope this helps, please let me know!

    Regards,

    David

    If this has been helpful
    - Please click on the *Add Reputation button at the bottom left.

    Please mark your thread as SOLVED:
    - Click Thread Tools above your first post, select "Mark your thread as Solved".


  6. #6
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Quality Control Chart from CSV File

    So I had another go at your problem. Could you test this modified macro and check if the format of date in cell C8 is the right one. In my Scandinavian environment it looks OK but does it work in yours? You will now have to add the number of the month as the month formula suggested by David adding 1 to month name does not work in my environment.

    If so the other formulas in columns E to T could be added.

    Please Login or Register  to view this content.
    Hi David

    Interesting input but your formula don't work in the Scandinavian environment. Don't really know why not as the input (month name) is the English one but testing I get a #VALUE! error.

    Alf

  7. #7
    Forum Moderator - RIP Richard Buttrey's Avatar
    Join Date
    01-14-2008
    Location
    Stockton Heath, Cheshire, UK
    MS-Off Ver
    Office 365, Excel for Windows 2010 & Excel for Mac
    Posts
    29,464

    Re: Quality Control Chart from CSV File

    @Frank

    Your post does not comply with Rule 8 of our Forum RULES. Do not crosspost your question on multiple forums without including links here to the other threads on other forums.

    Cross-posting is when you post the same question in other forums on the web. The last thing you want to do is waste people's time working on an issue you have already resolved elsewhere. We prefer that you not cross-post at all, but if you do (and it's unlikely to go unnoticed), you MUST provide a link (copy the url from the address bar in your browser) to the cross-post.

    Expect cross-posted questions without a link to be closed and a message will be posted by the moderator explaining why. We are here to help so help us to help you!

    Read this to understand why we ask you to do this, and then please edit your first post to include links to any and all cross-posts in any other forums (not just this site).

    To all
    Further responses should not be made until the crosspost links are included.
    Richard Buttrey

    RIP - d. 06/10/2022

    If any of the responses have helped then please consider rating them by clicking the small star icon below the post.

  8. #8
    Forum Expert
    Join Date
    11-26-2013
    Location
    Colac, Victoria, Australia
    MS-Off Ver
    Excel 2016
    Posts
    1,309

    Re: Quality Control Chart from CSV File

    Hi David

    Interesting input but your formula don't work in the Scandinavian environment. Don't really know why not as the input (month name) is the English one but testing I get a #VALUE! error.

    Alf
    Hi Alf - for your interest I have attached a copy of what I did - Column U has the formula; Col V has a Text copy of the result in case you still get the #VALUE

    DAC
    Attached Files Attached Files

  9. #9
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Quality Control Chart from CSV File

    Hi David

    Thanks for the upload, strange things seems to happen in Scandinavia. 1st image is a cut from your uploaded file

    formula1.jpg

    and the second one is taking a date from the OP's file and using your formula, as you can see time is not included in this case even if time has been included in your formula.

    formula2.jpg

    Alf

  10. #10
    Registered User
    Join Date
    02-06-2005
    MS-Off Ver
    Excell 2016
    Posts
    35

  11. #11
    Registered User
    Join Date
    02-06-2005
    MS-Off Ver
    Excell 2016
    Posts
    35

    Re: Quality Control Chart from CSV File

    Hi,

    I noticed that if manually removed "January" with and replace it with "01-". It converts the date to the appropriate format.

    Best

    Alf
    cross posts
    http://www.excelforum.com/showthread...9804&p=4561088
    http://forum.chandoo.org/threads/qua...7/#post-194295
    http://forum.chandoo.org/threads/qua...5/#post-194172

  12. #12
    Forum Expert Alf's Avatar
    Join Date
    03-13-2004
    Location
    Gothenburg/Mullsjoe, Sweden
    MS-Off Ver
    Excel 2019 and not sure I like it
    Posts
    4,758

    Re: Quality Control Chart from CSV File

    Hi Frank

    As you have now added the liks to the cross post (you only need to do it once by the way) I'm adding my comments.

    You say that when manually replace "January" with "01-" it converts data to the approppriate format. You could then add a line in my macro so it would look like this:

    Please Login or Register  to view this content.
    in order to replace January and get the correct date formate. Still as I've not seen what your result like I'm not sure if you wish to replace the value "January" in the B or the C column.

    Running the macro I wrote in post #6 I get the result as shown in the image:

    csv_imp.jpg

    so I see the date format in the way you wanted in cell C8 but in the formula bar it's shown in the Scandinavian way "YYYY-MM-DD hh:mm" but I'm not sure you see the date like this.

    So with the date problem and the pH value where I still don't know if you get this as numbers or not I think I can't help you any more. You should get help from sombody who uses the same date formate and number delimiter.

    Alf

  13. #13
    Registered User
    Join Date
    02-06-2005
    MS-Off Ver
    Excell 2016
    Posts
    35

    Re: Quality Control Chart from CSV File

    Hi Alf,

    Thank you for your help. The pH and Temperature values should be numbers. The date time can show seconds if you set the custom time format dd/mm/yy hh:mm:ss. I am beginning to wonder if the data portal is formatted in a European rather US time format.

    Best,

    Frank

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Quality Control Charts
    By nooredein in forum Excel General
    Replies: 19
    Last Post: 06-20-2014, 10:28 AM
  2. Replies: 9
    Last Post: 02-12-2011, 10:04 PM
  3. Copying a resized chart onto a UserForm without losing much quality
    By asadim in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 01-29-2009, 06:30 AM
  4. Improve quality of exported chart
    By bhofsetz in forum Excel Programming / VBA / Macros
    Replies: 7
    Last Post: 10-28-2008, 06:39 AM
  5. Using VBA to control Duplex, Color Mode and Print Quality
    By mkvassh in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 11-02-2007, 12:50 PM
  6. quality control
    By himura137 in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 04-28-2007, 11:28 AM
  7. [SOLVED] HOW DO I USE EXCEL FOR QUALITY CONTROL CHART SUCH AS TEMPERATURE .
    By GEE in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 03-30-2006, 09:30 AM

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1