Using apostrophes in Access

[Aaron Howe]

Could someone answer a question on exporting from Access
to Excel and retaining an apostrophe as a hard line end?
I would have posted this in the Excel forum but really it
sits in both camps.

I have a query which spits out a .csv file for use in
another system. The system receiving the file has to know
where each line ends - to do that it makes use of an
apostrophe.

I am able to get my query to output an apostrophe at the
end of every completed line, but the CSV file doesn't
entirely format it correctly. Excel as I understand it
will only recognise the apostrophe as an "invisible
character" where it has been entered directly into a cell
(or where I have edited the cell and hit enter).

Access doesn't seem to have an output format to do that -
to make Excel recognise the character as a line stop and
not as a literal character. I hope that makes sense.

With all the other things I have discovered Access is
capable of, have I finally hit the limit of its abilities?

 

[Douglas J. Steele]

You seem to be saying that Excel won't recognize the apostrophe at the end
of the cell from a csv file, in which case it strikes me as strictly an
Excel issue. If someone can tell you what needs to be in the csv file in
order for Excel to recognize it correctly, and Access doesn't generate that,
perhaps then we can help you.

 

[Aaron Howe]

I was worried you'd say that ;-)

The crux of the problem was that Excel won't recognise the
apostrophe as a "function" in Excel without the proper
formatting (what that formatting should be I don't know).
I had a feeling I wouldn't be able to do much with that
problem and I can live it, but if you don't ask you may
never know...!

Thanks for responding anyway Doug

 

[Andi Mayer]

Could someone answer a question on exporting from Access
to Excel and retaining an apostrophe as a hard line end?
I would have posted this in the Excel forum but really it
sits in both camps.

I have a query which spits out a .csv file for use in
another system. The system receiving the file has to know
where each line ends - to do that it makes use of an
apostrophe.

I am able to get my query to output an apostrophe at the
end of every completed line, but the CSV file doesn't
entirely format it correctly. Excel as I understand it
will only recognise the apostrophe as an "invisible
character" where it has been entered directly into a cell
(or where I have edited the cell and hit enter).

Access doesn't seem to have an output format to do that -
to make Excel recognise the character as a line stop and
not as a literal character. I hope that makes sense.

With all the other things I have discovered Access is
capable of, have I finally hit the limit of its abilities?

you can write your own csv format with:

dim tmp as string
dim rs as DAO.recordset
set rs=currentdb.openrecordset("MyQueryOrTableOr SQL_String")

open "C:\MyOutput.csv" for output as #1
while not rs.eof
tmp=""
for each fld in rs.fields
tmp=tmp &";" fld
next fld
tmp=mid(tmp,2) &"'"
print #1, tmp
rs.movenext
wend
close #1
rs.close
set rs=nothing

 

[Aaron Howe]

Thanks Andy, I'll give that a try!

-----Original Message-----
abilities?

you can write your own csv format with:

dim tmp as string
dim rs as DAO.recordset
set rs=currentdb.openrecordset("MyQueryOrTableOr SQL_String")

open "C:\MyOutput.csv" for output as #1
while not rs.eof
tmp=""
for each fld in rs.fields
tmp=tmp &";" fld
next fld
tmp=mid(tmp,2) &"'"
print #1, tmp
rs.movenext
wend
close #1
rs.close
set rs=nothing

word "manfred" to the first 10 lines in the message

 

[Gargamil]

The first question that springs to mind is why use the apostrophe?? A
typical CSV will look like data1,data2,data3EOLcharacter. To my mind the
vbCrLf (carriage return line feed) is the most obvious end of line character
since it works in both csv and excel AND you're never going to get it
confused with text.

Thats my 2.2 cents worth (GST incl)

g




Hello, Aaron!
You wrote on Wed, 12 Jan 2005 05:32:48 -0800:

AH> I have a query which spits out a .csv file for use in
AH> another system. The system receiving the file has to know
AH> where each line ends - to do that it makes use of an
AH> apostrophe.

AH> I am able to get my query to output an apostrophe at the
AH> end of every completed line, but the CSV file doesn't
AH> entirely format it correctly. Excel as I understand it
AH> will only recognise the apostrophe as an "invisible
AH> character" where it has been entered directly into a cell
AH> (or where I have edited the cell and hit enter).

AH> Access doesn't seem to have an output format to do that -
AH> to make Excel recognise the character as a line stop and
AH> not as a literal character. I hope that makes sense.


With best regards, Gargamil. E-mail: (e-mail address removed)