Importing from Oracle extract...

[JasonS]

Hello!
Please, help me with this issue: I got a file which is an extract from
Oracle database, and I want this file to be programmatically imported into
MS Access table.
This is an example of what kind of data this file contains:
243,177,699990000,"222222222222222222","333333335555555","
",0,"AddressesOfsomeone",Yes
The last line contains nothing but a graphical square :-/
The first line does not contain headers for fields.

The file contains about 500 000 of such lines, so it's impossible to import
this file into Excel. When I'm importing it into Access using
DoCmd.TransferText method, I get an error message telling that there is no
F1 field in corresponding table in Access database.
The problem dissapears when I'm manually removing "-signs from that file.
But, is it possible to do it programmatically using VBA?
How can I open for editing an external file in txt format in VBA and remove
all " and that fu**ing square at the end of the file and finally save it to
the hard disk?

Thank you 4 helping me
Greetings from a stupid polish guy

 

[Terry]

Jason,

Manually import the file as a comma seperated text file.
You will see there is an option to define the text
qualifier Set it to " (double quote). Click the advanced
button and define all fields as text (even numbers,
yes /no etc. Save your spec remembering the name you gave
it. It should all import with NO errors into a new table.

If you can get this far you can either mess around with
the import changing data types etc till you get the data
to import as you desire (saving the spec each time) or
create an append query using the vanilla flavoured import
as a source and tweaking your data as you append to a new
table. The transfertext method (in VBA) can use the import
spec saved earlier to automate the process in the future.

HTH,

Terry