Batch CSV conversion with commas in numbers

Sorttaaja2

New Member
Joined
Jan 23, 2018
Messages
14
Happy 2020!

How to write a macro that converts folder of excel files to csv files?
I found this kind of code that works well : How to batch convert multiple Excel files to CSV files in Excel?
but it changes 1,5 s into 1.5 s and that is not ok. so how to modify it to keep the commas in numbers , like the excels save as CSV UTF-8 does.


Thankyou


and joining the excel or csv files into one file would be nice to learn too. :)
 

Some videos you may like

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.

pbornemeier

Well-known Member
Joined
May 24, 2005
Messages
3,889
Can you manually export one Excel file to CSV and obtain the desired results?
If so, turn on your macro recorder, do that and post the resultant code here.
The problem with the referenced code may occur if your default numbers use a different code page.
If you export Excel files as CSV with quotes as text delimiters, the commas inside the numbers should not be a problem. This code from : Export a text file with both comma and quote delimiters in Excel - Office will help with that.

Code:
Sub QuoteCommaExport()
   ' Dimension all variables.
   Dim DestFile As String
   Dim FileNum As Integer
   Dim ColumnCount As Integer
   Dim RowCount As Integer

   ' Prompt user for destination file name.
   DestFile = InputBox("Enter the destination filename" _
      & Chr(10) & "(with complete path):", "Quote-Comma Exporter")

   ' Obtain next free file handle number.
   FileNum = FreeFile()

  ' Turn error checking off.
   On Error Resume Next

   ' Attempt to open destination file for output.
   Open DestFile For Output As #FileNum

   ' If an error occurs report it and end.
   If Err <> 0 Then
      MsgBox "Cannot open filename " & DestFile
      End
   End If

   ' Turn error checking on.
   On Error GoTo 0

   ' Loop for each row in selection.
   For RowCount = 1 To Selection.Rows.Count

   ' Loop for each column in selection.
      For ColumnCount = 1 To Selection.Columns.Count

        ' Write current cell's text to file with quotation marks.
         Print #FileNum, """" & Selection.Cells(RowCount, _
            ColumnCount).Text & """";

         ' Check if cell is in last column.
         If ColumnCount = Selection.Columns.Count Then
            ' If so, then write a blank line.
            Print #FileNum,
         Else
            ' Otherwise, write a comma.
            Print #FileNum, ",";
         End If
      ' Start next iteration of ColumnCount loop.
      Next ColumnCount
   ' Start next iteration of RowCount loop.
   Next RowCount

   ' Close destination file.
   Close #FileNum
End Sub
[code]

As far as combining the files into a single file that is not difficult, but it is necessary to either assume that the headers for each file match up (can be dangerous) or to open and verify that the headers match during the combining process (requires lots more info).  Also either there is a need to define which files (if not all) to combine in a particular directory.
 

Watch MrExcel Video

Forum statistics

Threads
1,127,589
Messages
5,625,671
Members
416,125
Latest member
NeedExcelHelp2021

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top