Anything Access

As a csv file is a text delimited file it doesn’t contain any formatting. The process of formatting csv files in Excel is a repetitive process which is an ideal candidate for a macro.

You can store macros you need to run on different excel workbooks in your personal workbook. This is a hidden workbook which loads each time you start Excel.

When the csv has loaded , click Developer, Macro and run the macro FormatCSVandAutoFit as shown in the video below.

Macro for Formatting CSV files in Excel

Sub FormatCSVandAutoFit()
    'formatting csv files in excel
    Dim ws As Worksheet
    Dim tbl As ListObject
    Dim rng As Range

    ' Set the active sheet
    Set ws = ActiveSheet
    
    ' Check if the sheet is empty
    If WorksheetFunction.CountA(ws.Cells) = 0 Then
        MsgBox "The sheet is empty. Please add data before formatting as a table.", vbExclamation, "No Data"
        Exit Sub
    End If

    ' Set the range to the current region around A1
    On Error Resume Next
    Set rng = ws.Cells(1, 1).CurrentRegion
    On Error GoTo 0

    If rng Is Nothing Then
        MsgBox "Unable to detect data range. Make sure the data starts from cell A1.", vbExclamation, "No Data Detected"
        Exit Sub
    End If

    ' Check if a table already exists in the range
    On Error Resume Next
    Set tbl = ws.ListObjects(1)
    On Error GoTo 0

    If tbl Is Nothing Then
        ' Create the table
        Set tbl = ws.ListObjects.Add(xlSrcRange, rng, , xlYes)
    Else
        MsgBox "A table already exists on this sheet. The style will be updated.", vbInformation, "Table Exists"
    End If

    ' Apply the Medium 2 Blue table style
    tbl.TableStyle = "TableStyleMedium2"

    ' Autofit the columns
    rng.Columns.AutoFit

    MsgBox "The sheet has been formatted as a table with style 'Medium 2 (Blue)', and columns have been autofitted.", vbInformation, "Success"
End Sub

You can also review our CSV Opener which automates the process of opening and preserving the content of CSV files.