473,320 Members | 1,832 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Problem with comma delimted file

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
4 1577
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
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Hans Almåsbakk | last post by:
Hi, I have a problem which I believe is seen before: Finding the correct pattern to use, in order to split a line correctly, using the split function in the re module. I'm new to regexp, and...
2
by: judy | last post by:
I have an xml file containing a series of names, first name and last name. My desired outcome is to create an xsl file that will generate a comma delimitated text file containing these names. I...
2
by: scott.ballard | last post by:
Greetings, I would like to know if it is possible in an XML Schema to specify an element that contains comma-delimted integers? For example, <element>1,2,3,4,5,6,7,8,9</element> Now I know...
6
by: Skc | last post by:
I am trying to import a file using a custom VB.net procedure, but the problem is it works on a file with pure comma separation and not inverted commas and commas, i.e. it works for AAA,BBB,CCC,DDD...
1
by: DCM Fan | last post by:
Access 2K, SP3 on Windows 2K, SP4 All, I have an import spec set up with quoted Identifiers and comma-separated values. The text file is produced by a 3rd-party program of which I have no...
3
by: Kris van der Mast | last post by:
Hi, I've created a little site for my sports club. In the root folder there are pages that are viewable by every anonymous user but at a certain subfolder my administration pages should be...
2
by: JR | last post by:
I have tried searching boards but have not been able to find an answer. What is the best way to display text from a log.txt file and then display it in three seperate text boxes? I have a log...
3
by: chudson007 | last post by:
Can somebody help me with a delimiter problem I have. I have several PIPE (|) delimted text files which I need to import to SQL. With one of the files I keep encountering the following error;...
0
by: genzy | last post by:
I'm facing the below problem. With <deny users="?" />, the Windows Login ID is able to be obtained to fill up the Windows IDSID text field automatically, but reading the file info is failed. ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.