| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.programming
|
|||
|
|||
Importing CSV Finding Min/Max
I have multiple *.csv files that I want to find the min/max of each data
column and write to an excel worksheet. The original *.csv data from the file does not need to be written out to the worksheet, only the min/max values for each column. For example, here is some sample data for *.csv File #1. Example of first few lines of *.csv File1 %TIME,DATE_TIME,Data A,Data B,Data C,Data D 1202626798.174,"02/01/08 11:00:03 MST",4368,-2.29,33.6,9.216e-07 1202626803.174,"02/01/08 11:00:08 MST",4752,.51,44.2,9.216e-06 1202626808.174,"02/01/08 11:00:13 MST",3992,1.23,-40.6,8.07e-07 Ideally, it would look something like this in the Excel spreadsheet: File1 Data A Data B Data C Data D Min 3992 -2.29 -40.6 8.07e-07 Max 4752 1.23 44.2 9.216e-06 Again, subsequently would follow File 2, File 3, etc. All data could be written to the same worksheet. Thanks for your help! |
|
#2
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Importing CSV Finding Min/Max
Hi kazoo
Try following code: Sub ReadCSVMinMax() Application.ScreenUpdating = False Application.DisplayAlerts = False ActWB = ActiveWorkbook.Name ActSheetRow = 1 ActSheetCol = 1 CSVDir = "D:\ExcelTest\" MaxNoOfCSVFile = 3 For i = 1 To MaxNoOfCSVFile ' Write CSV-filename, min and max into active sheet Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow, ActSheetCol).Value = "File" & i Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow + 2, ActSheetCol).Value = "Min" Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow + 3, ActSheetCol).Value = "Max" ' Open CSV-file Workbooks.Open Filename:=CSVDir & "File" & i Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=True, Comma:=True, Space:=False, Other:=False UsedRows = ActiveSheet.UsedRange.Rows.Count UsedCols = ActiveSheet.UsedRange.Columns.Count ' Select and transfer data For cols = 3 To UsedCols ActSheetCol = ActSheetCol + 1 ' Header of Column Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow + 1, ActSheetCol).Value = ActiveSheet.Cells(1, cols).Value ' Min of Column Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow + 2, ActSheetCol).Value = Application.WorksheetFunction.Min(ActiveSheet.Rang e(Cells(1, cols), Cells(UsedRows, cols))) ' Max of Column Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow + 3, ActSheetCol).Value = Application.WorksheetFunction.Max(ActiveSheet.Rang e(Cells(1, cols), Cells(UsedRows, cols))) Next cols ' Close CSV-file ActiveWorkbook.Saved = True ActiveWorkbook.Close ' Define Row and Col for next data ActSheetRow = ActSheetRow + 5 ActSheetCol = 1 Next i End Sub regards reklamo "kazoo" wrote: I have multiple *.csv files that I want to find the min/max of each data column and write to an excel worksheet. The original *.csv data from the file does not need to be written out to the worksheet, only the min/max values for each column. For example, here is some sample data for *.csv File #1. Example of first few lines of *.csv File1 %TIME,DATE_TIME,Data A,Data B,Data C,Data D 1202626798.174,"02/01/08 11:00:03 MST",4368,-2.29,33.6,9.216e-07 1202626803.174,"02/01/08 11:00:08 MST",4752,.51,44.2,9.216e-06 1202626808.174,"02/01/08 11:00:13 MST",3992,1.23,-40.6,8.07e-07 Ideally, it would look something like this in the Excel spreadsheet: File1 Data A Data B Data C Data D Min 3992 -2.29 -40.6 8.07e-07 Max 4752 1.23 44.2 9.216e-06 Again, subsequently would follow File 2, File 3, etc. All data could be written to the same worksheet. Thanks for your help! |
|
#3
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Importing CSV Finding Min/Max
This seems to work great. Another question, if instead of a =MIN(A2:A???)
function, I want to use an array formula (CTRL+SHIFT+ENTER) , where ??? is the end of the column to exclude the number -273.15 in finding the min, how do I translate this to VB? =MIN(IF(ISNUMBER(A2:A???)*(A2:A???<-273.15),A2:A???)) "reklamo" wrote: Hi kazoo Try following code: Sub ReadCSVMinMax() Application.ScreenUpdating = False Application.DisplayAlerts = False ActWB = ActiveWorkbook.Name ActSheetRow = 1 ActSheetCol = 1 CSVDir = "D:\ExcelTest\" MaxNoOfCSVFile = 3 For i = 1 To MaxNoOfCSVFile ' Write CSV-filename, min and max into active sheet Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow, ActSheetCol).Value = "File" & i Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow + 2, ActSheetCol).Value = "Min" Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow + 3, ActSheetCol).Value = "Max" ' Open CSV-file Workbooks.Open Filename:=CSVDir & "File" & i Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=True, Comma:=True, Space:=False, Other:=False UsedRows = ActiveSheet.UsedRange.Rows.Count UsedCols = ActiveSheet.UsedRange.Columns.Count ' Select and transfer data For cols = 3 To UsedCols ActSheetCol = ActSheetCol + 1 ' Header of Column Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow + 1, ActSheetCol).Value = ActiveSheet.Cells(1, cols).Value ' Min of Column Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow + 2, ActSheetCol).Value = Application.WorksheetFunction.Min(ActiveSheet.Rang e(Cells(1, cols), Cells(UsedRows, cols))) ' Max of Column Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow + 3, ActSheetCol).Value = Application.WorksheetFunction.Max(ActiveSheet.Rang e(Cells(1, cols), Cells(UsedRows, cols))) Next cols ' Close CSV-file ActiveWorkbook.Saved = True ActiveWorkbook.Close ' Define Row and Col for next data ActSheetRow = ActSheetRow + 5 ActSheetCol = 1 Next i End Sub regards reklamo "kazoo" wrote: I have multiple *.csv files that I want to find the min/max of each data column and write to an excel worksheet. The original *.csv data from the file does not need to be written out to the worksheet, only the min/max values for each column. For example, here is some sample data for *.csv File #1. Example of first few lines of *.csv File1 %TIME,DATE_TIME,Data A,Data B,Data C,Data D 1202626798.174,"02/01/08 11:00:03 MST",4368,-2.29,33.6,9.216e-07 1202626803.174,"02/01/08 11:00:08 MST",4752,.51,44.2,9.216e-06 1202626808.174,"02/01/08 11:00:13 MST",3992,1.23,-40.6,8.07e-07 Ideally, it would look something like this in the Excel spreadsheet: File1 Data A Data B Data C Data D Min 3992 -2.29 -40.6 8.07e-07 Max 4752 1.23 44.2 9.216e-06 Again, subsequently would follow File 2, File 3, etc. All data could be written to the same worksheet. Thanks for your help! |
|
#4
Posted to microsoft.public.excel.programming
|
|||
|
|||
|
Importing CSV Finding Min/Max
One way:
Option Explicit Sub testme() Dim wks As Worksheet Dim LastRow As Long Dim myFormula As String Set wks = Worksheets("Sheet1") With wks LastRow = .Cells(.Rows.Count, "A").End(xlUp).Row myFormula = ),A2:A@@@))" myFormula = Replace(myFormula, "@@@", LastRow) MsgBox .Evaluate(myFormula) End With End Sub kazoo wrote: This seems to work great. Another question, if instead of a =MIN(A2:A???) function, I want to use an array formula (CTRL+SHIFT+ENTER) , where ??? is the end of the column to exclude the number -273.15 in finding the min, how do I translate this to VB? =MIN(IF(ISNUMBER(A2:A???)*(A2:A???<-273.15),A2:A???)) "reklamo" wrote: Hi kazoo Try following code: Sub ReadCSVMinMax() Application.ScreenUpdating = False Application.DisplayAlerts = False ActWB = ActiveWorkbook.Name ActSheetRow = 1 ActSheetCol = 1 CSVDir = "D:\ExcelTest\" MaxNoOfCSVFile = 3 For i = 1 To MaxNoOfCSVFile ' Write CSV-filename, min and max into active sheet Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow, ActSheetCol).Value = "File" & i Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow + 2, ActSheetCol).Value = "Min" Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow + 3, ActSheetCol).Value = "Max" ' Open CSV-file Workbooks.Open Filename:=CSVDir & "File" & i Columns("A:A").Select Selection.TextToColumns Destination:=Range("A1"), DataType:=xlDelimited, _ TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _ Semicolon:=True, Comma:=True, Space:=False, Other:=False UsedRows = ActiveSheet.UsedRange.Rows.Count UsedCols = ActiveSheet.UsedRange.Columns.Count ' Select and transfer data For cols = 3 To UsedCols ActSheetCol = ActSheetCol + 1 ' Header of Column Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow + 1, ActSheetCol).Value = ActiveSheet.Cells(1, cols).Value ' Min of Column Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow + 2, ActSheetCol).Value = Application.WorksheetFunction.Min(ActiveSheet.Rang e(Cells(1, cols), Cells(UsedRows, cols))) ' Max of Column Workbooks(ActWB).ActiveSheet.Cells(ActSheetRow + 3, ActSheetCol).Value = Application.WorksheetFunction.Max(ActiveSheet.Rang e(Cells(1, cols), Cells(UsedRows, cols))) Next cols ' Close CSV-file ActiveWorkbook.Saved = True ActiveWorkbook.Close ' Define Row and Col for next data ActSheetRow = ActSheetRow + 5 ActSheetCol = 1 Next i End Sub regards reklamo "kazoo" wrote: I have multiple *.csv files that I want to find the min/max of each data column and write to an excel worksheet. The original *.csv data from the file does not need to be written out to the worksheet, only the min/max values for each column. For example, here is some sample data for *.csv File #1. Example of first few lines of *.csv File1 %TIME,DATE_TIME,Data A,Data B,Data C,Data D 1202626798.174,"02/01/08 11:00:03 MST",4368,-2.29,33.6,9.216e-07 1202626803.174,"02/01/08 11:00:08 MST",4752,.51,44.2,9.216e-06 1202626808.174,"02/01/08 11:00:13 MST",3992,1.23,-40.6,8.07e-07 Ideally, it would look something like this in the Excel spreadsheet: File1 Data A Data B Data C Data D Min 3992 -2.29 -40.6 8.07e-07 Max 4752 1.23 44.2 9.216e-06 Again, subsequently would follow File 2, File 3, etc. All data could be written to the same worksheet. Thanks for your help! -- Dave Peterson |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Importing etc. | Excel Worksheet Functions | |||
| Importing | Excel Worksheet Functions | |||
| Importing Alan Beban's code on Arrays; Importing a module or a project | Excel Worksheet Functions | |||
| Importing | Excel Discussion (Misc queries) | |||
| Importing and Finding Matching data | Excel Programming | |||
Linear Mode
