Skip to main content

Software Development

How to convert a CSV file to an Excel file

Excel Power Query Data Sources

Converting CSV to Excel streamlines data manipulation and analysis, bridging the simplicity of CSV’s plain text structure with Excel’s powerful spreadsheet functionalities. This conversion ensures a seamless transition from comma-separated records to organized rows and columns, enhancing data accessibility and interpretation. Whether for data analysis, visualization, or collaboration, Excel’s versatile format accommodates diverse needs, offering features like formulas, charts, and conditional formatting.

  • Converted the CSV file with leading zero’s unchanged when changed to an Excel file.
  • Multiple CSV files are automatically converted to Excel files using code built in a loop.

By converting multiple CSV files to an Excel file using VBA code.


Sub Csv_to_Excel()
' Csv_to_Excel
Dim Pathname As String
Dim Filename As String
Dim WOextn As String
Dim Nam As String

Pathname = "<Specify the Souce Path>"
Filename = Dir(Pathname)

Do While Filename <> ""
    WOextn = Left(Filename, InStr(1, Filename, ".") - 1)
    Nam = Pathname & "" & Filename
    Debug.Print Nam
    ActiveWorkbook.Queries.Add Name:=WOextn, Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(" & Chr(34) & Nam & Chr(34) & "),[Delimiter="","", Columns=25, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"""
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=" & WOextn & ";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [" & WOextn & "]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        Debug.Print WOextn
        .ListObject.DisplayName = WOextn
        .Refresh BackgroundQuery:=False
    End With
    Application.CommandBars("Queries and Connections").Visible = False
    ActiveSheet.Name = WOextn
    ActiveWorkbook.SaveAs Filename:="<Specify the target path>" & WOextn & ".xlsx"
    Filename = Dir()
End Sub

Step By Step Procedure to Run the VBA code:

Step 1: Open a new Excel sheet.

Step 1

Step 2: Go to the Developer tab ribbon option.

Step 2

Step 3: Select the Visual Basic option in the Developer Tab.

Step 3

Step 4: Selecting the Visual Basic option opens a new window.

Step 4

Step 5: On the Project tab, right-click the VBA project. Click Module after choosing the Insert option.

Step 5

Step 6: The module option will show in the Project tab under the VBA Project, and the right-side code space will open.

Step 6

Step 7: Paste the VBA code in the code space.

Step 8

Step 8: Select Run or press F5 to run the code from here manually.

Step 9

All of the CSV files that are in the designated folder have been successfully converted into Excel files and loaded into the target folder once the VBA code has been executed.




Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Christon Ramesh Jason

Christon works at Perficient as an associate technical consultant. He is proficient in Informatica and SQL and has a solid command over data integration and manipulation. He can leverage both technologies to streamline data workflows, enhance data quality, and drive insightful decision-making. Christon is adept at writing efficient SQL queries for data retrieval, manipulation, and analysis, ensuring optimal database performance. He shows an intense passion for learning, technology, and innovation.

More from this Author

Follow Us