September 27, 2005 at 8:01 am
Hi everyone,
I have quite a simple question. I am attempting to link an Access table to a SQL server table (through ODBC). It works fine, however, when I open the "table view" in Access to look at the data, each record (it's a one-field table) has a junk character at the end of it. The junk character is a hollow square. When I open the "table view" to look at the data in SQL Server, this junk character does not appear.
All the tables that I currently have in my SQL Server database originated as text files - so I had to use the BCP command to get them in SQL Server. However, this is the only one that gives me this junk character when linking to the SQL Server table in Access.
Has anyone had this kind of problem before? Does anyone have any explanation for this? Any help would be greatly appreciated!
thanks in advance
September 27, 2005 at 2:07 pm
That square could be a half line feed.
Are the textfile originating from mac or unix systems?
If you open the textfile with notepad,do you see the squares too?
September 28, 2005 at 9:38 am
I have found a similar issue when importing data from other sources such as FileMaker Pro. The people that maintain/use the FileMaker database occasionally insert a hard return which puts in something like: "$6.95Free". I presume this is the square you are talking about. Go back to your source data and check to see what it looks like prior to being pulled into SQL. BTW, for me the character also shows in SQL, not just through the ODBC link in Access.
September 28, 2005 at 10:16 am
I have to work a lot with "End Of Line" characters that normally are:
CR(carriage return) (ASCII 13), LF (line feed or new line ) (ASCII 10) or CRLF (both).
Everything depends on what tool you use and what export / import you do. I just did a demo last week for someone by creating files of 3 records by exporting SQL Server table by DTS Wizard and there are choices of what to use: CR, LF or CRLF. My demo also contained export by VBscript where you can explicitly specify the character. You can also specify the character when using TSQL. My point was to show that almost all of above looks the same as this little square or just nothing, but import tools are very sensitive to it.
I created a short VBscript file to get the characters off the text files and I wonder if it can help you to find out what is in your text:
Set objFSO = CreateObject("Scripting.FileSystemObject")
Set objFile = objFSO.OpenTextFile("mytextfile.txt", 1)
Do Until objFile.AtEndOfStream
strCharacters = objFile.Read(1)
Wscript.Echo strCharacters & " " & Cstr(asc(strCharacters))
Loop
It will show numbers for characters in message boxes. If you see just 10 or 13 without a character, it is your end of line. To stop the program you can "End Process Tree" in Task manager but better modify a script to give you an opportunity to end the program or to output to another file.
Regards,Yelena Varsha
September 28, 2005 at 1:33 pm
Hi everyone,
Thank you for all your help. After much trying, it turned out the problem was somewhere in Bulk Copy command which I had to try and retry many times over. It finally worked, unfortunately however, I was unable to find out what the problem was. I think it had to do with my field terminator character (it must have atleast!) in the format file.
Thanks a lot for all your efforts everyone!
It is much appreciated!
Good luck!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply