Removing characters from a linked table

[RichardMGreen]

Hi all

I have a CSV file linked to an Access table.
Unfortunately, the CSV file has quotation marks (") at either end of each field.

Is there a way of getting Access to ignore the quotation marks so instead of a field reading
"This is the field item"
you get
This is the field item.

I don't really want to have to open the file and do a find and replace manually if it can be done in VBA?
If it helps, I'm already using VBA to repoint the path of the linked table if necessary.
I'm also using code to run a series of macros to build some temporary tables.

If it could be included as part of that, or if anyone has any other ideas, I'd be very grateful.

::edit::

Just a quick add-on, if I open the CSV in Excel, I don't get the quotation marks around each field.
Anyone know why that is?

 

[RichardMGreen]

Hi all

Ignore this, I found it.
I needed to include the text qualifier as a " and that solved my problem.

 

[SydneyGeek]

Glad you found it -- Access is pretty fussy about such things, but I find the biggest hassle is with dates.
How big is this file? You may get better performance by importing the data. That gives you the added advantage that it's editable. And the first time you import it you can set up an import spec to deal with any tricky text / date formats.

Denis

 

[RichardMGreen]

There are 88 files in total, running to 2.8GB.
The linked tables are already set up, I was just adding an extra one (with a couple of issues that are now sorted).

 

[SydneyGeek]

Too big to import but you already knew that...

How does it perform?

Denis

 

[RichardMGreen]

Erm ..... slowly!
But there's nothing I can do about that. Thankfully most of the tables are indexed which helps. Because of the data layout (which I can't changed) each query has to link through several tables for each piece of information (it's in a fairly normalised format).

 

[SydneyGeek]

Have you considered putting the data into a SQL Server Express back end and using Access to do the analysis? You can install SSE on a standard Windows desktop.

SSE has a capacity of 10GB and the performance should be a lot better.

Denis

 

[RichardMGreen]

I did, but our IT department had a bit of a fit when I suggested it.
Apparently it would cause the server to fall over when it was doing the upload.

 

[SydneyGeek]

And they're happy with you dimming the lights when you run the current setup? The network traffic would be fairly huge...

Denis

 

[RichardMGreen]

They seem to be.
They didn't even blink when I downloaded the 330MB zipped file and unzipped it to 2.8GB on the network.