Importing lg csv file into Access tbl

[sahafi]

Hi
I'm trying to import data from Oracle into Access. I'm using a 'middle' tool
that help me run my SQL and it will show the output. My output data looks OK,
but when I try to save my results as .CSV so I can imported into Access tbl,
some of the data get split into multiple columns which cause the data to
shift one column to the left... the result incorrect queryable data. I'm
pulling over 700,000 rows of data at a time. If I try to open up the CSV
file, I only get what Excel XP will allow .. about 65k rows, and those rows
happened to be OK (by looking at my Access tble the messed up data starts on
row 100,000). I have tried all the text qualifiers with no success. I'm still
getting the right amount of records into Access, but wrong data under wrong
fields.
Can anyone suggest any thing to get this data into Access? I have been
trying for two days!!

Thanks.

 

[Klatuu]

Why not use an ODBC connection from Access to Oracle.
You can still create your queries in your middle tool and once you have them
working correctly, copy them into Access passthrough queries.

 

[sahafi]

Thanks Klatuu,
I really don't want to link to the tables in the Oracle dB, nor do I wanted
to import the tables into Access (very large tables). Basically I'm trying to
set up our dept databases as the corporate Oracle dB doesn't maintain a lot
of history.
I have tried the pass through query as you suggested, but I keep getting
this error:

[Microsoft][ODBC driver for Oracle][Oracle]ORA-00911:invalid character(#911)

The DSN name is setup, server name and password are saved, and everythings
look (at least to me) OK.
I have a page long SQL statement the pulls data from 5 different tables, and
it works fine except for previous issue I stated that the data get split into
multiple columns in the .csv file.
It looks like the error has to do with the syntax, but i'm not sure how to
by pass it.

Any direction into this is greatly appreciated.

Thanks.

 

[Klatuu]

The error indicates a syntax error in the query. Does' the middle app run
the same query syntax without errors?

I don't know much about Oracle specifically, but what I ususally do when I
need to work with a Pass Through is to use SQL Server Management Studio and
get the query working correctly there, then copy the SQL and paste it into
an Access query.

If all else fails, the clunky and ugly way would be to have the middle app
create a csv foramtted file, then import that into Access. But, this would
require manual intervention for every transfer.

Thanks Klatuu,
I really don't want to link to the tables in the Oracle dB, nor do I
wanted
to import the tables into Access (very large tables). Basically I'm trying
to
set up our dept databases as the corporate Oracle dB doesn't maintain a
lot
of history.
I have tried the pass through query as you suggested, but I keep getting
this error:

[Microsoft][ODBC driver for Oracle][Oracle]ORA-00911:invalid
character(#911)

The DSN name is setup, server name and password are saved, and everythings
look (at least to me) OK.
I have a page long SQL statement the pulls data from 5 different tables,
and
it works fine except for previous issue I stated that the data get split
into
multiple columns in the .csv file.
It looks like the error has to do with the syntax, but i'm not sure how to
by pass it.

Any direction into this is greatly appreciated.

Thanks.
--
when u change the way u look @ things, the things u look at change.

Why not use an ODBC connection from Access to Oracle.
You can still create your queries in your middle tool and once you have
them
working correctly, copy them into Access passthrough queries.

 

[sahafi]

Yes the same statement runs OK on the tool i'm using. Actually that tool lets
you see the output result in table/records format, and all the records look
alright. It just when I try to export the data out of the tool into .csv
because the tool cannot export data directly into Access table. If I can find
what causing the data coming from the .csv into Access to spill over multiple
columns, that would solve my problem.

Or, if I can get the PTQ to work, that's would be great too.

Thanks.
--
when u change the way u look @ things, the things u look at change.

The error indicates a syntax error in the query. Does' the middle app run
the same query syntax without errors?

I don't know much about Oracle specifically, but what I ususally do when I
need to work with a Pass Through is to use SQL Server Management Studio and
get the query working correctly there, then copy the SQL and paste it into
an Access query.

If all else fails, the clunky and ugly way would be to have the middle app
create a csv foramtted file, then import that into Access. But, this would
require manual intervention for every transfer.

Thanks Klatuu,
I really don't want to link to the tables in the Oracle dB, nor do I
wanted
to import the tables into Access (very large tables). Basically I'm trying
to
set up our dept databases as the corporate Oracle dB doesn't maintain a
lot
of history.
I have tried the pass through query as you suggested, but I keep getting
this error:

[Microsoft][ODBC driver for Oracle][Oracle]ORA-00911:invalid
character(#911)

The DSN name is setup, server name and password are saved, and everythings
look (at least to me) OK.
I have a page long SQL statement the pulls data from 5 different tables,
and
it works fine except for previous issue I stated that the data get split
into
multiple columns in the .csv file.
It looks like the error has to do with the syntax, but i'm not sure how to
by pass it.

Any direction into this is greatly appreciated.

Thanks.
--
when u change the way u look @ things, the things u look at change.

Why not use an ODBC connection from Access to Oracle.
You can still create your queries in your middle tool and once you have
them
working correctly, copy them into Access passthrough queries.

Hi
I'm trying to import data from Oracle into Access. I'm using a 'middle'
tool
that help me run my SQL and it will show the output. My output data
looks
OK,
but when I try to save my results as .CSV so I can imported into Access
tbl,
some of the data get split into multiple columns which cause the data
to
shift one column to the left... the result incorrect queryable data.
I'm
pulling over 700,000 rows of data at a time. If I try to open up the
CSV
file, I only get what Excel XP will allow .. about 65k rows, and those
rows
happened to be OK (by looking at my Access tble the messed up data
starts
on
row 100,000). I have tried all the text qualifiers with no success. I'm
still
getting the right amount of records into Access, but wrong data under
wrong
fields.
Can anyone suggest any thing to get this data into Access? I have been
trying for two days!!

Thanks.

 

[sahafi]

After several trials, Access behaved nicely and imported the .csv data as it
should be. What I did, is inserting one row of data in my table before the
import process, which forced the incoming data to be imported in the same
manner.

But i'm still curious to find out why the PTQ didn't work.

Thanks.
--
when u change the way u look @ things, the things u look at change.

Yes the same statement runs OK on the tool i'm using. Actually that tool lets
you see the output result in table/records format, and all the records look
alright. It just when I try to export the data out of the tool into .csv
because the tool cannot export data directly into Access table. If I can find
what causing the data coming from the .csv into Access to spill over multiple
columns, that would solve my problem.

Or, if I can get the PTQ to work, that's would be great too.

Thanks.
--
when u change the way u look @ things, the things u look at change.

The error indicates a syntax error in the query. Does' the middle app run
the same query syntax without errors?

I don't know much about Oracle specifically, but what I ususally do when I
need to work with a Pass Through is to use SQL Server Management Studio and
get the query working correctly there, then copy the SQL and paste it into
an Access query.

If all else fails, the clunky and ugly way would be to have the middle app
create a csv foramtted file, then import that into Access. But, this would
require manual intervention for every transfer.

Thanks Klatuu,
I really don't want to link to the tables in the Oracle dB, nor do I
wanted
to import the tables into Access (very large tables). Basically I'm trying
to
set up our dept databases as the corporate Oracle dB doesn't maintain a
lot
of history.
I have tried the pass through query as you suggested, but I keep getting
this error:

[Microsoft][ODBC driver for Oracle][Oracle]ORA-00911:invalid
character(#911)

The DSN name is setup, server name and password are saved, and everythings
look (at least to me) OK.
I have a page long SQL statement the pulls data from 5 different tables,
and
it works fine except for previous issue I stated that the data get split
into
multiple columns in the .csv file.
It looks like the error has to do with the syntax, but i'm not sure how to
by pass it.

Any direction into this is greatly appreciated.

Thanks.
--
when u change the way u look @ things, the things u look at change.


:

Why not use an ODBC connection from Access to Oracle.
You can still create your queries in your middle tool and once you have
them
working correctly, copy them into Access passthrough queries.

Hi
I'm trying to import data from Oracle into Access. I'm using a 'middle'
tool
that help me run my SQL and it will show the output. My output data
looks
OK,
but when I try to save my results as .CSV so I can imported into Access
tbl,
some of the data get split into multiple columns which cause the data
to
shift one column to the left... the result incorrect queryable data.
I'm
pulling over 700,000 rows of data at a time. If I try to open up the
CSV
file, I only get what Excel XP will allow .. about 65k rows, and those
rows
happened to be OK (by looking at my Access tble the messed up data
starts
on
row 100,000). I have tried all the text qualifiers with no success. I'm
still
getting the right amount of records into Access, but wrong data under
wrong
fields.
Can anyone suggest any thing to get this data into Access? I have been
trying for two days!!

Thanks.