Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

replace numeric date format in sql postgresql file

Posted on 2008-11-03
10
Medium Priority
?
762 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
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
Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

 

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 750 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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

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…
Google Drive is extremely cheap offsite storage, and it's even possible to get extra storage for free for two years.  You can use the free account 15GB, and if you have an Android device..when you install Google Drive for the first time it will give…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
Learn how to navigate the file tree with the shell. Use pwd to print the current working directory: Use ls to list a directory's contents: Use cd to change to a new directory: Use wildcards instead of typing out long directory names: Use ../ to move…
Suggested Courses
Course of the Month11 days, 6 hours left to enroll

916 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