Convert Single or Multiple CSV To Excel (Xlsx)

There are a requirement needed to extract source text and currently translated text into a sheet and let third party to do translation. So this task is assigned to my team intern (Adrain) and under my guidance. So when I first received this task, I already think of a general automation way to extract the info via using python library (Beautiful soup). I have once using that library for my web scraping project so that I assume it will work on the xml tag file as well. 

Therefore, what I proposed to my intern is to using that and extract the source text into a csv with delimiter, so that it will categorize it nicely when we use excel or other applications. I suggest he to break the source text by context name makes them into separate spreadsheet in a workbook.

And today, my team lead tell me since using excel already what not also extract other languages make them together with different column? After some discussion I only realize lol CSV cannot support multiple spreadsheet 😂😂 I was so stupid and say it very confidently to my intern 🤦 😂 我坑他了😂

So after I reach home, I try to explore is there any way to make this possible? And after 2 hours of exploring, Finally I found the way to do the below requirements

• 1. Makes multiple csv into a single sheet under an excel workbook 
• 2. Makes multiple csv into separate sheet under an excel workbook

Let's me continue after I bath, busy exploring until delay my bathing time 😂

1. Makes multiple csv into a single sheet under an excel workbook (Example)

1.  Create a,csv & b.csv
2.  Place both of the file in a folder.
3.  Copy the folder directory file path.
4. Run Command Line in your PC
5. Navigate to the drive of the path 
• eg. C:\Users\User> d:
• This command is changing the directory to d: drive
6. Navigate to the folder path
• eg. C:\Users\User> cd D:\Desktop\TestCSV
7. Insert the command below
• D:\Desktop\TestCSV> copy *.csv test.csv
• * means all
• copy <filename>.csv <new_csv_name>.csv
• This command is basically copy all csv file in the folder and make it into a "test.csv" file
8. Open the csv using microsoft excel
9. You will see the details of a.csv and b.csv is in the test.csv's test sheet.
10. Save as Excel

2. Makes multiple csv into separate sheet under an excel workbook

To achieve this you actually not required to buy any extra software.

1. Launch Microsoft Excel
2. Enable developer tab in your Microsoft Excel
• File > Options > Customize Ribbon to enable the Developer tab
3. Go to the Developer > VisualBasic
• 
4. A window is pop up
5. Insert a module Insert>Module
• 
6. Copy Paste the below code in the module

   	Sub CombineTextFiles()
   	'updateby Extendoffice 20151015
   	Dim xFilesToOpen As Variant
   	Dim I As Integer
   	Dim xWb As Workbook
   	Dim xTempWb As Workbook
   	Dim xDelimiter As String
   	Dim xScreen As Boolean
   	On Error GoTo ErrHandler
   	xScreen = Application.ScreenUpdating
   	Application.ScreenUpdating = False
   	xDelimiter = "|"
   	xFilesToOpen = Application.GetOpenFilename("Text Files (*.csv), *.csv", , "Kutools for Excel", , True)
   	If TypeName(xFilesToOpen) = "Boolean" Then
   	MsgBox "No files were selected", , "Kutools for Excel"
   	GoTo ExitHandler
   	End If
   	I = 1
   	Set xTempWb = Workbooks.Open(xFilesToOpen(I))
   	xTempWb.Sheets(1).Copy
   	Set xWb = Application.ActiveWorkbook
   	xTempWb.Close False
   	Do While I < UBound(xFilesToOpen)
   	I = I + 1
   	Set xTempWb = Workbooks.Open(xFilesToOpen(I))
   	xTempWb.Sheets(1).Move , xWb.Sheets(xWb.Sheets.Count)
   	Loop
   	ExitHandler:
   	Application.ScreenUpdating = xScreen
   	Set xWb = Nothing
   	Set xTempWb = Nothing
   	Exit Sub
   	ErrHandler:
   	MsgBox Err.Description, , "Kutools for Excel"
   	Resume ExitHandler
   	End Sub

   view raw CombineTextFiles.txt hosted with ❤ by GitHub
7. Developer > Macro > Run > Select the csv file that wish to combined
• 
8. You will see the details of a.csv and b.csv is in separate spreadsheet under a workbook.
9. Save as Excel

Reference:

1. Mrexcel Forum
2. Microsoft Support
3. Ablebits Website