SPC data from multiple Excel files

E

Excel_Enthusiast

#1
Hello all...
I have an Arduino linked to a laser gauge, which in turn feeds an excel spreadsheet with diameter data and Lind speeds etc...
On completion of each job, the operator click a button, and the data for the given run is saved as a csv file on the server - this activity is repeated every four hours roughly (24/7)
I have created a spreadsheet that can look into specific folders and import all the csv's for a specific product.
Ideally I need to also specify a 'start date' and 'end date' using I suspect something like the 'date last modified' function...
This facility will then allow me to load in 3 months worth of csv files so that I can compile quarterly comparison reports.

Anybody with any suggestions would be superb - advanced thanks.
 
Elsmar Forum Sponsor

Darius

Quite Involved in Discussions
#2
Why don't just add the info to a database (SQL for example, to be robust or if no other way an Access DB), You can run at your machine startup a demon (a program that stay resident and every specified time does his stuff) on your computer that search for new files to process on a folder, and if a new file arrives, import it to a database and delete the refered CSV file.

Other way is to add to an Excel worksheet, but the amount of data must not be too large.

Can you post an example of the data you are talking about, and specify the number of records per month (aprox)?
 

Darius

Quite Involved in Discussions
#3
Ideally I need to also specify a 'start date' and 'end date' using I suspect something like the 'date last modified' function...
or maybe just want simple
Code:
Sub do_something_with_files_dated()
Dim FILES_DATE As Date, F_INI as date, F_END as date,  file As Variant
  'initializations
  PROGRAM_PATH = "c:\respaldo\"
  F_INI = InputBox("Initial Date", "Init date for Report", DateSerial(Year(Now()), Month(Now()) - 4, 1))
  F_END = InputBox("End Date", "End date for Report", DateSerial(Year(Now()), Month(Now()), 1) - 1)
  
  'LoopThroughFiles
  file = Dir(PROGRAM_PATH)
  While (file <> "")
    MsgBox file
    FILES_DATE = FileDateTime(PROGRAM_PATH & file)
    If FILES_DATE >= F_INI And FILES_DATE <= F_END Then
      'do your stuff
    End If
    file = Dir
   Wend
End Sub
:magic:
 

reynald

Quite Involved in Discussions
#4
Why don't just add the info to a database (SQL for example, to be robust or if no other way an Access DB), You can run at your machine startup a demon (a program that stay resident and every specified time does his stuff) on your computer that search for new files to process on a folder, and if a new file arrives, import it to a database and delete the refered CSV file.
I would use the same concept, but what I do is use a *bat file that is scheduled to run at a certain frequency. This would use Windows' scheduled task facility. The bat file would open an Excel file that has an auto-run macro.
It is in the excel macro that I put all the data processing, data saving, and charting.

It can be nasty though If you will not use a dedicated workstation for this. It should use a computer that is not normally used by a person to perform day to day tasks
 

Steve Prevette

Deming Disciple
Staff member
Super Moderator
#5
You can use the "VLOOKUP" function (or alternately, "HLOOKUP") function to turn Excel spreadsheets into a relational database and link results from separate tables together. It is very inefficient when it comes to computer time, but given that computer processing time is so relatively cheap, if it saves programming time, it is worthwhile.
 
Thread starter Similar threads Forum Replies Date
L SPC: Methods to collect data IATF 16949 - Automotive Quality Systems Standard 6
M SPC with many data points from one dimension Statistical Analysis Tools, Techniques and SPC 16
Q Mixed data collection (SPC) for different mold cavities Statistical Analysis Tools, Techniques and SPC 2
I SPC on Cyclic Data Statistical Analysis Tools, Techniques and SPC 10
B Is SPC data For Special Characteristics always required? TS 16949 clause 8.1 ISO 9000, ISO 9001, and ISO 9004 Quality Management Systems Standards 2
M SPC - What Data do I Collect? Statistical Analysis Tools, Techniques and SPC 4
P Change the way you think about SPC - Automatic Data Collection Statistical Analysis Tools, Techniques and SPC 8
D IMR (Individuals and Moving Range) Chart for Short-Run SPC - How much data to include Statistical Analysis Tools, Techniques and SPC 1
L Moving SPC Data from Variables to an Attributes Chart Statistical Analysis Tools, Techniques and SPC 4
P SPC Data Collection and Control Chart Type question Statistical Analysis Tools, Techniques and SPC 4
K Almost embarrassed to ask... SPC UCL/Data Range Quality Tools, Improvement and Analysis 4
Q How to determine the average baseline for the following data in SPC Quality Tools, Improvement and Analysis 3
F SPC Data Analysis - Excel Spreadsheet I can use for Diameters and Perpendicularity Capability, Accuracy and Stability - Processes, Machines, etc. 6
A Best Way for SPC via Optical Comparator with output to Computer for Data Analysis Statistical Analysis Tools, Techniques and SPC 4
Hami812 Collecting Data for SPC Control Charting Statistical Analysis Tools, Techniques and SPC 7
D SPC Software that will satisfy auto/furniture data submissions - Opinions wanted Quality Assurance and Compliance Software Tools and Solutions 5
O SPC Statistical Process Control with All data Using Minitab Software 4
C Collecting SPC Data Electronically? CNC Lathes, CNC Mills and CNC Grinders Inspection, Prints (Drawings), Testing, Sampling and Related Topics 4
T SPC Data - Autocorrelation - How do I interpret this result? Statistical Analysis Tools, Techniques and SPC 17
J Online Monitoring of SPC Data and Charts in Real Time Statistical Analysis Tools, Techniques and SPC 12
Z SPC Data presentation - The best way to present histograms in a dashboard format Statistical Analysis Tools, Techniques and SPC 14
M Time frequency estimation for SPC data collection Statistical Analysis Tools, Techniques and SPC 9
T Monthly SPC original data sheets - What should be done with the reports? AS9100, IAQG 9100, Nadcap and related Aerospace Standards and Requirements 9
S SPC (Statistical Process Control) - I have data, now what? Statistical Analysis Tools, Techniques and SPC 14
B New to Minitab - What Data to use for SPC? Using Minitab Software 3
C SPC: How to set up control limits on SPC chart based on historical data? Statistical Analysis Tools, Techniques and SPC 7
M Cpk for attribute data? Performing SPC on capacitors - Capacitance measurement Statistical Analysis Tools, Techniques and SPC 9
L Beginning SPC data collection: Metal Stamping Company - Need some advice Statistical Analysis Tools, Techniques and SPC 14
C SPC for calibration check data? Daily gram scales check - What is acceptable? Statistical Analysis Tools, Techniques and SPC 5
L Portable SPC Data Collectors and Industrial 'Hardened' Computers Statistical Analysis Tools, Techniques and SPC 12
M Predictive Maintenance vs SPC (Statistical Process Control) Data Misc. Quality Assurance and Business Systems Related Topics 1
W Falsifying SPC Data - What type of disciplinary action would you take Misc. Quality Assurance and Business Systems Related Topics 13
A Non-normal Distributions in SPC - How do I Normalize Data? Statistical Analysis Tools, Techniques and SPC 55
S SPC - Go/No Go Sampling - Attribute Data Inspection, Prints (Drawings), Testing, Sampling and Related Topics 2
J SPC - Stamping parts - Small batch size Statistical Analysis Tools, Techniques and SPC 6
T Attributes SPC study - Attributive control (Go gage) Statistical Analysis Tools, Techniques and SPC 5
S Anyone Familiar with "SPC for Excel" add-in? Lean in Manufacturing and Service Industries 4
G Assignable cause/corrective action list for SPC Software Statistical Analysis Tools, Techniques and SPC 3
H SPC for special characteristics - CSR - Control of SC and CC Statistical Analysis Tools, Techniques and SPC 1
J Determining SPC tolerance Statistical Analysis Tools, Techniques and SPC 21
Marc Steve Prevette's Statistical Process Control (SPC) "Library" Statistical Analysis Tools, Techniques and SPC 0
A SPC for Eddy Current Testing Statistical Analysis Tools, Techniques and SPC 28
S SPC (Statistical Process Control) for Unilateral Tolerance - Questions Statistical Analysis Tools, Techniques and SPC 6
Proud Liberal Informational Use of Classical SPC in Precision Machining Statistical Analysis Tools, Techniques and SPC 9
J Recommendations on Inspection/SPC Software Statistical Analysis Tools, Techniques and SPC 4
Q Real Time Electronic SPC/Control Charting Statistical Analysis Tools, Techniques and SPC 4
Rameshwar25 Other types of Control Charts described in Chapter II of SPC Manual Statistical Analysis Tools, Techniques and SPC 6
A Electric terminal crimping machines - Is SPC appropriate for this process? Manufacturing and Related Processes 4
J Properly understanding SPC - Newbie SPC questions Statistical Analysis Tools, Techniques and SPC 29
bobdoering SPC Charting frequency calculator for X hi/lo chart Imported Legacy Blogs 0
Similar threads


















































Top Bottom