0

I'm trying to extract some value from *.csv .The file looks like this:

enter image description here

with a following code I get a recordset

Function getCN_Ace16_csv(ByVal dbPath As String) As Object
    Set getCN_Ace16_csv = CreateObject("ADODB.Connection")
    getCN_Ace16_csv.connectionString = "Provider=Microsoft.ACE.OLEDB.16.0;Data Source=" & dbPath _
                                    & "; Extended Properties=""text;HDR=Yes;FMT=Delimited"""
End Function

Function foo (ByRef report as Object) as Boolean
    Set myConn = CN.getCN_Ace16_csv(report.ParentFolder.path)
    myConn.Open
    Set RS = myConn.Execute("SELECT * FROM [" & report.Name & "]")
End Function

When I get the recordset I don't see header names

For the first field a name is "п»ï" instead of "Product Details"

enter image description here

For all other "F2","F3" etc.

enter image description here

Have no idea how to extract info with a correct fields.Name Thank you for any help.

I don't want to use textstream - file is not big 10 - 20 rows, but it's a lot of code for a correct parsing.

Mik
  • 2,099
  • 1
  • 5
  • 19
  • Could be something about encoding, maybe [here](https://stackoverflow.com/questions/64573522/fix-bom-issues-when-reading-utf-8-encoded-csvs-with-vba-%C3%AF) there is something that may help. – Oran G. Utan Aug 14 '23 at 19:46
  • If it's not a large file it would be fast to open the file in excel (if that's where you're wrking) – Tim Williams Aug 14 '23 at 20:11
  • @OranG.Utan thank you for the link. Seems very close to my issue, but the problem is that query doesn't see fields name there no abracadabra it's shown simply like F2,F3,F4 lke there no any header in the file. CSV also is a UTF-8 with BOM – Mik Aug 15 '23 at 13:27
  • @TimWilliams, thank you for your reply. There are 2 problems Frist - there are too many files like that and I want to automate the process. Second - its UTF-8 with BOM and while opening it's puts 1 line - one cell. Spliting also a problem - there are commas in values. – Mik Aug 15 '23 at 13:33
  • Your values with commas are quoted though, so that shouldn't be a problem for Excel to handle. – Tim Williams Aug 15 '23 at 15:26
  • Have you looked through questions about BOM and UTF-8 like [this one](https://stackoverflow.com/questions/31435662/vba-save-a-file-with-utf-8-without-bom) or [this](https://stackoverflow.com/questions/54330347/vba-function-to-convert-csv-to-utf-8-without-bom) or [this](https://stackoverflow.com/questions/54330347/vba-function-to-convert-csv-to-utf-8-without-bom) or, finally, [this](https://stackoverflow.com/questions/50235161/strange-characters-in-string-when-importing-sql-file-via-vba) one for example? – Oran G. Utan Aug 16 '23 at 12:03
  • @OranG.Utan, thank you much for links. As I understand the problem, the only solution for my case is converting UTF-8 with BOM to an other charset. – Mik Aug 17 '23 at 12:05

0 Answers0