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
Improve this question
2
  • You can use F1,F2,F3 in place of field names if you have HDR=NO in the connection string. Commented Apr 2, 2023 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? Commented Apr 2, 2023 at 20:13
Related questions
0
Loop Through .FILE type files in folder to load into Excel
0
Importing various CSV-files with dynamic header names and dynamic amount of columns
2
In VBA replace formula output with text value
Load 4 more related questions Show fewer related questions

0

Reset to default

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.