Save as .csv correct decimal separator

1
votes

I'm writing a "master" Excel document that creates new workbooks and saves them as ".csv" by using Visual Basic for Applications. The created workbooks contain measured data from two sources. One source already delivers the measure date in my regional settings (Holland, in which the comma , sign is commonly to used as decimal separator). The other source however delivers data with a dot . as separator (American settings). I'd like to replace all dot signs for comma signs.

Is this possible with the Replace function?

(I guess this solution would only fail when applying it on values greater than 1.000,00 (or 1,000.00 for US settings). Which would be acceptable but far from perfect.

Attempts so far

  • Application.DecimalSeperator = ","
  • Add, local = true after the line where the workbook is saved

In Addition

Excel thinks the values from the text document have 1000 separators 31.435. When i turn of the 1000-separator the value is 31435. I need it to be 31,435 (decimal Separated by comma , sign)

Do I need to adapt the cell format?
If so, then... How?
Any other options to solve this?

Anwser/Sollution(that works best for me)

Sheets(SheetNaamCNC).Range("B1").Select
Do While ActiveCell.Value <> ""
    If InStr(ActiveCell.Value, "0,") = 0 Then
        ActiveCell.Value = ActiveCell.Value / 1000
    End If
    ActiveCell.Offset(1, 0).Select
Loop
4
vbaexcelcsvdecimal
How does the data get from the 'sources' into your spreadsheet? It might be easier to fix the format at this step rather than trying to untangle how Excel has misinterpreted it afterwards. – nekomatic
Source One, Touche probe on a CNC milling machine that sends a text document to the laptop (or pc). my macro code extrat data from it... but the milling machine can only deliver measure data with dots as separator. Source two, Digital Gauge Caliper measure device. connected with a cable to the laptop. It enters data into one of the sheets of the master file, and gives me values with the decimal separated bij , (so works fine) – Cornelis

4 Answers

2
votes

You could use a function like below

Sub sub1()
    Application.DecimalSeparator = "."
    Application.ThousandsSeparator = ","
    Application.UseSystemSeparators = False
    Plan1.Columns(1).NumberFormat = "#,##0.00"
End Sub
2
votes

When you read a text file in to an Excel sheet using the Data tab > Get External Data > From Text, you can specify the decimal point and thousands separator by clicking the Advanced button in step 3 of the text file import wizard.

enter image description here

If you record a macro while you import one of your data files, you'll get something like this: 

With ActiveSheet.QueryTables.Add(Connection:= _
    "TEXT;myfilename.csv" _
    , Destination:=Range("$A$1"))
    .Name = "myfilename"
    .FieldNames = True
    .RowNumbers = False
    .FillAdjacentFormulas = False
    .PreserveFormatting = True
    .RefreshOnFileOpen = False
    .RefreshStyle = xlInsertDeleteCells
    .SavePassword = False
    .SaveData = True
    .AdjustColumnWidth = True
    .RefreshPeriod = 0
    .TextFilePromptOnRefresh = False
    .TextFilePlatform = 850
    .TextFileStartRow = 1
    .TextFileParseType = xlDelimited
    .TextFileTextQualifier = xlTextQualifierDoubleQuote
    .TextFileConsecutiveDelimiter = False
    .TextFileTabDelimiter = False
    .TextFileSemicolonDelimiter = False
    .TextFileCommaDelimiter = True
    .TextFileSpaceDelimiter = False
    .TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1)
    .TextFileDecimalSeparator = ","
    .TextFileThousandsSeparator = " "
    .Refresh BackgroundQuery:=False
End With

and you can then use the recorded code to develop your own macro that will import future data files in the correct format - see in particular the TextFileDecimalSeparator and TextFileThousandsSeparator properties.

1
votes

If it's reading them in as text, you could try

=SUBSTITUTE(SUBSTITUTE(SUBSTITUTE(sheet1!A2,",","|"),".",","),"|",".")

If I was doing it this way I would enter this formula in the same column of a new sheet for every column I wanted to re-format, and just copy any other columns using

=sheet1!B2 etc.

It depends how many columns you have whether this would be a workable solution.

0
votes

Already accived to set , as separator. This works for Values like 0,234 but values like 31,345 will fail. They are displayed as 31.345 (with in this case the . as thousand separator). Possible Sollution Loop through cells one by one. If first charachter does not equals "0" then devide ActiveCell.Value by 1000. Kind of a detour... but I guess it will work for me.

Add the following, 

Sheets("YourSheetName").Range("TopOfRangeYouLikeToEdit").Select
Do While ActiveCell.Value <> ""
    If Left(ActiveCell.Value, 1) <> "0" Then
        ActiveCell.Value = ActiveCell.Value / 1000
    End if
    ActiveCell.Offset(1,0).Select
Loop

-Edit by Cornelis: Instead of Left(ActiveCell.Value,1)<> "0" 

If ActiveCell.Value < 0 Then

would be better