How to get file extension - Page 3 - Shell Programming and Scripting

ghostdog74 · #15 01-15-2008

Quote:

Originally Posted by shirleyeow

Hi ghostdog74,

Yup, all the date value 010108, 020108, 030108 become 00:00:00 after load the text file into database if I declare the Date column as "DATE or DATETIME" datatype. But if I declare the date column as "VARCHAR", then the value can be insert into the Date column correctly.

I just worry if I declare the Date column as "VARCHAR", may be later will cause some problem to the database.

So just want to find some way to convert the date format first before load it into database. Any suggestion?

Thank you.

Best Regards,
Shirley

you might want to try reformatting your column 1 to become what mysql can recognise.

Code:

awk 'BEGIN{FS="|"}
{
  d=substr($1,1,2)
  m=substr($1,3,2)
  y=substr($1,5,2)
  datetime="20"y"-"m"-"d
  cmd="insert table values(\047" datetime "\047,"$2")"  
  print cmd
}' file

output:

Code:

# ./test.sh
insert table values('2008-01-01', 200)
insert table values('2008-01-02', 450)
insert table values('2008-01-03', 600)

However, i suggest you wait for somebody who can guide you further in mysql.

ilan · #16 01-15-2008

Hi Shirley,

1. it is always better not to use "rm -r *" if the files matching the given pattern is too large, then your rm command is goin to fail with 'argument is too long' error. so, better is to use the combination of find and xargs in these situations.

2. if you want to execute the mysql queries from a shell script it is really easier to do like this...

mysql -D<database name> -e "<your query here>"

if you have a password for logging into mysql, then read about the password option in the above usage and use it ;-)

-ilan

shirleyeow · #17 01-15-2008

Hi ilan,

Thank you so much for your advise.

But I have some queries again to seek for your advise.

1. Can you show some example how to use find and xargs to delete files?

2. Do you know how to convert the date format in a text file to the format that MYSQL can recognise? Let say I have 010107 in a text file, when I load it into database which Date column datatype is DATE, the value will become 00:00:00. Is there any way I can convert the date format by using shell script first before load it in to database? Please advise.

Thank you.

Br,
Shirley

ilan · #18 01-17-2008

Quote:

Originally Posted by shirleyeow

Hi ilan,

Thank you so much for your advise.

But I have some queries again to seek for your advise.

1. Can you show some example how to use find and xargs to delete files?

2. Do you know how to convert the date format in a text file to the format that MYSQL can recognise? Let say I have 010107 in a text file, when I load it into database which Date column datatype is DATE, the value will become 00:00:00. Is there any way I can convert the date format by using shell script first before load it in to database? Please advise.

Thank you.

Br,
Shirley

for no.1:
find /path/to/blah -type d -mtime +180 -exec rmdir {} \;

or

find /path/to/blah -type d -mtime +180 -print0 | xargs -0 rm -rf

for no.2: (if i understand your req. correctly, you are trying to convert 010107 to 01:01:07)

d=010107; echo $(echo $d | cut -c 1-2):$(echo $d | cut -c 3-4):$(echo $d | cut -c 5-6)

-ilan

More UNIX and Linux Forum Topics You Might Find Helpful
Thread	Thread Starter	Forum	Replies	Last Post
How do i check whether a file has extension?	sunday8	Shell Programming and Scripting	2	08-29-2008 06:58 PM
how to unzip File which has no extension	thepurple	SUN Solaris	13	11-29-2007 02:46 AM
Stripping out the extension of a file name	ramky79	Shell Programming and Scripting	2	12-27-2006 11:25 AM
Check file extension	mahalakshmi	Shell Programming and Scripting	6	12-27-2006 10:15 AM
default extension of file	rujupriya	UNIX for Dummies Questions & Answers	2	05-17-2006 07:49 AM

Thread Tools	Search this Thread
Show Printable Version Email this Page	Search this Thread: Advanced Search
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode