Solved

replace numeric date format in sql postgresql file

Posted on 2008-11-03
10
744 Views
Last Modified: 2013-12-26
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
Comment
Question by:1Tsupp
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
10 Comments
 
LVL 40

Expert Comment

by:omarfarid
ID: 22867541
can you show sample file content?
0
 
LVL 14

Expert Comment

by:cjl7
ID: 22867566
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
 

Author Comment

by:1Tsupp
ID: 22867617
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
Technology Partners: 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!

 

Author Comment

by:1Tsupp
ID: 22867766
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
 
LVL 19

Expert Comment

by:jools
ID: 22868331
Can you re-export the database in the right date format?

Sorry, had to ask...
0
 
LVL 22

Expert Comment

by:earth man2
ID: 22868877
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
 
LVL 3

Accepted Solution

by:
bishillo earned 250 total points
ID: 22869613
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
 
LVL 14

Expert Comment

by:cjl7
ID: 22873905
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
 

Author Comment

by:1Tsupp
ID: 22874743
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
 

Author Closing Comment

by:1Tsupp
ID: 31512704
Thanks
0

Featured Post

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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Zimbra on Amazon Linux help 7 159
AWK: Pytthagoras bp script Part deux 22 50
Shrink Linux Swap File Size CentOS 10 72
[bash] complex parsing 9 34
Recently, an awarded photographer, Selina De Maeyer (http://www.selinademaeyer.com/), completed a photo shoot of a beautiful event (http://www.sintjacobantwerpen.be/verslag-en-fotoreportage-van-de-sacramentsprocessie-door-antwerpen#thumbnails) in An…
Utilizing an array to gracefully append to a list of EmailAddresses
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

739 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question