Access encoding issues

[vegard_fv]

Hi everyone, I encountered an encoding problem never before experienced...

I'm importing a batch with textfiles to a table called "PAI", and so I've made a code that handles this (please see code below). I've made an import spec also called "PAI". Please see the attached picture, which illustrates my encoding problem:

When reviewing the data, the norwegian characters "Æ", "Ø" and "Å" lookes just fine. But after the import it appears with the character " � ".

Do any have experience with this problem and got a tip on how to solve it?



CODE:


Option Compare Database

Function Excelimport()
Dim myfile, mypath, strFile_Path As String
mypath = "C:\Users\vegard\Desktop\"
newpath = "C:\Users\vegard\Desktop\done"
Set fso = CreateObject("Scripting.FileSystemObject")


ChDir (mypath)
myfile = Dir(mypath)


DoCmd.SetWarnings False


Do While myfile <> ""


If myfile Like "*.txt*" Then
Debug.Print myfile

DoCmd.TransferText acImportFixed, "PAI", "PAI", mypath & myfile, False
fso.CopyFile (mypath & myfile), newpath, True
'fso.DeleteFile (myfile)
End If


myfile = Dir()


Loop


DoCmd.SetWarnings True
MsgBox "Ferdig!"


End Function

[jonh]

Hi everyone, I encountered an encoding problem never before experienced...

You big tease.

http://stackoverflow.com/questions/3344668/microsoft-access-transfertext-function-problem-with-codepage

[vegard_fv]

Haha never before experienced _ by me _ I should add :tongue2:

But I wish it was that easy as described in the post from stackoverflow. This is exactly what I have done. The attached picture is showing this. First pic on top is how the text looks like in the Import text wizard (you can see "ø" is viewed correctly). So I saved the import spec and called it "PAI", same as table name, and then calls it from my code just as described in the post.

That's why also I am so confused. When reviewing the data in the text import wizard after choosing the import spec with UTF, all norwegian characters is displayed perfectly. But after importing the data to the table, the " � " - character shows up again (like the table showed on the pic nr 2/beneath in the attachment).. I've tested this both with to import using the attached code, and by importing manually with the import spec.

In addition, I also tried to set the encoding in the VBA code using the codepage-property that comes with the docmd.transfertext-object (which I know was not recommended in the stackoverflow post).

Is there maybe something else I can do in VBA to set the encoding correctly?

I'll paste four rows of sampledata (looks fuzzy beneath but paste it in a notebook and you will see it's a fixed size setup), if that helps.


01060101600000012100Thorbjørnsen Ingrid 01080611922177963005254187963005328180400037517761000000000000000UU16010615 1201KAdjunkt m/tilleggutd 00000000000000000000000000000000000FAA0000000000000000000009400395419747663 37974565455000000000000000000000000000001062020106
01060101900000013610Sætre Christine Mari01031504139156572002736966572002801990211335504934000000000000000UU1601 06151201KAssistent 00000000000000000000001425060021005FAM0060563014250600000009400395419944849 23973872176000000020042400000000000000001062540106
01060102800000014220Strømshaug Ellen Wee01040906044647168004350007168004419001000037536825000000000000000UU16010 6151201KSosialkonsulent 00000000000000000000000000000000000FAA0000000000000000000009400395419944849 23993393851000000000000000000000000000001352760106
01060000002345914300Aså Awaz Salih 01080807931996572003689996572003824001000037531867000000000000000UU16010615 1201KAssistent(barnehage) 00000000000000000000000000000000000FAA0000000000000000000009400395419747663 37988025372000000060066000000000000000001062110106

[jonh]

I imported the file and everything seems fine.

Try a manual import and let access create a new table for you.
If it works compare fields.

[vegard_fv]

I imported the file and everything seems fine.

Try a manual import and let access create a new table for you.
If it works compare fields.

Yeah you're right, it works for me to with a manual import. It also works when I'm using the import spec manually. It's just so strange, it should be the exact same thing when I'm calling it from VBA.

My only problem is that I have like 350 txt file that is going to be imported. So if I don't find the solution, maybe a way could be to loop through all the files first and make one large, and import this one manually.

Thanx for taking the time! :)

[vegard_fv]

Just for the fun of it (and if it may help others), this is how I coded the batch processing of taking 350 txt files into one big, nice worksheet in excel. Saved me for 3-4 days of hell :devil2:


Option Base 1
Option Compare Text

Public ArrayString, NewTxtArr


Sub Batchprocess_TxtFiles()
Dim myfile, mypath As String
Dim wb As Workbook
Dim newpath
Dim fso

Set fso = CreateObject("Scripting.FileSystemObject")
Application.ScreenUpdating = False
Application.DisplayAlerts = False

'specify where the txt-files is
mypath = "C:\Users\vegard\Desktop\"

'specify where you would like to move the txt-files after it's processed - which could be a smart thing if the batch processing should fail somewhere
newpath = "C:\Users\vegard\Desktop\finished\"

ChDir (mypath)
myfile = Dir(mypath, myfile)

'Starts a loop that runs until every file in folder is processed
Do While myfile <> ""

'Calls the sub-routine that reads and/or edits the files
PAI_edit myfile, mypath, newpath

'write the txt-file to the workbook
numberof_rows_array = UBound(NewTxtArr)
lastrow = Cells(Rows.Count, "B").End(xlUp).Row
nextrow = lastrow + 1

'log which file you are working with in column A
Cells(nextrow, 1) = myfile

'loop the data into column B
For counter = LBound(NewTxtArr) To UBound(NewTxtArr)
Cells(nextrow, 2).Value = NewTxtArr(counter)
nextrow = nextrow + 1
Next counter

Workbooks(1).Save

'reset NewTxtArr and ArrayString to make it ready for next file without any fuzz or complaining!
Erase NewTxtArr
ArrayString = ""

'move the text file to the finished folder
fso.CopyFile (mypath & myfile), newpath, True
fso.DeleteFile (myfile)

myfile = Dir()
Loop

End Sub

Sub PAI_edit(myfile, mypath, newpath)

Dim MyData As String, StrData() As String
knr = Left(myfile, 4)

nyfil = newpath & knr & ".txt"

Dim objStream
Set objStream = CreateObject("ADODB.Stream")
objStream.Charset = "iso-8859-1"
objStream.Open
objStream.LoadFromFile (myfile)
Txt = objStream.ReadText()

NewTxtArr = Split(Txt, vbCrLf)

For counter = LBound(NewTxtArr) To UBound(NewTxtArr)
NyTxtArr(counter) = Left(NewTxtArr(counter), 266) & knr
Next counter

ArrayString = Join(NewTxtArr, vbNewLine)

End Sub