Need Help Parsing text

[lee1978]

Hi, Guys&Girls

I am new to the site and need help parsing a file the file has a max of 2000 line and looks like this

1006:93:89:16,6473:94:21:33
1009:47:13:95,624:15:78:4
1019:53:77:46,4547:29:42:78
1022:39:26:64,39:52:59:99
1024:48:28:52,7087:29:70:75

i need to convert the above so i can insert it directly in to an sql database it need to look like this exactly not sure what is the best way to do it either somekind of script to convert the file or a sql script new to all this but getting there at the moment i have to do it manually which just takes me hours thanks in advance for your help

INSERT INTO `de_Coordonnee` (`ID`, `TYPE`, `POSIN`, `POSOUT`, `COORDET`, `COORDETOUT`, `NOTE`, `UTILISATEUR`, `udate`) VALUES
( , 1, '1006', '6473', '93-89-16', '94-21-33', '', 'Lee', 1283682460),

Thanks

Lee

[lee1978]

Forgot to add i would be willing to pay someone to do it

Lee

[Charles]

Forgot to add i would be willing to pay someone to do it

Lee

Don't bother; what you are asking is trivial. But we'll need some more information. What exactly is the format of the source file? What do each of those columns represent and how do they map to the resulting table? And we'll need to know what tools you have available. Perl? PHP? Or are you using a Windows box?

[keirvt]

Your specification example has some inconsistencies like how the data items in the input text file are to map onto the mysql table.

Below is some python code that does mostly what you want. I wrote it on the fly and it isn't tested and as is the way with code may need a little work Anyway it gives you a framework that you should be able to bang into what you want done.

I would have included the correct command construction and tested it if the example had been more clear. In Python indenting is important. The indent on this wiki isn't clear so you may need to check the indents


import MySQLdb
msqluser="whatever the msysql user is"
password="The user password...not secure but tough"

infileName="paramraw.txt"
try:paramsfd=open(infileName=,"r")
except:print "Input filei " +infileName+ " not found"

#Connect to the mysql database
db = MySQLdb.connect("localhost",mysqluser,passwd = password)
cur=db.cursor()

#Read each line of the data input file and split on the colon delimeters
for tableRec in paramfd.readlines():fields=tableRec.split(":")
values="("for item in fields:values=values+","+item
sqlcmd="insert into `de_Coordonnee` (`ID`, `TYPE`, `POSIN`, `POSOUT`, `COORDET`, `COORDETOUT`, `NOTE`, `UTILISATEUR`, `udate`) values"sqlcmd+=values+");"

# Execute the above commandcur.execute(cmd)

[keirvt]

Sorry the indent didn't come out Uhhh
Okay all lines after the first for command are indented once except
the line following the second for command is indented twice