Thread: MS Access data to PostgrSQL data
hi, can anyone help me ? i need to convert a ms access .mdb file into somthing readable so i can insert it into my postgreSQL db... does anyone have any experiance? (preferably using PHP) Robert Korteweg The Netherlands
>>hi,
>>can anyone help me ? i need to convert a ms access .mdb file into
>>somthing readable so i can insert it into my postgreSQL db...
>>does anyone have any experiance? (preferably using PHP)
>>Robert Korteweg
>>The Netherlands
Here is your steps.
1) Create your destination table in postgres
2) Export your data to a comma delimeted format
3) save this perl script to a file
------
#! /usr/bin/perl
$inquotes = 0;
while (<>){
# Chop the crlf
chop ($_);
chop ($_);
# this first bit goes through and replaces
# all the commas that re not in quotes with tildes
for ($i=0 ; $i < length($_) ; $i++){
$char=substr($_,$i,1);
if ($char eq '"' ){
$inquotes = not($inquotes);
}else{
if ( (!$inquotes) && ($char eq ",") ){
substr($_,$i,1)="~";
}
}
}
# this replaces any quotes
s/"//g;
print "$_\n";
}
-----
4) type this cat myfile.txt | perl myprog.pl > outfile.dat
5) go to psql
6) COPY "tablename" FROM '/path/to/outfile.dat' USING DELIMITERS '~';
you can even automate this with a VB script.
Tim Uckun wrote: > > >>hi, > >>can anyone help me ? i need to convert a ms access .mdb file into > >>somthing readable so i can insert it into my postgreSQL db... > >>does anyone have any experiance? (preferably using PHP) > >>Robert Korteweg > >>The Netherlands > > Here is your steps. > 1) Create your destination table in postgres > 2) Export your data to a comma delimeted format > 3) save this perl script to a file Good script. But, if you have the PostgreSQL ODBC driver installed on the windows machine, you can use Access's SaveAs/Export function to save it directly into a PostgreSQL table. Although, that might be overkill for this, I do this quite alot (did it five times today to get some data over to the real RDBMS.....). -- Lamar Owen WGCR Internet Radio 1 Peter 4:11
You want to import access info into pgsql using PHP? For starters, go here: http://www.php.net/FAQ.php and read section 4.2. It tells you what you need to interact with Access and PHP. If PHP is on Linux, then you are going to jump through some hoops to do it. I'd say your best route, if you can, would be use VB on a windows box and write an app to do it. Even easier would be write some code in the Access database to connect to the postgresql database over ODBC and just upload it that way. Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com ----- Original Message ----- From: "Robert Korteweg" <robert@bit.nl> To: <pgsql-general@postgresql.org> Sent: Wednesday, January 17, 2001 4:21 AM Subject: [GENERAL] MS Access data to PostgrSQL data > hi, > > can anyone help me ? i need to convert a ms access .mdb file into > somthing readable so i can insert it into my postgreSQL db... > > does anyone have any experiance? (preferably using PHP) > > Robert Korteweg > The Netherlands
That's sort of the problem though... the person never quite mentioned where PHP was located. I agree with you though, If you have Access and postgreql, why write a whole new program in a third language to get the data over? There has to be other reasons if the person wants to make it that difficult. ;) Adam Lang Systems Engineer Rutgers Casualty Insurance Company http://www.rutgersinsurance.com ----- Original Message ----- From: "Lamar Owen" <lamar.owen@wgcr.org> To: "Tim Uckun" <tim@diligence.com> Cc: <pgsql-general@postgresql.org> Sent: Thursday, January 18, 2001 3:25 PM Subject: Re: [GENERAL] Re: MS Access data to PostgrSQL data > Tim Uckun wrote: > > > > >>hi, > > >>can anyone help me ? i need to convert a ms access .mdb file into > > >>somthing readable so i can insert it into my postgreSQL db... > > >>does anyone have any experiance? (preferably using PHP) > > >>Robert Korteweg > > >>The Netherlands > > > > Here is your steps. > > 1) Create your destination table in postgres > > 2) Export your data to a comma delimeted format > > 3) save this perl script to a file > > Good script. > > But, if you have the PostgreSQL ODBC driver installed on the windows > machine, you can use Access's SaveAs/Export function to save it directly > into a PostgreSQL table. Although, that might be overkill for this, I > do this quite alot (did it five times today to get some data over to the > real RDBMS.....). > -- > Lamar Owen > WGCR Internet Radio > 1 Peter 4:11
Tim Uckun wrote: > > >>hi, > >>can anyone help me ? i need to convert a ms access .mdb file into > >>somthing readable so i can insert it into my postgreSQL db... > >>does anyone have any experiance? (preferably using PHP) > >>Robert Korteweg > >>The Netherlands > > Here is your steps. > 1) Create your destination table in postgres > 2) Export your data to a comma delimeted format > 3) save this perl script to a file > There is also a CPAN module to parse out comma-delimited data: Text::CSV_XS and a DBD module that builds on that: DBD::CSV I think the perldoc for that module even uses Excel data as an example.
Hi, my boss said that this was very low prio so we probaly will not do the import and export, automaticly.... Robert Robert Korteweg wrote: > hi, > > can anyone help me ? i need to convert a ms access .mdb file into > somthing readable so i can insert it into my postgreSQL db... > > does anyone have any experiance? (preferably using PHP) > > Robert Korteweg > The Netherlands
Search this (and the novice) list for importing from Excel. I recently posted some tips there on using tab delimited text files and the copy command. DBD has a file size limit. --rob ----- Original Message ----- From: "Keith G. Murphy" <keithmur@mindspring.com> To: <pgsql-general@postgresql.org> Sent: Friday, January 19, 2001 11:13 AM Subject: Re: Re: MS Access data to PostgrSQL data > Tim Uckun wrote: > > > > >>hi, > > >>can anyone help me ? i need to convert a ms access .mdb file into > > >>somthing readable so i can insert it into my postgreSQL db... > > >>does anyone have any experiance? (preferably using PHP) > > >>Robert Korteweg > > >>The Netherlands > > > > Here is your steps. > > 1) Create your destination table in postgres > > 2) Export your data to a comma delimeted format > > 3) save this perl script to a file > > > There is also a CPAN module to parse out comma-delimited data: > > Text::CSV_XS > > and a DBD module that builds on that: > > DBD::CSV > > I think the perldoc for that module even uses Excel data as an example. >
>> hi, >> >> can anyone help me ? i need to convert a ms access .mdb file into >> somthing readable so i can insert it into my postgreSQL db... >> >> does anyone have any experiance? (preferably using PHP) >> >> Robert Korteweg >> The Netherlands export the .mdb file in comma seperated format and import in postgres with the COPY function. Wouter