Re: linux/unix script to dump csv files for each table in db
Date: Wed, 1 Dec 2010 10:59:54 -0800 (PST)
Message-ID: <f305ce6f-98fe-461e-8f48-752332909575_at_j18g2000prn.googlegroups.com>
On Dec 1, 9:38 am, Mladen Gogala <n..._at_email.here.invalid> wrote:
> On Wed, 01 Dec 2010 06:52:26 -0800, Mark D Powell wrote:
> > On Nov 30, 7:45 am, syd_p <sydneypue..._at_yahoo.com> wrote:
> >> Hi,
>
> >> I am using a very simple script to dump out some csv files from all the
> >> tables in a mysql db.
> >> Well actually they are psv files cos they are separted by a pipe
> >> symbol.
>
> >> But anyway I want to do the same thing with oracle 10G. Any ideas on
> >> how to do this?
> >> Please help if you can!
>
> >> -Syd
>
> >> #!/bin/bash
>
> >> db=nb276
> >> user=root
> >> pass=xxxx
>
> >> for table in $(mysql -u$user -p$pass $db -Be "SHOW tables" | sed 1d);
> >> do \
> >> echo "exporting $table.."
> >> mysql $db -u$user -p$pass -e "SELECT * FROM $table" | sed
> >> 's/\t/|/g'> $db.$table.psv
>
> >> done
>
> > How do I export a database table to a flat file ?
>
> > http://www.jlcomp.demon.co.uk/faq/flatfile.html
>
> > HTH -- Mark D Powell --
>
> There are, of course, tools like this one:
>
> http://mgogala.byethost5.com/dump2csv.zip
>
> I have the new revision which will be put on the website tonight. The new
> revision has the binary flag turned on, to do the trick with the binary
> data and allows the choice of separators. The default separator is
> changed from the comma to tab.
>
> --http://mgogala.byethost5.com
Can't locate Text/CSV.pm in _at_INC (@INC contains: /opt/perl_32/lib/ 5.8.8/IA64.ARCHREV_0-thread-multi /opt/perl_32/lib/5.8.8 /opt/perl_32/ lib/site_perl/5.8.8/IA64.ARCHREV_0-thread-multi /opt/perl_32/lib/
site_perl/5.8.8 /opt/perl_32/lib/site_perl /opt/perl_32/lib/ vendor_perl/5.8.8/IA64.ARCHREV_0-thread-multi /opt/perl_32/lib/ vendor_perl/5.8.8 /opt/perl_32/lib/vendor_perl .) at ./dump2csv.plline 5.
BEGIN failed--compilation aborted at ./dump2csv.pl line 5.
(I cut and pasted it into that pl file and added a comment on the second line about where I got it from - you might want to put where it is from and author/copyright in your download)
I know I ought to know how to fix that off the top of my head, but I can't remember and perl works for my RDA, I think, ISTR I had to do something to make that work, too... or do I use the shell version?...
oracle:TTST_at_XXXX /home2/oracle/scripts/joel$ perl -V
Summary of my perl5 (revision 5 version 8 subversion 8) configuration:
Platform:
osname=hpux, osvers=11.22, archname=IA64.ARCHREV_0-thread-multi
uname='hp-ux bigsur03 b.11.22 u ia64 1800516905 unlimited-user
license '
config_args='-ders -Dcc=cc -Dusethreads -Duseithreads - Ud_sigsetjmp -Uinstallusrbinperl -Ulocincpth= -Uloclibpth= -Dsh=/usr/ bin/sh -Dd_attribut=undef -Dd_attribute_warn_unused_result=undef - Dd_u32align=define -Aprepend:libswanted=cl -Dvendorprefix=/opt/ perl_32 -Doptimize=-fast +DSitanium2 +Ofltacc=strict -Accflags=+Z - Accflags=-DUSE_SITECUSTOMIZE -Duselargefiles -Accflags=-DNO_HASH_SEED -
Dprefix=/opt/perl_32 -Dinc_version_list=5.8.7/$archname 5.8.7 5.8.6/$archname 5.8.6 5.8.4/$archname 5.8.4 5.8.3/$archname 5.8.3 5.8.2/$archname 5.8.2 5.8.1/$archname 5.8.1 5.8.0/$archname 5.8.0 -Dsed=/usr/bin/sed -Duseshrplib -Dconfig_heavy=Config_dynamic.pl - Dcf_by=ActiveState -Dcf_email=support_at_ActiveState.com'
hint=recommended, useposix=true, d_sigaction=define usethreads=define use5005threads=undef useithreads=define usemultiplicity=define
useperlio=define d_sfio=undef uselargefiles=define usesocks=undef
use64bitint=undef use64bitall=undef uselongdouble=undef
usemymalloc=n, bincompat5005=undef
Compiler:
cc='cc', ccflags =' -D_POSIX_C_SOURCE=199506L -D_REENTRANT -Ae -
D_HPUX_SOURCE -Wl,+vnocompatwarnings +Z -DUSE_SITECUSTOMIZE -
DNO_HASH_SEED -D_LARGEFILE_SOURCE -D_FILE_OFFSET_BITS=64 ',
optimize='-fast +DSitanium2 +Ofltacc=strict',
cppflags='-D__STDC_EXT__ -D_HPUX_SOURCE -D_POSIX_C_SOURCE=199506L -
D_REENTRANT -D_HPUX_SOURCE -Wl,+vnocompatwarnings -DUSE_SITECUSTOMIZE -
DNO_HASH_SEED'
ccversion='B3910B A.05.55', gccversion='', gccosandvers='' intsize=4, longsize=4, ptrsize=4, doublesize=8, byteorder=4321 d_longlong=define, longlongsize=8, d_longdbl=define, longdblsize=16
ivtype='long', ivsize=4, nvtype='double', nvsize=8, Off_t='off_t', lseeksize=8
alignbytes=8, prototype=define
Linker and Libraries:
ld='/usr/bin/ld', ldflags =' -L/usr/lib/hpux32'
libpth=/usr/lib/hpux32 /lib /usr/lib /usr/ccs/lib /usr/local/lib
libs=-lcl -lnsl -lnm -lndbm -ldl -ldld -lm -lsec -lpthread -lc
perllibs=-lcl -lnsl -lnm -ldl -ldld -lm -lsec -lpthread -lc
libc=/usr/lib/hpux32/libc.so, so=so, useshrplib=true,
libperl=libperl.so
gnulibc_version=''
Dynamic Linking:
dlsrc=dl_hpux.xs, dlext=so, d_dlsymun=undef, ccdlflags='-Wl,-E -
Wl,-B,deferred '
cccdlflags='+Z', lddlflags='-b +vnocompatwarnings -L/usr/lib/ hpux32'
Characteristics of this binary (from libperl): Compile-time options: MULTIPLICITY PERL_IMPLICIT_CONTEXT
PERL_MALLOC_WRAP USE_ITHREADS USE_LARGE_FILES
USE_PERLIO USE_REENTRANT_API USE_SITECUSTOMIZE
Locally applied patches:
ActivePerl Build 817.1 [268662]
Iin_load_module moved for compatibility with build 806
POSIX: Support the long hostname version of uname()
Avoid signal flag SA_RESTART for older versions of HP-UX
PerlEx support in CGI::Carp
Less verbose ExtUtils::Install and Pod::Find
Patch for CAN-2005-0448 from Debian with modifications
Partly reverted 24733 to preserve binary compatibility
28671 Define PERL_NO_DEV_RANDOM on Windows
28376 Add error checks after execing PL_cshname or PL_sh_path
28305 Pod::Html should not convert "foo" into ``foo''
27736 Make perl_fini() run with Sun WorkShop compiler
27619 Bug in Term::ReadKey being triggered by a bug in
Term::ReadLine
27549 Move DynaLoader.o into libperl.so
27528 win32_pclose() error exit doesn't unlock mutex
27527 win32_async_check() can loop indefinitely
27515 ignore directories when searching _at_INC
27359 Fix -d:Foo=bar syntax
27210 Fix quote typo in c2ph
27203 Allow compiling swigged C++ code
27200 Make stat() on Windows handle trailing slashes correctly
27194 Get perl_fini() running on HP-UX again
27133 Initialise lastparen in the regexp structure
27034 Avoid "Prototype mismatch" warnings with autouse
26970 Make Passive mode the default for Net::FTP
26921 Avoid getprotobyname/number calls in IO::Socket::INET
26897,26903 Make common IPPROTO_* constants always available
26670 Make '-s' on the shebang line parse -foo=bar switches
26379 Fix alarm() for Windows 2003
26087 Storable 0.1 compatibility
25861 IO::File performace issue
25084 long groups entry could cause memory exhaustion
24699 ICMP_UNREACHABLE handling in Net::Ping
Built under hpux
Compiled at Sep 19 2006 13:53:03
_at_INC:
/opt/perl_32/lib/5.8.8/IA64.ARCHREV_0-thread-multi
/opt/perl_32/lib/5.8.8
/opt/perl_32/lib/site_perl/5.8.8/IA64.ARCHREV_0-thread-multi
/opt/perl_32/lib/site_perl/5.8.8
/opt/perl_32/lib/site_perl
/opt/perl_32/lib/vendor_perl/5.8.8/IA64.ARCHREV_0-thread-multi
/opt/perl_32/lib/vendor_perl/5.8.8
/opt/perl_32/lib/vendor_perl
.
I have on my list of things to do to look at a module that allows creating the weird crap Excel files need, Spreadsheet_WriteExcel from sourceforge, or some such thing.
jg
-- _at_home.com is bogus. SAP Oracle Support Verdict Gives Companies Another Reason to Use Open Source: http://www.networkworld.com/community/node/69098Received on Wed Dec 01 2010 - 12:59:54 CST