Extracting data from a database and storing it. 
Author Message
 Extracting data from a database and storing it.

Hi all.

I'm writing an App which extracts data from a database and stores in text
files.

delimiters.

I want to reinsert the data into a table later, so I need delimeters (lots
of null fields) and ideally a header

What would be the best way to do this? I'm considering datadumper and using
a "|" as my field delimeter.

Anyone got any better ideas? (is there some magic tool i don't know about?)

Thanks.

Pete



Tue, 22 Nov 2005 00:46:37 GMT  
 Extracting data from a database and storing it.

Quote:

> Hi all.

> I'm writing an App which extracts data from a database and stores in text
> files.

> delimiters.

> I want to reinsert the data into a table later, so I need delimeters (lots
> of null fields) and ideally a header

> What would be the best way to do this? I'm considering datadumper and using
> a "|" as my field delimeter.

> Anyone got any better ideas? (is there some magic tool i don't know about?)

> Thanks.

> Pete

let's see the code that writes to the text file... prolly as easy as:


--
Michael Budash



Tue, 22 Nov 2005 00:58:34 GMT  
 Extracting data from a database and storing it.
##sub called using: current directory and tablename
   &extract_tab($curr_dir, $tab)

sub extract_tab

    open OUTFILE, ">$curr_dir/$tab.ctl"; # open file to write to.
    print "=> Getting $tab \n" ;

    my  $sth = $dbh->prepare( "SELECT * FROM $tab" )
        or die "Can't prepare SQL statement: $DBI::errstr\n";

    $sth->execute
        or die "Can't execute SQL statement: $DBI::errstr\n";



    }

    warn "Problem in fetchrow_array(): ", $sth->errstr(), "\n"
    if $sth->err();

    close OUTFILE;
    return 1;

Quote:
}



Quote:


> > Hi all.

> > I'm writing an App which extracts data from a database and stores in
text
> > files.

> > delimiters.

> > I want to reinsert the data into a table later, so I need delimeters
(lots
> > of null fields) and ideally a header

> > What would be the best way to do this? I'm considering datadumper and
using
> > a "|" as my field delimeter.

> > Anyone got any better ideas? (is there some magic tool i don't know
about?)

> > Thanks.

> > Pete

> let's see the code that writes to the text file... prolly as easy as:


> --
> Michael Budash



Tue, 22 Nov 2005 01:44:43 GMT  
 Extracting data from a database and storing it.

Quote:





> > > Hi all.

> > > I'm writing an App which extracts data from a database and stores

> > > file. But there are no delimiters.

> > > I want to reinsert the data into a table later, so I need
> > > delimeters (lots of null fields) and ideally a header

> > > What would be the best way to do this? I'm considering datadumper
> > > and using a "|" as my field delimeter.

> > > Anyone got any better ideas? (is there some magic tool i don't
> > > know about?)

> > let's see the code that writes to the text file... prolly as easy as:


> [snip]





Quote:
>     }

> [snip]

hth -

--
Michael Budash



Tue, 22 Nov 2005 01:50:13 GMT  
 Extracting data from a database and storing it.
what I'm wanting is:

BEGINDATA
19|19|HOME1|262|4|175|2|2|0|0|N|60|60|DEX|1|HPLMN Address 1|HPLMN Address
2|HPLMN xxx|HPLMN xxx|HPLMN xxx|26204|HPLMN Contact|0049 12345678||Home
Network|278|7|600|600|1995-JAN-01 00:00:00|1999-JAN-01 00:00:00|
21|21|TEST1|262|2|172|2|2|0|35|N|60|9999|TS1|2|TEST1 Address 1|TEST1 Address
2|TEST1 xxx|TEST1 xxx|TEST1 xxx|26202|TEST1 Contact|0049
87654321||TEST1|278|7|||1995-JAN-01 00:00:00|1999-JAN-01 00:00:00|
22|22|TEST2|240|8|199|2|2|0|35|Y|60|9999|TS2|2|TEST2 Address 1|TEST2 Address
2|TEST2 xxx|TEST2 xxx|TEST2 xxx|24008|TEST2 Contact|0046

what I've got now is:

7369 SMITH CLERK 7902 17-DEC-80 800  20
7499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30
7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30
7566 JONES MANAGER 7839 02-APR-81 2975  20
7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 30
7698 BLAKE MANAGER 7839 01-MAY-81 2850  30

Because my app will connect to both Oracle and Sybase db's I want a generic
approach, to unload and reload.
I could write database specific sub  routines.. but i'd prefer to avoid it
if I can..


Quote:


> > Hi all.

> > I'm writing an App which extracts data from a database and stores in
text
> > files.

> > delimiters.

> > I want to reinsert the data into a table later, so I need delimeters
(lots
> > of null fields) and ideally a header

> > What would be the best way to do this? I'm considering datadumper and
using
> > a "|" as my field delimeter.

> > Anyone got any better ideas? (is there some magic tool i don't know
about?)

> > Thanks.

> > Pete

> let's see the code that writes to the text file... prolly as easy as:


> --
> Michael Budash



Tue, 22 Nov 2005 01:58:48 GMT  
 Extracting data from a database and storing it.
Too right it does :)

It works a treat!. Many thanks

Pete

7369|SMITH|CLERK|7902|17-DEC-80|800||20
7499|ALLEN|SALESMAN|7698|20-FEB-81|1600|300|30
7521|WARD|SALESMAN|7698|22-FEB-81|1250|500|30
7566|JONES|MANAGER|7839|02-APR-81|2975||20
7654|MARTIN|SALESMAN|7698|28-SEP-81|1250|1400|30
7698|BLAKE|MANAGER|7839|01-MAY-81|2850||30
7782|CLARK|MANAGER|7839|09-JUN-81|2450||10
7788|SCOTT|ANALYST|7566|19-APR-87|3000||20
7839|KING|PRESIDENT||17-NOV-81|5000||10
7844|TURNER|SALESMAN|7698|08-SEP-81|1500|0|30
7876|ADAMS|CLERK|7788|23-MAY-87|1100||20
7900|JAMES|CLERK|7698|03-DEC-81|950||30
7902|FORD|ANALYST|7566|03-DEC-81|3000||20
7934|MILLER|CLERK|7782|23-JAN-82|1300||10

Quote:



> hth -

> Michael Budash



Tue, 22 Nov 2005 02:08:48 GMT  
 Extracting data from a database and storing it.

Quote:




> Too right it does :)

> It works a treat!. Many thanks

> Pete

> 7369|SMITH|CLERK|7902|17-DEC-80|800||20
> 7499|ALLEN|SALESMAN|7698|20-FEB-81|1600|300|30
> 7521|WARD|SALESMAN|7698|22-FEB-81|1250|500|30
> 7566|JONES|MANAGER|7839|02-APR-81|2975||20
> 7654|MARTIN|SALESMAN|7698|28-SEP-81|1250|1400|30
> 7698|BLAKE|MANAGER|7839|01-MAY-81|2850||30
> 7782|CLARK|MANAGER|7839|09-JUN-81|2450||10
> 7788|SCOTT|ANALYST|7566|19-APR-87|3000||20
> 7839|KING|PRESIDENT||17-NOV-81|5000||10
> 7844|TURNER|SALESMAN|7698|08-SEP-81|1500|0|30
> 7876|ADAMS|CLERK|7788|23-MAY-87|1100||20
> 7900|JAMES|CLERK|7698|03-DEC-81|950||30
> 7902|FORD|ANALYST|7566|03-DEC-81|3000||20
> 7934|MILLER|CLERK|7782|23-JAN-82|1300||10

great - glad to help. but please don't top post - put your answers after
the originals like everyone else ...

--
Michael Budash



Tue, 22 Nov 2005 02:16:38 GMT  
 
 [ 7 post ] 

 Relevant Pages 

1. storing binary data in a database record

2. Extracting BLOB data from Oracle database

3. Storing JPEG in SQL Database

4. Can I store an array into a DataBase?

5. Perl won't store latin1 in mysql database

6. storing HoH in database

7. store binaries in a database

8. Storing images into MS Access 97 database

9. Stored encrypted messages in database

10. store session data in mySQL?

11. storing/restoring perl data structure

12. Storing session data in IPC vs DB File

 

 
Powered by phpBB® Forum Software