find and replace in a .csv from Access

[Guest]

Hi,

I have written an Access procedure that retrieves a XML table and saves it
as a .csv. This is then imported into my database with the transfertext
method. All is well so far.

The problem is that within the data retrieved from the web it returns a N/A
(as a text) if a value does not exist. Normally the values should be numeric
and I have no control over what data comes back. This means I get a type
conversion failure when I import into access and this is causing access to
crash at times.

As a solution I am thinking I could do a find and replace the N/A with a
zero and problem solved. If I open the .csv in excel the relevant fields are
in columns D and E and my file is called download.csv.

Am i on the right track? How do I run a function from Access to do the find
and replace so I can add this to my procedure before running transfertext?

Bruce

 

[Guest]

Bruce,

Why not create an import specification. You can use the regular steps from
get external data and then choose "advanced". Here you have the ability to
set the field properties for the various fields you are about to import. Set
the specific field to text this will not crash you app. You have the option
to save the import specification. Within your code you can refer to the
importspecification and use this evry time (assuming the format is the same
every time). You'll find intellisense after the docmd.transfertext in code.

hth

 

[John W. Vinson]

Am i on the right track? How do I run a function from Access to do the find
and replace so I can add this to my procedure before running transfertext?

What I'd suggest is *linking* to the CSV file rather than importing. Then you
can run an Append query to append the data to an existing database, using a
calculated field for this value such as

ImpNumber: IIF([number]="N/A", Null, [number])

where [number] is the name of the field in the csv file.

John W. Vinson [MVP]