How to edit a text file from access

[martinainscough]

Hi all

I have created a text file from an access query. This contains csv information. However for me to upload this to a program i need to get rid of the quotation marks that are present in the file so for eaxmple "ABC" would become ABC.

Is there any way that i could create some code to access the file and delete all these quotation marks automatically as i would like to integrate this into the procedure rather than do this manually.

Your help would be much appreciated

Thanks

Martin

 

[The_Doc_Man]

Welcome to the world of text parsing. You need to look at the following keyword help topics:

Verbs

Open (a file)
Close (a file)
Input (a record/line)
Write (a record/line) or Print (a record/line)
Name As - as a way to rename a file.
Kill - as a way to remove obsoleted files.

Functions

InStr() - as a way to find quotes.
Left() and Right() and Mid() - as ways to take things out of strings.
Chr() - as a way to convert a code number to a character
ASC() - as a way to convert a character to a code number

Constants

vbCRLF, vbCR, vbLF, etc. - short-cuts built into VBA so you don't have to remember the stupid code numbers all the time for common things like carriage returns, line feeds, etc. etc.

Applications

FindFile (or is it FileFind... never can remember when shooting from the hip) - as a way to find a file to be modified.

Also, you could search this forum for the topic "OpenFile dialog box" or "Common Dialog box" as a way to browse for your file through VBA.

To launch your function:

You can use a wizard to make a control button on a form. It can do anything you want in two phases. First, pick a function for the control button. Almost doesn't matter what. Next, once the code under the button has been built, go into the OnClick routine for that button in the form's class module. Edit the code to remove the stuff you didn't want. (Leave in any error processing that it might do - as a safety issue.) Substitute your own code, which might do something so simple as call a public subroutine you wrote in a general module after looking up those earlier topics.

It is not wise to attempt to edit a text file in-place. But you could open a temporary file and start reading your desired file. For each line in the file, look for your quoted strings. Use Mid to copy the parts of the line that AREN'T quote characters into a new record. Write the record.

When you reach EndOfFile condition on the file (which you will probably have to write an OnError routine to trap), close the original file and the temp file, then do some renaming and deleting.

While debugging this routine, work with trash copies of the original file or make some good backup copies, 'cause odds are you won't get it right the first time. Parsing isn't a trivial problem even for a simple parse such as you describe.

 

[Mile-O]

Also, I also posted an example recently in the Modules & VBA forum if you have a look for it.

 

[martinainscough]

thanks for the suggestions guys but i have no idea how to go about this.

I know now that I need to open the file to edit it. (intra.txt)
is there not a find and replace type code to look for all the quotation marks and then delete them and then re save the file.

Thanks for your patience

Martin

 

[islman1975]

I'm trying to do something similar.

Any help on how to go about the following would be appriciated.

http://www.access-programmers.co.uk/forums/showthread.php?t=78533

 

[The_Doc_Man]

OK, the other way to do this requires you to open something as an external application. Word will do this but it is notoriously cranky.

For THIS approach, you will need to look up the following topics

In Access, creating an application (and the help files specifically include an example or two on doing exactly that step).

In Word, you need to open the help files on "Using VBA" and browse the heck out of the topic. You want to find the Paragraphs collection and get a list of the methods available under that collection. One of them will be a Find method. (Or maybe FindText)

Basically, using THIS approach, from a VBA routine you would ...

Create a Word application

Open the targeted file from within that application.

Specify a find and replace operation as a method of the paragraphs collection (I think...) I forget whether the .Find automagically has a replace option or whether .FindAndReplace is a separate method of Paragraphs.

When you have done the last find and replace, save the file and close it. At this point you must be EXTREMELY CAREFUL in what you specify as a file format. If you get it wrong, it won't be text anymore. (Gee, Toto - I don't think we're in Kansas any more...)

Close the application.

You're done.

Failure to close the app or the file will result in sloppy shutdowns and an eventual "out of memory" error in something (not necessarily Access).

Now, you will note that I have not given you the code for this. I am not at the place where I did that a couple of years ago and I'm running from memory. My notes are packed away at the moment. So while I have actually done a more complex version of this that included statistical analysis of certain factors in the file, I'm not about to give you half-remembered detail-level code snippets.

The reason this is so much trouble is that you are working from the wrong end of the control chain. To do a find-and-replace as a monolithic action via programming, you MUST work in an editor that exposes its components via the Component Object Model (COM) - or whatever term Bill Gates likes for this concept these days. This used to be called ActiveX, though there is more to this term than just pushing the buttons on another application. Which is why I cannot just tell you to run Notepad. I don't think Notepad exposes itself this way. Not sure, but Wordpad might not, either. Which leaves you with Word, the crankiest word processor in existence.

The thing that bothers me about this approach is that it would be FAR easier, even for a small bunch of files, to just do this by hand. This task is so complex to achieve such a simple result that I doubt it would pay off unless you had literally HUNDREDS of files to do this way.

The other method I suggested earlier is easier - using VBA to pass the characters of a file one character at a time UNLESS the character is one of the quoting characters. You are obviously not very experienced with text management in order to even ask the question. Well, I've been doing this and other types of text manipulation for a little over 29 years, off and on. What you asked is not as east to do as it is to say. Not by a long shot. And you are choosing the most complex method by wanting to do a find and replace through a program interface. Trust me, you would do better choosing any other method first.

Oh the other hand, I would do you a disservice if I didn't at least point the way, which I have done. And you DO need to gain experience. You know what "experience" is, don't you? It is the ability to recognize your mistakes when you make them again. (with apologies to Ambrose Bierce...)

 

[reclusivemonkey]

I used Perl to remove "=" from every line of a CSV file. It went like this:

#!/usr/bin/perl
use warnings;

# Ask for the csv file to be checked
my $csvfile = "dump.csv";

# Open the file
open FILE, $csvfile or die "Cannot read '$csvfile': $!";

# Substitute = with null to remove the problem when importing into access.
while (<FILE>)
{
  s/=//;
  print $_;
}
# Close the file
close FILE;

If you are going to do a lot of text manipulation I would look at Perl, or at least something more suited to the task than Access.

 

[The_Doc_Man]

Damn it all, reclusive, you are flat out right. You are SO right it hurts to think about how right you are - and that's its own form of tragedy. Here we are, two Access users, sittin' on the Access form saying that ANYTHING ELSE BUT ACCESS is probably better suited for this type of task.

If Martin has PERL available or any other scripting language that includes file operational capabilities, that's the way to go.

I've checked. Neither Wordpad nor Notepad will do this for you. There is no command line interface for either one (that is documented, anyway.) The topic of "command lines" is conspicuously absent from Word Help, too. Which means a scripted solution using any of these three editors is SOL. (Sh|t out of luck.) I also tried "keyboard scripts" - which IS in the help files for WordPerfect if you've got it.

Let's face it. This kind of editing is not something you normally do in a GUI environment. This is a command-line environment task, and that is exactly what MS Office ISN'T.

 

[reclusivemonkey]

Thats what I love about this forum Doc, its not about egos or agendas, just people helping each other out. There's a great quote which I can't just at the moment remember the author, but it goes;

If the only tool you have is a hammer, every problem starts to look like a nail.

I love Access, its a fantastic program, but its not always the first thing I pull out of my tool bag .

 

[The_Doc_Man]

I love Access, its a fantastic program, but its not always the first thing I pull out of my tool bag

Ain't it the truth?

The biggest problem is fillin' up the tool bag. Toolsmithing is a lost art in the computer world, I fear.