PDA

View Full Version : Select CSV file and Import - UTF-8



Dancul
03-05-2019, 03:52 AM
Hello


I'm stuck for longer time, I can not figure out how to open csv in dialog window and import data from csv. The problem is that I need to import in utf-8 format ...


So I have code which open file dialog window and allow me select csv file. Then this code return path and file name of selected file. Let say it place it in Worksheet "CSV" in range A1 and A2.


I have got code from record function which properly import csv. But I can not figure out how to connect with first code. That it will automatically open csv which path and name are in A1 and A2...




Sub reccsv()
'
' reccsv Makro
'


'
ActiveWorkbook.Queries.Add Name:="ninja-forms-submission (3)", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Zdroj = Csv.Document(File.Contents(""C:\SystemIT\csv\ninja-forms-submission.csv""),[Delimiter="","", Columns=10, Encoding=65001, QuoteStyle=QuoteStyle.Csv])," & Chr(13) & "" & Chr(10) & " #""Záhlaví se zvýšenou úrovní"" = Table.PromoteHeaders(Zdroj, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & " #""Změněný typ"" = Table.TransformColumnTypes(#""Záhlaví se zvýšenou úrovní"",{{""#"", Int64.Type}, {" & _
"""Date Submitted"", type date}, {""Vaše jméno"", type text}, {""Váš Email"", type text}, {""Telefonní kontakt"", type text}, {""Pir/pur desky"", type text}, {""Text Zprávy"", type text}, {""Tlouška izolace [mm]"", Int64.Type}, {""Množství [m2]"", Int64.Type}, {""PSČ nebo Místo dodání "", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Změněný typ"""
ActiveWorkbook.Worksheets.Add

With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=""ninja-forms-submission (3)"";Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [ninja-forms-submission (3)]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "ninja_forms_submission__3"
.Refresh BackgroundQuery:=False
End With
Range("D16").Select
End Sub

大灰狼1976
03-05-2019, 10:51 PM
Hi Dancul!
something like below:

Sub Macro1()
With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;" & [a1] & [a2], Destination:=Range("$A$3"))
.TextFilePlatform = 65001 'UTF-8 format
.TextFileCommaDelimiter = True
.Refresh BackgroundQuery:=True
End With
End Sub

Dancul
03-06-2019, 08:47 AM
Many thanks, so simple code...

I would like ask you one more question.
This I trying to import data from web form. And customers used to hit Enter when writing message or add comma and it make problems then working with the imported data. So I suppose that ListObjects can solve this?

I'll try to google for some useful code for learning. Just maybe if you know how to properly connect ListObjects to this code. I mean how the second part of my code should start:


With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _

大灰狼1976
03-06-2019, 10:28 PM
I'm sorry, I'm afraid I can't help you, because I have no experience in web form operation.

Dancul
03-07-2019, 01:00 PM
Ok, many thanks for the code :)