Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> SQL Server Data Access

MS Access Memo field to SQL Server text field

Hi all,

i've a reasonable amount of experience with MS Access and less

experience with SQL Server. I've just written an .NET application that

uses an SQL Server database. I need to collate lots of data from around

the company in the simplest way, that can then be loaded into the SQL

Server database.

I decided to collect the info in Excel because that's what most people

know best and is the quickest to use. The idea being i could just copy

and paste the records directly into the SQL Server database table (in

the same format) using the SQL Server Management Studio, for

example.

Trouble is, i have a problem with line feed characters. If an Excel

cell contains a chunk of text with line breaks (Chr(10) or Chr(13))

then the copy'n'paste doesn't work - only the text up to the first line

break is pasted into the SQL Server database cell. The rest is not

pasted for some reason.

I've tried with MS Access too, copying and pasting the contents of a

memo field into SQL Server database, but with exactly the same problem.

I've tried with 'text' or 'varchar' SQL Server database field formats.

Since i've no experience of using different types of databases

interacting together, can someone suggest the simplest way of

transferring the data without getting this problem with the line feeds?

I don't want to spend hours writing scripts/programs when it's just

this linefeed problem that is preventing the whole lot just being

cut'n'pasted in 5 seconds!

cheers

Dominic

[1612 byte] By [Dom_donald] at [2007-12-27]
«« Other Porgramming Languages
»» Problem creating NDISUIO handle
# 1

hi dominic,

To achieve that you have to do a dirty work

with the TSQL. you can do it with

Select... (from excel or whatever)

INSERT... (to SQL).

as you can plainly see, Carriege return and Linefeed are unprintable characters

regards,

joey

joeydj at 2007-9-3 > top of Msdn Tech,SQL Server,SQL Server Data Access...
# 2
ahh, see good old microsoft provide a tool for data migration from Access to SQL server

http://www.microsoft.com/sql/solutions/migration/default.mspx

This seems to do exactly what i want :-)

Dom_donald at 2007-9-3 > top of Msdn Tech,SQL Server,SQL Server Data Access...
# 3

Hi all. I ran across this post and this same problem has been driving me crazy for a week. I have a description column that apparently has hard returns in the text (MS SQL Server 2005). When I execute a query against the view, you can see two small box characters (special characters for hard returns) in the text. I am trying to export the data from the view to a tab delimited text file with each record ending in a carriage return one record per line. Getting it to even execute using the DTSWizard was an absolute pain because it didn’t like the character type definition with a Unicode file (I think it was DCHAR).Anyway, the only place I can see the special characters (2 boxes) is in SQL Server. I can't copy and paste them to do a find and replace on them and none of the find special character options are helping in Excel, Dreamweaver, Word, Notepad, etc.

Does anyone know of a way to add to my view a way to strip these characters out using an option in my select statement or some off-the-wall way to get these characters out of my flat file? I saw the TSQL post, but that's not helping much.

Any help would be greatly appreciated. Thanks!!!

LandenDavis at 2007-9-3 > top of Msdn Tech,SQL Server,SQL Server Data Access...
# 4

Just ran across this. Use embedded replace fuctions to strip carriage return , new line, etc.

select replace(replace(replace(YourTextField,char(9),' '),char(10),' '),char(13),' ')

Fox4 at 2007-9-3 > top of Msdn Tech,SQL Server,SQL Server Data Access...
SQL Server
SQL Server Data Access

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified