Open CSV with VBA, convert to ANSI and a cell "75 %" is changed to format type percent and the value is changed to "0.75"

[Tanquen]

Right now I'm opening the CSV file in Notepad just to save it with ANSI encoding so the "°F" is not changed to "°F". It would be nice if there was a way to do this when bringing the CSV in with VBA.

Also, the CSV includes the units with the value in one cell. I have script to split them up but Excels auto format removes the percent units "%" and changed the value to "0.75" vs. "75".

Some something like "480 V" is left alone while "75 %" has text removed and the value changed. Is there no way to stop Excel from change values in cells?

Sub Import_BMS_CSV()
    Dim ws As Worksheet
    Dim csvFilePath As Variant
    Dim lastRow As Long
    Dim rngF As Range, rngH As Range
    Dim cell As Range
    Dim fileTimestamp As Date
    Dim formattedTimestamp As String

    ' Set the worksheet where you want to paste the data
    Set ws = ThisWorkbook.Sheets("BMS")

    ' Prompt the user to select the CSV file
    csvFilePath = Application.GetOpenFilename(FileFilter:="CSV Files (*.csv), *.csv", Title:="Select CSV File")

    ' Check if the user selected a file or canceled the dialog
    If csvFilePath = False Then
        MsgBox "No file selected. Import canceled.", vbExclamation
        Exit Sub
    End If

    ' Get the file timestamp
    fileTimestamp = FileDateTime(csvFilePath)

    ' Round the timestamp to the nearest minute
    fileTimestamp = Round(fileTimestamp * 1440, 0) / 1440

    ' Format the timestamp to "2024-08-06T15:50:00.000Z"
    formattedTimestamp = Format(fileTimestamp, "yyyy-mm-dd\THH:MM:SS") & ".000Z"

    ' Place the formatted timestamp in cell J2
    ws.Range("J2").Value = formattedTimestamp


    ' Define the range to clear (A9:O and beyond)
    ws.Range("A9:O" & ws.Cells(ws.Rows.Count, "A").End(xlUp).Row).ClearContents

    ' Import the CSV file starting at row 9
    With ws.QueryTables.Add(Connection:="TEXT;" & csvFilePath, Destination:=ws.Range("A9"))
        .TextFileConsecutiveDelimiter = False
        .TextFileTabDelimiter = False
        .TextFileSemicolonDelimiter = False
        .TextFileCommaDelimiter = True
        .TextFilePlatform = xlWindows
        .Refresh
    End With

    ' Make row 9 bold
    ws.Rows(9).Font.Bold = True

    ' Find the last row in the imported data
    lastRow = ws.Cells(ws.Rows.Count, "A").End(xlUp).Row

    ' Set the range for columns F and H from row 10 to the last row
    Set rngF = ws.Range("F10:F" & lastRow)
    Set rngH = ws.Range("H10:H" & lastRow)

    ' Loop through each cell in column F and replace "DCAIG" or "DCA" with "MCP"
    For Each cell In rngF
        If InStr(cell.Value, "DCAIG") > 0 Or InStr(cell.Value, "DCA") > 0 Then
            cell.Value = Replace(cell.Value, "DCAIG", "MCP")
            cell.Value = Replace(cell.Value, "DCA", "MCP")
        End If
    Next cell

    ' Loop through each cell in column H and replace "DCAIG" or "DCA" with "MCP"
    For Each cell In rngH
        If InStr(cell.Value, "DCAIG") > 0 Or InStr(cell.Value, "DCA") > 0 Then
            cell.Value = Replace(cell.Value, "DCAIG", "MCP")
            cell.Value = Replace(cell.Value, "DCA", "MCP")
        End If
    Next cell
End Sub

 

[Tanquen]

I've add this code at the end to repair the cells after import but it just makes a mess as now the next step will not find the value units in those cells and so on. I guess I could add it back in for these and then...

    ' Find the last row in column I
    lastRow = ws.Cells(ws.Rows.Count, "I").End(xlUp).Row
    
    ' Loop through each cell in column I
    For Each cell In ws.Range("I2:I" & lastRow) ' Adjust the starting row if needed
        If cell.NumberFormat = "0%" Or cell.NumberFormat = "0.00%" Then
            ' Convert the percentage to a number and remove the percentage format
            cell.Value = cell.Value * 100
            cell.NumberFormat = "General"
        End If
    Next cell