By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,972 Members | 1,092 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,972 IT Pros & Developers. It's quick & easy.

Problem with comma delimted file

P: n/a
My access table when exported to a comma delimited field gets all messed up
because some of the fields have
the double quote character - ". Used for measurements and other things. I
guess I will have to strip them out.
How can I replace the " for a blank space? I am having trouble doing it
with the replace command in a sql statement.

sqlstatement = "update table set table.[item] = replace(table.[item]," &
"""""""" & "," + """""" + ")"

this gives an error.

what to do?
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
On Mon, 26 Jan 2004 12:19:43 GMT, Jim's wife wrote:
My access table when exported to a comma delimited field gets all messed up
because some of the fields have
the double quote character - ". Used for measurements and other things. I
guess I will have to strip them out.
How can I replace the " for a blank space? I am having trouble doing it
with the replace command in a sql statement.

sqlstatement = "update table set table.[item] = replace(table.[item]," &
"""""""" & "," + """""" + ")"

this gives an error.

what to do?


How are you doing the exporting in the first place? Are the fields in
question of a non-number data type (ie. string)? If it thinks it is a
string, then it will put "" around the value by default. Access allows you
to use export specifications in the same way that the import feature does,
so you could specify what fields are numbers, and it should not place ""
around the data.
--
Mike Storr
veraccess.com
Nov 12 '05 #2

P: n/a
The field is all text and has measurements: 5 1/2" and quotes like tihs
"this is a test", so when access puts the " around each field, it gets
screwed up when it sees the above, and I cannot omit the quotes around the
fields because I have a few commas in the text too.

I need a sql string that will replace the " with a blank.

Thakns a bunch
"Mike Storr" <st******@sympatico.ca> wrote in message
news:19******************************@40tude.net.. .
On Mon, 26 Jan 2004 12:19:43 GMT, Jim's wife wrote:
My access table when exported to a comma delimited field gets all messed up because some of the fields have
the double quote character - ". Used for measurements and other things. I guess I will have to strip them out.
How can I replace the " for a blank space? I am having trouble doing it
with the replace command in a sql statement.

sqlstatement = "update table set table.[item] = replace(table.[item]," &
"""""""" & "," + """""" + ")"

this gives an error.

what to do?


How are you doing the exporting in the first place? Are the fields in
question of a non-number data type (ie. string)? If it thinks it is a
string, then it will put "" around the value by default. Access allows you
to use export specifications in the same way that the import feature does,
so you could specify what fields are numbers, and it should not place ""
around the data.
--
Mike Storr
veraccess.com

Nov 12 '05 #3

P: n/a
On Mon, 26 Jan 2004 15:18:48 GMT, Jim's wife wrote:
The field is all text and has measurements: 5 1/2" and quotes like tihs
"this is a test", so when access puts the " around each field, it gets
screwed up when it sees the above, and I cannot omit the quotes around the
fields because I have a few commas in the text too.

I need a sql string that will replace the " with a blank.

Thakns a bunch
"Mike Storr" <st******@sympatico.ca> wrote in message
news:19******************************@40tude.net.. .
On Mon, 26 Jan 2004 12:19:43 GMT, Jim's wife wrote:
My access table when exported to a comma delimited field gets all messed up because some of the fields have
the double quote character - ". Used for measurements and other things. I guess I will have to strip them out.
How can I replace the " for a blank space? I am having trouble doing it
with the replace command in a sql statement.

sqlstatement = "update table set table.[item] = replace(table.[item]," &
"""""""" & "," + """""" + ")"

this gives an error.

what to do?


How are you doing the exporting in the first place? Are the fields in
question of a non-number data type (ie. string)? If it thinks it is a
string, then it will put "" around the value by default. Access allows you
to use export specifications in the same way that the import feature does,
so you could specify what fields are numbers, and it should not place ""
around the data.
--
Mike Storr
veraccess.com


OK, what is "screwed up" about it. In the csv or txt file you create, ""
indicate empty string fields (you probably have AllowZeroLength set to Yes
on these fields in your table), this is normal, and not really a problem
unless you try to import them to something that does not allow empty
strings. I beleive if you actually want to make them Null, then create an
Update query that sets the desired fields to NULL if they are "" (empty).
--
Mike Storr
veraccess.com
Nov 12 '05 #4

P: n/a
On Jan 26 2004, 07:19 am, "Jim's wife" <ta*****@mindspring.com> wrote in
news:zB********************@news4.srv.hcvlny.cv.ne t:
My access table when exported to a comma delimited field gets all
messed up because some of the fields have
the double quote character - ". Used for measurements and other
things. I guess I will have to strip them out.
How can I replace the " for a blank space?


You can try TextExport class from
http://www.users.cloud9.net/~dfurman/code.htm instead of using
TransferText. It will allow you to specify a string that will replace field
delimiters found in the exported data, so you could replace commas with
something else. Another option is to use a different field delimiter, such
as Tab.

--
(remove a 9 to reply by email)
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.