0

I have this CSV: enter image description here

columns: "Replace Column 1" and "Replace Column 2" need to be replaced "," to "."

in addition, i need to open the file with UTF-8 encoding.

How do I open a CSV with this transform of the data? is it possible to do it without mentioning the Headers' names in the let formula in VBA (when I record a macro)?

Here is the macro I recorded:

Sub Macro3()
    ActiveWorkbook.Queries.Add Name:="ENG CSV", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""E:\ENG CSV.csv""),[Delimiter="","", Columns=4, Encoding=65001, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""Date"", type" & _
        " datetime}, {""Created By"", type text}, {""Replace Column 1"", type text}, {""Replace Column 2"", type text}})," & Chr(13) & "" & Chr(10) & "    #""Replaced Value"" = Table.ReplaceValue(#""Changed Type"","","",""."",Replacer.ReplaceText,{""Replace Column 1"", ""Replace Column 2""})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Replaced Value"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""ENG CSV"";Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [ENG CSV]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "ENG_CSV"
        .Refresh BackgroundQuery:=False
    End With
End Sub
Kobe2424
  • 147
  • 7
  • You can use `F1,F2,F3` in place of field names if you have HDR=NO in the connection string. – CDP1802 Apr 02 '23 at 17:51
  • this is a new area for me so I really have no idea what are you talking about. I have a lot of experience in VBA though. I edited my question and added the macro I recorded. Any chance to change it to dynamic code? – Kobe2424 Apr 02 '23 at 20:13

0 Answers0