Fast processing for a csv file.

[wwj]

How to import a big CSV to Excel.

Code: Select all

Sub Import_and_ConvertFormat_block()
'
' Import the data and Convert their format from 2 column for all wells onto two columns each obs. well
'
' Variables
    Dim filePath  As String
    Dim inputFile  As String
    Dim inputSheet  As String
    Dim stdWorkbook As String
    Dim dataSheet As String
    Dim ws As Worksheet
    
    Dim initRowPlot  As Integer
    
    Dim i  As Long
    Dim colNo  As Integer
    Dim rowNo  As Integer   ' -32,768 and 32,767
    
    ThisWorkbook.Activate
    
'   Parameters.
    folderNm = Worksheets("macro").Cells(6, "D")
    inputFile = "1_Hw.csv"
    filePath = ActiveWorkbook.Path & "\" & folderNm & "\" & inputFile
    
    'If filePath = "False" Then
    '    MsgBox "File Not Found"
    '    Exit Sub
    'End If
    
    dataSheet = "FormedData"
    Set ws = ThisWorkbook.Worksheets(dataSheet)
        
    If filePath = "False" Then
        MsgBox "File Not Found"
        Exit Sub
    End If
    
'   [1] Open the csv file. It exceeds the Excel limits (# of rows).
    fileNumber = FreeFile
    Open filePath For Input As #fileNumber
    
    outCol = 1
    ' Read and import the CSV line by line
    Do While Not EOF(fileNumber)
        Line Input #fileNumber, lineText
        Data = Split(lineText, ",")
        
        ' Split the data for each location.
        ' Re-arrange the input data
        elementCount = UBound(Data) - LBound(Data) + 1
        If elementCount = 1 Then
            Name = Data(0)  ' First column
            ws.Cells(1, outCol).Value = Data(0)
            
            ' Observation data
            Line Input #fileNumber, lineText
            Data = Split(lineText, ",")
            
            If Data(1) = "Observed" Then
                ws.Cells(2, outCol).Value = "O.Tm(d)"
                ws.Cells(2, outCol + 1).Value = "Obs(ft)"
            
                numObs = Data(0)
                ' Preallocate observation data array
                ReDim obsData(1 To numObs, 1 To 2)
                
                For i = 1 To numObs
                    Line Input #fileNumber, lineText
                    Data = Split(lineText, ",")
                    obsData(i, 1) = Data(0)
                    obsData(i, 2) = Data(1)
                Next i
                
                ' Write observation data in bulk
                ws.Range(ws.Cells(3, outCol), ws.Cells(2 + numObs, outCol + 1)).Value = obsData
                
            End If
            
            ' Model-calculated data
            Line Input #fileNumber, lineText
            Data = Split(lineText, ",")
            
            If Data(1) = "Computed" Then
                outCol = outCol + 2
                
                ws.Cells(2, outCol).Value = "M.Tm(d)"
                ws.Cells(2, outCol + 1).Value = "Modeled(ft)"
                
                numModel = Data(0)
                ' Preallocate model data array
                ReDim modelData(1 To numModel, 1 To 2)
                
                For i = 1 To numModel
                    Line Input #fileNumber, lineText
                    Data = Split(lineText, ",")
                    
                    modelData(i, 1) = Data(0)
                    modelData(i, 2) = Data(1)
                Next i
                
                ' Write model data in bulk
                ws.Range(ws.Cells(3, outCol), ws.Cells(2 + numModel, outCol + 1)).Value = modelData
            End If
        
            outCol = outCol + 2
        End If

    Loop
    
    ' Close the CSV file
    Close #fileNumber
    
'   Remove the inputsheet.
    Application.DisplayAlerts = False
    Application.DisplayAlerts = True
    
    ' Conert the elapsed time by excluding SS time periods.
    Call convert_elapsed_time("True")
    Call convert_elapsed_time_to_date("True")
    
    Worksheets("macro").Select
    ' Message box
    MsgBox "Done to import and convert modeling data! "
    
End Sub