Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 763
  • Last Modified:

replace numeric date format in sql postgresql file

I have a postgresql dump file which I want to import to version 8.3
I need to replace numeric data in this file with format "10-25-2004" to "25-10-2004".
i.e. the middle  field needs to be swapped with the first field.

How do I do this in linux?

0
1Tsupp
Asked:
1Tsupp
1 Solution
 
omarfaridCommented:
can you show sample file content?
0
 
cjl7Commented:
Hi,

This is one way...

echo "10-25-2004" |perl -pe 's/(..)-(..)-(....)/$2-$1-$3/g'

And if you have a dump-file

cat file |perl -pe 's/(..)-(..)-(....)/$2-$1-$3/g'

If you know it's only this specific date and nothing else you could use sed (sed -i 's/10-25-2004/25-10-2004/g' <file>)


0
 
1TsuppAuthor Commented:
sample data line will be:
mrproper      Can Music Support Interfaces to Complex Databases?      cpchr      Euromicro - 1997, Frontiers of Information Technology      06-13-1997      Proper      D.      DAta2      J.L.      Long      F.W                  \N      \N      \N      \N      \N      \N            \N      \N      \N                  261      \N      Budapest      Hungary      y      \N      \N            Published      \N      \N      \N      \N      \N      \N

I want to take the field in this instance 06-13-1997 and swap it to 13-06-1997 leaving the rest of the line unaltered.
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
1TsuppAuthor Commented:
I've just tried cat file |perl -pe 's/(..)-(..)-(....)/$2-$1-$3/g'
and it appears to have partially worked however I have just realised the data is a little more complex.

The data line:

author      Applications of Virtual Reality      bked            01-01-1995      Author1      R.A.      Bloggs      H.      Vince      J.A.                  \N      0-12-227755-4      \N      \N      \N      \N      328      \N                              286      \N      \N      \N      \N      Academic Press      \N            Published      \N      \N      \N      \N      \N      \N

is now:

author      Applications of Virtual Reality      bked            01-01-1995      Author1      R.A.      Bloggs      H.      Vince      J.A.                  \N      0-227755-4      \N      \N      \N      \N      328      \N                              286      \N      \N      \N      \N      Academic Press      \N            Published      \N      \N      \N      \N      \N      \N

i.e. 0-12-227755-4 has changed to 0-227755-4
I want to leave this unchanged so the test needs to be strictly:  
Test for  first2 digit numeric-second2 digit numeric-4 digit numeric
Swap second2 digit numeric-first2 digit numeric-4 digit numeric

Any clues?

0
 
joolsCommented:
Can you re-export the database in the right date format?

Sorry, had to ask...
0
 
earth man2Commented:
http://www.postgresql.org/docs/8.3/static/runtime-config-client.html#GUC-DATESTYLE
prepend data in file with a set command ie

SET DATESTYLE iso, mdy;
0
 
bishilloCommented:
the "SET DATESTYLE iso, mdy;" is the best way to go. You should use same configuration for import/import, in dates and also in charset (iso-8859-1, utf-8...).

But in perl you should use [0-9] (\d for decimal is also valid) iinstead of dot. Dot matches every character, with [0-9] or \d just numbers, so it won't broke other data.

cat file | perl -pe 's/(\d{2})-(\d{2})-(\d{4})/$2-$1-$3/g'
0
 
cjl7Commented:
Working with regular expressions is always tricky. It's probably better to get the data into the database and then manipulate it. After all that's one thing a SQL server is good at! ;)
0
 
1TsuppAuthor Commented:
cat file | perl -pe 's/(\d{2})-(\d{2})-(\d{4})/$2-$1-$3/g' works without altering other data.
I cannot use "SET DATESTYLE iso, mdy;" - i'm migrating data from postgres 6.5 to 8.3
and massaging the 6.5 pg_dump file.
Thanks to all for your help.

0
 
1TsuppAuthor Commented:
Thanks
0

Featured Post

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Tackle projects and never again get stuck behind a technical roadblock.
Join Now