We help IT Professionals succeed at work.

Reading text files via ADO in VB6 - How to handle UTF files

FNDAdmin
FNDAdmin asked
on
I am using ADO to open text files as a recordset in VB6, but the files are not opening correctly - they won't read the column headers.  I believe the problem is the encoding/code page of the text file.  The files are supplied by a vendor so I can't control the encoding, but some are UTF-8, and I'm sure others will vary.  

Once I convert them to ANSI my code works correctly.  Two questions - what code page does ADO expect, and can I open the file specifying the codepage?  I'm using the following to open the file:
ImportConn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _
                       "Data Source=" & ImportDirectoryName & ";" & _
                       "Extended Properties=""text;HDR=YES;FMT=Delimited"""

Second, I found code that tells me what encoding the file uses, but I am having trouble finding code that will convert the file.  If I need to use ANSI, can someone tell me an efficient way in VB6 to convert the file so I can import it properly?
 
Comment
Watch Question

Martin Liss"There is still no cure for the common birthday." ~John Glenn
Most Valuable Expert 2017
Distinguished Expert 2018

Commented:
According to this example I found on the web you don't seem to have the Extended Properties and the rest after that 'punctuated' correctly.


connCSV.Open "Provider=Microsoft.Jet" _
        & ".OLEDB.4.0;Data Source=" & App.Path _
        & ";Extended Properties='text;HDR=Yes;" _
        & "FMT=Delimited'"

Author

Commented:
The file seems to read in ok as long as it is ansi, so I'm guessing the punctuation just isn't looking correct on the post.  The problem, from what I can tell, seems to be related to the code page on the file, though I will look at the punctuation more closely.  Thank you.
GrahamSkanRetired
Top Expert 2012

Commented:
The usual problem is that semi-colons and commas are special characters that are treated differently between regions.

UTF-8 is designed to be as compatible as possible with ASCII, so that could be a misdiagnosis of your difficulty.

Could you post a bit of the file (including the header), please?
Commented:
It appears the problem was indeed the format.  Once I used some really slow code to convert the text file to ANSI everything worked fine.  I was unable to find efficient code to convert the file, but what I did find works.  Thanks for you help.

Author

Commented:
Thanks for all your help, but the problem was with the format itself.