TransferText changes pound sign into accented character

[ScottT]

I exported an Excel 2003 spreadsheet to a tab-separated text file, and then wrote some code to import the text file into an Access table.

All goes fine except the fact that any pound (£) signs in the text are translated into the accented u symbol " ú "

I realise it may be a charset translation issue, but how do I make it work first time?

My Windows XP 64 Control Panel | Regional and Language settings are all set to United Kingdom, what else do I need to do to ensure proper import from text file -> database table?

 

[smig]

not sure how you imported it, but if you use the docmd.transfertext you can set the code page to be used.
why not using the docmd.transferspredsheet to import the excel file ?

 

[ScottT]

not sure how you imported it, but if you use the docmd.transfertext you can set the code page to be used.
why not using the docmd.transferspredsheet to import the excel file ?

I used TransferSpreadsheet at the start, but it had problems converting some fields.

I have read that TransferSpreadsheet scans the first 10 rows of the Excel spreadsheet and makes guesses as to the field datatypes. If the guessed datatypes don't match with the actual table's datatypes (and I have been using a static table with properly typed fields, not a temporary one) then an exception is raised. Not very good form tbh.

So I used TransferText with an options file, works fine for me.

I will check out the code page option but tbh should the system's code page not be the one adopted?