UltraEdit REGEX Question

[pengu]

I'm sorry I could not see any other places to ask this.  UltraEdit uses a similar method for REGEX for FIND & REPLACE (can also use Unix regex). http://www.ultraedit.com/support/tutorials_power_tips/ultraedit/regular_expressions.html

 

I just need some assistance, I have a data file with some information I want to insert into a database.

 

first_name,last_name,address1,city,postal,

D,Pearce,00 Changed St,PERTH,870,

M,Berghan,00 Changed St,PERTH,872,

 

I want to take the information from this file and change it to this.

 

insert into client_desc (first_name, last_name, Address1, City, Postal, Plate_Number, Email, Phone, Robot_id, description ) values('D','Pearce','00 Changed St','Perth','870','','','','','')

 

The order is not important I can change that myself with queries.

 

Once again I'm sorry if I've posted in the wrong section.

 

An example I came across, http://simonwillison.net/2003/Mar/23/ultraEditRegularExpressions/

[salathe]

Do you really want to un-capitalize the "PERTH" into "Perth"? Is there any need to do this in UltraEdit, with a tiny PHP script you could read the comma-separated file into whatever format you like with whatever text transformations you like.

[pengu]

Do you really want to un-capitalize the "PERTH" into "Perth"? Is there any need to do this in UltraEdit, with a tiny PHP script you could read the comma-separated file into whatever format you like with whatever text transformations you like.

 

No my post is obviously not clear enough.

 

I have this sort of data. 2000 lines or so, in a text document.

first_name,last_name,address1,city,postal,

D,Pearce,00 Changed St,PERTH,870,

M,Berghan,00 Changed St,PERTH,872,

 

I need to insert this information into a database, but due to microsoft being buggy I am unable to just do a straight import.

 

So I need to change all that information into the following. Having insert into client_desc (blablabla) at the start.  Then using all the information into variables and putting them between ' & ' and ending with a bracket.

 

insert into client_desc (first_name, last_name, Address1, City, Postal, Plate_Number, Email, Phone, Robot_id, description ) values('D','Pearce','00 Changed St','PERTH','870','','','','','')

 

Does this make sense, could a PHP script do that?  I really don't want to manually do this for 2000 lines of data lol.

[pengu]

Ok screw it, attached is the information I want to change so I can insert it into a database.

 

[attachment deleted by admin]

[MadTechie]

you mean like this ?

$result = preg_replace('/(.*?)\t(.*?)\t(.*?)\t(.*?)\t(.*)$/im', 'insert into client_desc (first_name, last_name, Address1, City, Postal, Plate_Number, Email, Phone, Robot_id, description ) values(\'\1\',\'\2\',\'\3\',\'\4\',\'\5\',\'\',\'\',\'\',\'\',\'\')', $data);

see attached

 

[attachment deleted by admin]

[pengu]

<?php 
$YourFile = "Client_Desc.txt"; 
$handle = fopen($YourFile, 'r'); 
while (!feof($handle)) 
{ 
$Data = fgets($handle, 512); 
print "insert into client_desc (first_name, last_name, Address1, City, Postal, Plate_Number, Email, Phone, Robot_id, description ) values('" .$Data. "','','','','')"; 
print "<p>"; 
} 
fclose($handle); 
?> 

 

I have this so far, is it possible to put each variable or something into an array? (By variable I mean each peice of data, name , address etc)

 

EDIT:

Thanks Mad Techie, exactly what I was after!

Words can not explain the love I feel for you right now!

[MadTechie]

Welcome (to be honest I end up creating small scripts for SQL cleanup's) but please skim the file as its was just a quick draft!

[pengu]

Could I ask for a moderator to remove the files attached in this thread. Thanks!

[cags]

I'm not being funny, but wouldn't this have been a lot easier by just using fgetcsv then imploding the array with quotes around it??

[pengu]

I'm not being funny, but wouldn't this have been a lot easier by just using fgetcsv then imploding the array with quotes around it??

 

I saw fgetcsv() after, it probably would have done what I needed.