Often, we get our files in CSV format. That file format is not very user-friendly. In this article, we’re going to show 6 methods to convert CSV to XLSX without opening. To demonstrate our techniques, we’ve chosen a dataset with 3 columns: “Name”, “Email”, and “Birth Year”.
Download Practice Workbook
5 Ways to Convert CSV to XLSX without Opening
1. Use of Text Import Wizard to Convert CSV to XLSX without Opening
For the first method, we’re going to use the Text Import Wizard to convert CSV to XLSX format.
Steps:
- Firstly, from the Data tab >>> select From Text/CSV.
The Import Data dialog box will appear.
- Secondly, select the file named “convert-csv-to-xlsx.csv”, and press Import.
A new window will appear.
- Thirdly, from Load >>> select “Load To…”.
Here, we’ll select our output location.
- Then, click on “Existing worksheet:” and point to cell A1.
- Finally, press OK.
Thus, we’ll convert our CSV file to XLSX.
Read More: How to Convert CSV to XLSX (4 Quick Methods)
2. Convert CSV to XLSX without Opening by Using Legacy Wizard
In this section, we’re going to use a Legacy Wizard to convert our file from CSV to XLSX.
Steps:
- Firstly, press ALT + F + T to bring up the Excel Options window.
- Secondly, from Data >>> put a tick mark on “From Text (Legacy)” and press OK.
- Thirdly, from the Data tab >>> Get Data >>> Legacy Wizards >>> select From Text (Legacy).
Now, we’ll Import our file again.
After that, the Text Import Wizard window will appear.
- Then, put a tick mark on “My data has headers”, and click on Next.
- After that, choose Comma as Delimiter, and press Next.
- Then, click on Finish.
Then, we’ll set our output location as cell A1 in Sheet2.
- Finally, press OK.
In conclusion, we’ll solve our problem using this method. Moreover, this is what the final step should look like.
Read More: Excel VBA to Import CSV File without Opening (3 Suitable Examples)
3. Convert CSV to XLSX without Opening by Using VBA Code
In this method, we’ll use another VBA code to convert CSV files. Here, we’re going to define our input and output files, and this code is for a single file only.
Steps:
- Firstly, select our file and from the Home tab >>> select Copy path.
This will copy the location of our file.
- Secondly, as shown in the third method, bring up the Module window and type the following code.
Sub CsvToXlsxConversion2()
Dim w As Workbook
Set w = Workbooks.Open("C:\Users\Rafi\OneDrive\Desktop\Softeko\36\convert-csv-to-xlsx.csv")
w.SaveAs Filename:="C:\Users\Rafi\OneDrive\Desktop\Softeko\36\convert-csv-to-xlsx.xlsx", _
FileFormat:=xlWorkbookDefault, _
ReadOnlyRecommended:=False, CreateBackup:=False
End Sub
VBA Code Breakdown
- Firstly, we’re calling our Sub Procedure CsvToXlsxConversion2.
- Secondly, declaring our variable types.
- Thirdly, we’re defining our input file using the Set Statement.
Here, we’ve got our file location via the Copy path option. - Finally, we’re setting our output file as “converted.xlsx” and the file format as xlWorkbookDefault, which means XLSX format.
- Finally, Save and Run the Module.
Thus, we’ve shown you yet another method of converting CSV files to XLSX.
Read More: Excel VBA to Convert CSV File to XLSX (2 Easy Examples)
Similar Readings
- How to Import Text File to Excel Automatically (2 Suitable Ways)
- Excel VBA: Import Comma Delimited Text File (2 Cases)
- How to Import CSV into Existing Sheet in Excel (5 Methods)
4. Applying VBA Code to Convert Multiple CSV Files to XLSX without Opening
We’re going to use Excel VBA to convert CSV files. Here, we’ll select a folder in this method, moreover, this code can convert every file inside that folder. Without further ado, let’s jump into the action.
Here, we’ll bring up the Module window inside Visual Basic.
- Firstly, from the Developer tab >>> select Visual Basic.
- Secondly, from Insert >>> select Module.
This is where we’ll write our code.
- Thirdly, type the following code.
Sub CsvToXlsxConversion()
Dim f As FileDialog
Dim fPath As String
Dim csvFile As String
Dim ws As String
Application.DisplayAlerts = False
Application.StatusBar = True
ws = ActiveWorkbook.Name
Set f = Application.FileDialog(msoFileDialogFolderPicker)
f.Title = "Select a folder:"
If f.Show = -1 Then
fPath = f.SelectedItems(1)
Else
Exit Sub
End If
If Right(fPath, 1) <> "\" Then fPath = fPath + "\"
csvFile = Dir(fPath & "*.csv")
Do While csvFile <> ""
Application.StatusBar = "Converting: " & csvFile
Workbooks.Open Filename:=fPath & csvFile
ActiveWorkbook.SaveAs Replace(fPath & csvFile, ".csv", ".xlsx", vbTextCompare), xlWorkbookDefault
ActiveWorkbook.Close
Windows(ws).Activate
csvFile = Dir
Loop
Application.StatusBar = False
Application.DisplayAlerts = True
End Sub
VBA Code Breakdown
- Firstly, we’re calling our Sub Procedure CsvToXlsxConversion.
- Secondly, declaring our variable types.
- Thirdly, we’re displaying the InputBox to pick a folder.
- Then, the code looks for all CSV files in that folder.
- Finally, we’re using the Do While Loop to convert all CSV files.
- Then, Save the Module.
- After that, click anywhere in the code and press the Run button.
It will ask us to select a folder.
- Finally, select a folder, and press OK.
We’ll see two files with XLSX format in that folder.
Moreover, we can open any file to make sure our data conversion is OK.
Read More: How to Open CSV File in Excel with Columns Automatically (3 Methods)
5. Utilizing Command-Line to Convert CSV to XLSX
In this section, we’re going to use the Command Prompt to convert CSV files.
Steps:
- Firstly, type the following code in the notepad.
sourceFile= Wscript.Arguments(0)
targetFile = Wscript.Arguments(1)
On Error Resume Next
Set tExcel = GetObject(,"Excel.Application")
If Err.Number = 429 Then
Set tExcel = CreateObject("Excel.Application")
End If
tExcel.Visible = false
tExcel.displayalerts=false
Set tWorkbook = tExcel.Workbooks.open(sourceFile)
Set tWorksheet1 = tWorkbook.Worksheets(1)
Set tRange = tWorksheet1.UsedRange
tRange.EntireColumn.Autofit()
tExcel.Rows(1).Font.Bold = TRUE
tWorksheet1.SaveAs targetFile, 51
tExcel.Quit()
VBS Code Breakdown
This is the Visual Basic scripting edition code.
- Firstly, we’re setting two arguments.
- Secondly, we’re defining the input file. Moreover, the first Worksheet is used in our code only.
- Thirdly, we’re setting the cell range.
- Then, we’re using Autofit.
- After that, we’re making the first row Bold.
- Finally, we’re saving the file as 51 (this means XLSX).
- Secondly, select All Files in “save as type:”.
- Thirdly, Save it as “conversion.vbs” filename.
- Then, from the Start Menu >>> open Command Prompt.
- After that, type this code and press ENTER.
CD "C:\Users\USER\Desktop\Exceldemy"
This will set our directory to our working folder.
- Then, type this code.
conversion "C:\Users\USER\Desktop\Exceldemy\convert-csv-to-xlsx.csv" "C:\Users\USER\Desktop\Exceldemy\converted-using-cmd.xlsx"
This code will run our “conversion.vbs” file. In our code, the first location is the input and the second one is the output file.
- Finally, press ENTER.
Then, we can open our “converted-using-cmd.xlsx” file to verify everything. We’ll see if everything worked as expected.
Read More: How to Open CSV File with Columns in Excel (3 Easy Ways)
Convert CSV Incorporating Online Converter
For the last method, we can use any online converter. Here, we’re gonna use the website Cloud Convert to convert our file.
Steps:
- Firstly, go to the website.
- Secondly, select our file.
- Thirdly, select our file and click on Open.
- Then, select XLSX as our output format and press Convert.
- Finally, click on Download.
In conclusion, you can convert your CSV files via online tools too and this is what the final step should look like.
Read More: How to Read CSV File in Excel (4 Fastest Ways)
Conclusion
We’ve shown you 5 methods to convert CSV to XLSX without opening. If you face any problems regarding any of the methods, feel free to comment below. Thanks for reading, keep excelling!
The below works fine for me and is what i’ve been looking for, but how do i get it to set all columns to text?
Thank you.
sourceFile= Wscript.Arguments(0)
targetFile = Wscript.Arguments(1)
On Error Resume Next
Set tExcel = GetObject(,”Excel.Application”)
If Err.Number = 429 Then
Set tExcel = CreateObject(“Excel.Application”)
End If
tExcel.Visible = false
tExcel.displayalerts=false
Set tWorkbook = tExcel.Workbooks.open(sourceFile)
Set tWorksheet1 = tWorkbook.Worksheets(1)
Set tRange = tWorksheet1.UsedRange
tRange.EntireColumn.Autofit()
tExcel.Rows(1).Font.Bold = TRUE
tWorksheet1.SaveAs targetFile, 51
tExcel.Quit()
Thank you so much JOHN JOYCE for your comment. To set all the columns into text, you can follow the below steps accordingly.
After changing the format save the file and re-open it to check whether the data shows in the proper format.
Best Regards,
Bishwajit
Team ExcelDemy