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? 4 1601
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
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******@sympa tico.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
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******@sympa tico.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
On Jan 26 2004, 07:19 am, "Jim's wife" <ta*****@mindsp ring.com> wrote in
news:zB******** ************@ne ws4.srv.hcvlny. cv.net: 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) This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
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 it isn't always easy to comprehend for a newbie :)
The lines I want to split are like this:
(The following is one line, even if news client splits it up:)
|
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 need first name/
comma/ last name /carriage return. The first name comma last name work
fine. Can't get the carriage return.
Any suggestions would be helpful.
<?xml version="1.0"?>
<xsl:stylesheet version="1.0"
|
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 you might say it's better to split the array into distinct
elements. However, some of the elements will have so many integers
that the overhead of the extra XML tags will bloat the file. So back
|
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 but not for "AAA","BBB","CCC","DDD".
Here is an extract from the code which needs to be modified for the """:
Sub LoadTextFile(ByVal strFilePath As String)
Dim oDS As New DataSet()
Dim strFields As String
Dim oTable As New DataTable()
|
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
control. It outputs all text fields surrounded by quotes, and all numeric
fields w/o quotes. All fields are separated with commas.
This has been working for 2 years, until today, when one of the data fields
| |
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 protected by forms authentication.
When I create forms authentication at root level it works but when I move my
code up to the subfolder I get this error:
Server Error in '/TestProjects/FormsAuthenticationTestingArea' Application.
|
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 file that is continually going to have 3 temperatures appended
to it. I need to read that temperature in from the log file and display it to
the user.
The temperatures are seperated by commas. I was reading and is Streamreader
|
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;
"Error at Source for Row 27753. Errors encountered so far in this task:
1. Column Delimter not found."
I suspect the problem is that one record (and possibly more) has a PIPE
|
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.
Without <deny users="?" />, the Windows Login ID is not able to be
obtained, but reading the file info is successful.
Is there any way to make both working at the same time?
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |