vba - Character changes when importing to Access and exporting to Excel -


i'm working on access database in import csv files converted xls works, 1 file has fields characters change within field after being imported access

for example: dash changes û
beginning double quote changes ô
end double quote changes ö

from have read has 7 or 8 bit character codes.. not understand.

my questions are, there way prevent character change or there better i've tried already? or there potential problems haven't come across seems work in example below?

here's i've tried far seems work

from original excel file save unicode text file (something new me) 

    activeworkbook.saveas filename:= _  "d:\newfiles\reportlist.txt", fileformat:=xlunicodetext _      , createbackup:=false

then import database following code 

    docmd.transfertext acimportdelim, "reportlist  import specification", "tbl_reportlist", "d:\newfiles\reportlist.txt", true

this seems import text database correctly.

other people work data , export new report access excel. changes font ms sans serif , changes characters again not same changes when imported. after excel report exported, , change font arial characters correct again.... @ least far.

i haven't run character change in past , solution seems work, i'm not sure if there other potential problems or if there's missed. haven't found answer specific question yet.

thanks taking time this.

here method have used in past circumvent character encoding issues.

i suspect method should work between excel , access -- although access not familiar with.

this sub specifies file's full name & path, , destination new filename & path. these same if want overwrite existing.

note on few simple tests, can't read file saved "unicode" excel, works on files saved "tab delimited txt" files , csv/comma-separated files, too.

sub openandsavetxtutf8() dim txtfilename string dim newtxtfilename string  txtfilename = "d:\newfiles\reportlist.txt" newtxtfilename = "d:\newfiles\utf8_reportlist.txt"  writeutf8(readtextfile(txtfilename), newtxtfilename)  end sub

this sub calls on 2 functions borrowed sources credited in code comments. writeutf8 creates proper utf8 file contents of readtextfile returns string of full file contents. 

function readtextfile(sfilename string) string 'http://www.vbaexpress.com/kb/getarticle.php?kb_id=699     dim ifile integer      on local error resume next      ' \\ use freefile supply file number not in use     ifile = freefile       ' \\ ' open file input.     open sfilename input #ifile       ' \\ return (read) whole content of file function     readtextfile = input$(lof(ifile), ifile)      close #ifile     on error goto 0 end function

this function requires reference adodb library, or, can dim objstream object , code should still work you.

function writeutf8(textstring$, myfileout$) 'modified http://www.vbaexpress.com/forum/showthread.php?t=42375 'david zemens - february 12, 2013  'requires reference adodb?       ' utf8()  version 1.00      ' open "plain" text file , save again in utf-8 encoding      ' (overwriting existing file without asking confirmation).      '      ' based on sample script jtmar:      ' http://bytes.com/groups/asp/52959-save-file-utf-8-format-asp-vbscript      '      ' written rob van der woude      ' http://www.robvanderwoude.com      dim objstream adodb.stream       ' valid charset values adodb.stream     const cdobig5 = "big5"     const cdoeuc_jp = "euc-jp"     const cdoeuc_kr = "euc-kr"     const cdogb2312 = "gb2312"     const cdoiso_2022_jp = "iso-2022-jp"     const cdoiso_2022_kr = "iso-2022-kr"     const cdoiso_8859_1 = "iso-8859-1"     const cdoiso_8859_2 = "iso-8859-2"     const cdoiso_8859_3 = "iso-8859-3"     const cdoiso_8859_4 = "iso-8859-4"     const cdoiso_8859_5 = "iso-8859-5"     const cdoiso_8859_6 = "iso-8859-6"     const cdoiso_8859_7 = "iso-8859-7"     const cdoiso_8859_8 = "iso-8859-8"     const cdoiso_8859_9 = "iso-8859-9"     const cdokoi8_r = "koi8-r"     const cdoshift_jis = "shift-jis"     const cdous_ascii = "us-ascii"     const cdoutf_7 = "utf-7"     const cdoutf_8 = "utf-8"       ' adodb.stream file i/o constants     const adtypebinary = 1     const adtypetext = 2     const adsavecreatenotexist = 1     const adsavecreateoverwrite = 2      on error resume next      set objstream = createobject("adodb.stream")     objstream.open     objstream.type = adtypetext     objstream.position = 0     objstream.charset = cdoutf_8   'we passing string write file, omit following line '    objstream.loadfromfile myfilein  'and instead of using loadfromfile writing directly copied ' text unsaved/temp instance of notepad.exe objstream.writetext textstring, 1      objstream.savetofile myfileout, adsavecreateoverwrite     objstream.close     set objstream = nothing      if err         writeutf8 = false     else         writeutf8 = true     end if      on error goto 0 end function

Comments

Post a Comment

Popular posts from this blog

jquery - How can I dynamically add a browser tab? -

since using iframes house external pages are, accounts "not preferred method" , disallowed sites (flickr, one), want dynamically generate browser tabs. e.g., when user submits form, want dynamically generate tab on browser in response submitted. e.g., might want add new tab url like: http://www.bigsurgarrapata.com/contact how in jquery? you want form target attribute action attribute. http://www.w3schools.com/tags/att_form_target.asp browsers have ultimate control on result of target attribute open new tab value of _blank. <form action="contact_submit.php" target="_blank" method="post">
Read more

keyboard - C++ GetAsyncKeyState alternative -

i using windows.h library's getasynckeystate function define key pushed on keyboard . here program returns int value of pushed key example. #include <iostream> #include <windows.h> using namespace std; int main (){ char i; for(i=8;i<190;i++){ if(getasynckeystate(i)== -32767){ cout<<int (i)<<endl; } } return 0; } but trying make opengl simple game , function appeared slow . there function not need cycle 180 times , if statement . thanks you're trying make opengl game, you're going use glfw. if that's case can away using glfwgetkey() such: if (glfwgetkey(glfw_key_up) == glfw_press) {...} if (glfwgetkey(glfw_key_down) == glfw_press) {...} that method nicely explained in tutorial 6 opengl-tutorial.org . should start first tutorial of beginners tutorials , later intermediate tutorials . if you're going make win32 window can listen wm_keydown or wm_keyup messages , virtua...
Read more

android - java.net.UnknownHostException(Unable to resolve host “URL”: No address associated with hostname) -

i developing app testing asp.net webservice in android.for simple webservice adding 2 numbers used.where numbers passed parameters.the result in dialogue.the webserver works in local. when application runs following result exception.i provided internet permission in manifest. java.net.unknownhostexception(unable resolve host “ http://url.com/ ”: no address associated hostname) my code given below main activity public static string rslt=""; /** called when activity first created. */ @override public void oncreate(bundle savedinstancestate) { super.oncreate(savedinstancestate); setcontentview(r.layout.activity_main); button b1=(button)findviewbyid(r.id.button1); final alertdialog ad=new alertdialog.builder(this).create(); b1.setonclicklistener(new onclicklistener() { @override public void onclick(view arg0) { // todo auto-generated method stub try { ...
Read more