Merge data from file 1 with data in file 2 ignoring accents

MonWeb · Nov 15, 2008#12008-11-15T07:58+00:00

Hello,

I'm looking for a solution with UltraEdit before to buy it.

Is it possible to do this and how to do it ?

In my database (Mysql) I would like to replace some stuffs

Example :

In my file, I have 5000 rows
zipcode, 'city', 'Right_url_website',
means:
zipcode, 'Chicago', 'www.website-chicago.com',

and in this same file I have 8000 different rows
(id, 'department', 'area', zipcode, 'city', 'Not yet url website', 'address'),
means
(id, 'department', 'area', zipcode, 'Chicago', 'Not yet url website', 'address'),

I would like replace 'No_url_website' by 'Right_url_website'
It means:
Search 'chicago' ('city' = Column 2) if found 'chicago' ('city' = Columns 5) with 'No_url_website' (Columns 6) Replace 'No_url_website' (Columns 6) by 'www.website-chicago.com' ('Right_url_website' Columns 3)

Many thanks in advance for your answer
(I hope my English was not to bad because I'm French

)

Mofi · Nov 15, 2008#22008-11-15T19:47+00:00

Okay, the macro worked on your example files. It is a variant of the macro posted at Replace text in file A with info from file B.

It is important that you have only the 2 files open and the text file has the focus when you start the macro. The main difference is the red code. This code reformats your text file to the format of the sql file.

First it deletes all white-spaces at end of every line and then moves the cursor to start of the first line with an URL. Next it replaces 2 or more spaces with a single space. These 2 actions are just for security.

The 2 following regular expression replaces in UltraEdit syntax are the tricky one. They convert every line to the format used in the sql file.

The reformatted block is copied into user clipboard 9. Because of these changes the text file is always modified after executing the macro. So you can see after macro execution what the regular expresssion replaces have done in the text file. Use File - Revert To Saved or close the text file without saving if you don't want to keep this format. You could also replace the command NextWindow by CloseFile NoSave to close the modified text file without saving inside the macro.

The first regular expression Find command in the loop selects the zip_postal_code and the city in the line from the text file. This string is used for the replace in the loop which searches for that string plus the empty url string '' and replaces it with the whole line from the text file. I thought, it is better to include also the zip postal code for security if two different cities have the same name.

But unfortunately this is not enough to get the correct result in the sql file because in your text file the cities are always completely in uppercase and so the cities in the SQL file would be also completely in uppercase after macro execution. To avoid that I have inserted 2 additional regular expression replaces before and after the loop. The first one before the loop creates a copy of the city names in the sql file at start of every line (column is copied). After the loop this column is moved back to the correct position in the sql file replacing the uppercase city names from the text file.

The macro property Continue if search string not found must be checked for this macro.

InsertMode
ColumnModeOff
HexOff
UnixReOff
Clipboard 9
Top
TrimTrailingSpaces
Find "http"
EndSelect
Key HOME
Find RegExp " +"
Replace All " "
Find RegExp " ^([0-9]+^)$"
Replace All "|^1"
Find RegExp "%^(*^) ^([~|]+^)|^([0-9]+^)$"
Replace All "^3, '^2', '^1'"
SelectToBottom
Copy
NextWindow
Top
Paste
IfColNumGt 1
"
#
"
Else
"#
"
EndIf
Find RegExp "%^(*, '*', '*', '*', [0-9]+, [0-9]+, ^)^('*'^)"
Replace All "^2, ^1^2"
Top
Loop
IfCharIs "#"
DeleteLine
ExitLoop
EndIf
Find RegExp "%*, '*',"
Copy
EndSelect
Key HOME
StartSelect
Key END
Find "^c ''"
Replace All "^s"
DeleteLine
EndLoop
Find RegExp "%^('*'^), ^((*, '*', '*', '*', [0-9]+, [0-9]+, ^)'*'"
Replace All "^2^1"
ClearClipboard
Clipboard 0

MonWeb · Nov 15, 2008#32008-11-15T19:57+00:00

Excellent, thank you so much for your time.
I'm very lucky, you have spent a lot of time for help me, I really appreciate.

Actually in France it's 9.00pm I will try your advises tomorrow morning, and let you know asap if I have understood everything.

Mofi wrote:it is better to include also the zip postal code for security if two different cities have the same name.

In fact no, it's the opposite, in France we can have 5 same zip postal for 5 different cities but not two different cities have the same name

Thanks again

Mofi · Nov 16, 2008#42008-11-16T13:55+00:00

Okay, no problem. The slightly modified macro ignoring the zip postal code. The red lines are the lines where I made changes.

InsertMode
ColumnModeOff
HexOff
UnixReOff
Clipboard 9
Top
TrimTrailingSpaces
Find "http"
EndSelect
Key HOME
Find RegExp " +"
Replace All " "
Find RegExp " ^([0-9]+^)$"
Replace All "|^1"
Find RegExp "%^(*^) ^([~|]+^)|[0-9]+$"
Replace All "'^2', '^1'"
SelectToBottom
Copy
CloseFile NoSave
Top
Paste
IfColNumGt 1
"
#
"
Else
"#
"
EndIf
Find RegExp "%^(*, '*', '*', '*', [0-9]+, [0-9]+, ^)^('*'^)"
Replace All "^2, ^1^2"
Top
Loop
IfCharIs "#"
DeleteLine
ExitLoop
EndIf
Find RegExp "%'*',"
Copy
EndSelect
Key HOME
StartSelect
Key END
Find "^c ''"
Replace All "^s"
DeleteLine
EndLoop
Find RegExp "%^('*'^), ^((*, '*', '*', '*', [0-9]+, [0-9]+, ^)'*'"
Replace All "^2^1"
ClearClipboard
Clipboard 0

MonWeb · Nov 16, 2008#52008-11-16T14:43+00:00

I have spent more than 5 hours without success

Of course the problem is from me not from your code I'm sure

To be more simple I have changed the file (attached)

convert in sql file and deleted ', and replace by : |

Like this, It will be easier for me to understand, and this will give me the possibility to use your code for other things.

Would you like to make a new one for me

For more explanations :
This macro doesn't add the url adress at the right place and I don't understand why.

That's why, I have asked you to make a new macro with less parameters, in this way it will be easier for me to find the right way.

I really want to learn something and discover how to use UltraEdit, I really want to understand and not just get the solution without the possibily to do something else in the future with this software.

Many thank's in advance

Attached ZIP file deleted by Mofi to save privacy.

Mofi · Nov 17, 2008#62008-11-17T18:12+00:00

Please note that the lines

|start|http://mairie.pagespro-orange.fr/charno ... tm|Charnoz|Sur|Ain|end|
|start|http://www.ville-laboisse.fr|La|Boisse|end|
|start|http://www.villette-sur-ain.fr|Villette|Sur|Ain|end|

in file base_with_www17nov.sql are not correct. That must be corrected or these 3 lines will be wrong interpreted by the macro. I used the UltraEdit regular expression search string |start|*|*|*|*| to find those 3 lines with too many | characters.

Because file base_with_www17nov.sql contains the city names now in correct case, the macro is much easier now.

InsertMode
ColumnModeOff
HexOff
UnixReOff
Clipboard 9
Top
Find RegExp "%|start|^(*|^)^(*|^)end|"
Replace All "|^2^1"
SelectAll
Copy
CloseFile NoSave
Top
Paste
IfColNumGt 1
"
#
"
Else
"#
"
EndIf
Find RegExp "%^(|*|*|*|*|*|*|*|*|*|*|*|*|*|^)^(*^)|"
Replace All "^2^1^2|"
Top
Loop
IfCharIs "#"
DeleteLine
ExitLoop
EndIf
Find RegExp "%|*|"
Copy
EndSelect
Key HOME
StartSelect
Key END
Find "^cwebsite|"
Replace All "^s"
DeleteLine
EndLoop
Find RegExp "%^(*|^)^(*|*|*|*|*|*|*|*|*|*|*|*|*|^)*|"
Replace All "|^2^1"
ClearClipboard
Clipboard 0

The same macro again as UEM file (UltraEdit macro code). There is a syntax highlighting wordfile for such files - see Macro examples and reference for beginners and experts. I have added in this version lots of comments to make it easier for you to understand the macro.

Code: Select all

    InsertMode
    ColumnModeOff
    HexOff
/*! This macro uses the UltraEdit regular expression engine. But any other
    engine could be used too if the regular expression search strings would
    be translated for the Unix or Perl regular expression engine. !*/
    UnixReOff
//  Use user clipboard 9 for copying data and as variable storage buffer.
    Clipboard 9
    Top
/*! File "base_with_www17nov.sql" must have the focus. The format
    of the data in this file is:
    
|start|website|city name|end|

    But needed is only the city name and the website in the reverse order.
    So a regular expression replace all is used to transform the data into
    the format:

|city name|website|

    The regular expression search string means following:
    
    % ......... string must be found at start of a line.
    |start| ... just a simple string with no special meaning.
    ^(*|^) .... following |start| 0 or more characters can occur of any
                type except the new line characters CR and LF until the
                character | is found. This string - the website - is
                tagged (stored in a temporary variable 1) for the
                usage in the replace string referenced by ^1.
    ^(*|^) .... same as above, but now finds the city name and stores
                this found string in a temporary variable 2 referenced
                by ^2 in the replace string.
    end| ...... again just a simple string with no special meaning. !*/
    Find RegExp "%|start|^(*|^)^(*|^)end|"
    Replace All "|^2^1"
/*! The data in file "base_with_www17nov.sql" contains the data now
    in the required format. Select all the content, copy it into user
    clipboard 9 and close the file without saving the changes. That
    changes also the focus to the file "base_without_www_17nov.sql". !*/
    SelectAll
    Copy
    CloseFile NoSave
/*! Paste the content of file "base_with_www17nov.sql" now at top of
    file "base_without_www_17nov.sql". The cursor is now at the end
    of the inserted data. If the cursor is not at start of the first
    line of the real content of "base_without_www_17nov.sql", the last
    line of file "base_with_www_17nov.sql" has had no line termination.
    So insert a line termination + character # in an additional line.
    Otherwise insert just a line with character #. !*/
    Top
    Paste
    IfColNumGt 1
        "
        #
        "
    Else
        "#
        "
    EndIf
/*! This regex replace creates a copy of every city name at start
    of every line in file "base_without_www_17nov.sql". See end of
    the macro for the reason why this is done. !*/
    Find RegExp "%^(|*|*|*|*|*|*|*|*|*|*|*|*|*|^)^(*^)|"
    Replace All "^2^1^2|"
    Top
/*! Now run from top of the file in a loop the required replaces. The loop
    is exited when the cursor reaches the special line inserted before
    starting with character #. This line is deleted when reached. !*/
    Loop
        IfCharIs "#"
            DeleteLine
            ExitLoop
        EndIf
/*! Inside the loop first select the |city name| of the current line copied
    from the first file "base_with_www17nov.sql" and copy this string into
    user clipboard 9. !*/
        Find RegExp "%|*|"
        Copy
/*! Stop selection mode, move cursor back to start of the line, start
    selection mode and move cursor to end of the line. Now the whole
    line   |city name|website|   is selected. !*/
        EndSelect
        Key HOME
        StartSelect
        Key END
/*! Use a non regular expression replace to find all occurences of the
    just copied   |city name|   with the dummy text   website|   following
    and replace all occurences from current cursor position to end of file
    with the current selection. So with this method all cities present in
    file "base_with_www17nov.sql" will have then also the website string
    in file "base_without_www_17nov.sql". !*/
        Find "^cwebsite|"
        Replace All "^s"
/*! Replace all does not change the cursor position. So the cursor is still
    at the end of the selected line from first file which is no longer
    needed and therefore deleted. That stops also the selection mode and
    moves the cursor to start of the next line. Of course the cursor is
    always at top of the file after deleting a line. !*/
        DeleteLine
    EndLoop
/*! This regex replace replaces all city names in the city column with the
    original city names at start of the line to avoid changes in the city
    names caused by different spelling of the city names in the first file. !*/
    Find RegExp "%^(*|^)^(*|*|*|*|*|*|*|*|*|*|*|*|*|^)*|"
    Replace All "|^2^1"
/*! All lines from the first file are processed and the special marker
    line is deleted too. So now only the original lines of the second
    file are present, but with the added websites. Clear user clipboard 9
    to free RAM and switch back to the Windows clipboard. !*/
    ClearClipboard
    Clipboard 0

MonWeb · Nov 17, 2008#72008-11-17T19:24+00:00

YOU ARE MAGIC

This is a great help to learn

Thank you so much

I will work on tomorrow morning

and let you know after

We don't need to you use something special in the regular expressions?

Because the ANSI characters with hex code 0xC0 and higher could result in some problems.

Nov 18, 2008#82008-11-18T11:56+00:00

Hello Mister Magic

I have got a strange result with this macro because :
Action is :
Delete |start| and |end| in file : base_with_www17nov.sql
after all informations from file : base_without_www_17nov.sql are paste in file : base_with_www17nov.sql
that's it not more....

The goal is :
Search in file: without_www_17nov.sql colum |cities| and find the same city in file: base_with_www17nov.sql
if the same citiy was found import from file: base_with_www17nov.sql colum |www| and insert into file: base_without_www_17nov.sql into colum|website|
else
if the same city was not found
result= pass over

Maybe I didn't use ultraEdit in the right way ? because this macro doesn't gave me the result that I was waiting.

Mofi · Nov 18, 2008#92008-11-18T16:33+00:00

Special ANSI characters are no problem for the regular expression replaces. They are not modified in any way.

According to your description it looks like you have forgotten to check the macro property Continue if search string not found for this macro. Do this now by opening Macro - Delete Macro/Modfiy Properties, select the macro and check the property. It is important that the macro continues even if a city specified in file base_with_www17nov.sql is not found in file base_without_www_17nov.sql.

I opened again the files "base_without_www_17nov.sql" and "base_with_www17nov.sql" (with the 3 lines corrected). And only these 2 files were opened in UltraEdit. "base_with_www17nov.sql" was the active one - had the focus as I run my macro. The result was file "result_17nov.sql". A file content compare with "base_without_www_17nov.sql" showed me that the websites were correct inserted - see Compare_result.png. But some city names are slightly modified because of different case of some letters.

So I modified my macro again - see the today inserted red lines in my last macro. I have explained already once the technique to keep the city names in file "base_without_www_17nov.sql" even if they are in file "base_with_www_17nov.sql" written slightly different.

I opened again the files "base_without_www_17nov.sql" and "base_with_www17nov.sql" (with the 3 lines corrected). And only these 2 files were opened in UltraEdit again. "base_with_www17nov.sql" was the active one - had the focus as I run my modified macro. The result was file "better_result_17nov.sql". A file content compare with "base_without_www_17nov.sql" showed me that the websites were correct inserted - see Compare_better_result.png. But this time no city name is modified in any way.

So the macro works perfect and produces what you want if I have understood you correct. The method used is not exactly the method you suggest, but my approach is easier for UltraEdit and faster.

The 2 result files and the 2 PNG screenshots are packed in the attached ZIP file (already deleted).

MonWeb · Nov 18, 2008#102008-11-18T17:42+00:00

I have updated your new macro and I've got result

Mofi · Nov 19, 2008#112008-11-19T15:49+00:00

Sorry, but 'e' is a different character than 'é'. The macro ignores differences in case, so 'A' = 'a', 'B' = 'b', etc. for ASCII characters. But the ANSI character 'é' has the hexadecimal byte code 0xE9 and 'e' has the hex code 0x65. For a computer program these bytes are different and therefore the city name is not equal and so "Ambléon" is not equal "Ambleon". Language specific "similarities" are not handled by the search and replace function.

But I found a solution for this problem. In the updated macro hopefully all special ANSI characters in the file "base_with_www17nov.sql" are replaced by their ASCII equivalents before copying the content into file "base_without_www_17nov.sql".

In file "base_without_www_17nov.sql" after copying all city names to start of every line also hopefully all special ANSI characters in the city names in the real city column are replaced by their ASCII equivalents. The city names at start of every line are not modified.

So now the loop searching for a city name in the correct column and replace it with city name + website works with only ASCII characters and therefore it does not matter any more if a city name in the 2 files uses different spellings for characters with accents.

If I compare now file "better_result_17nov.sql" from yesterday with the new result file "even_better_result_17nov.sql", I can see that 35 additional cities have now a website URL in the new result file. (ZIP archive with the 2 files already deleted.)

Don't forget to enable macro property Continue if search string not found for the new macro.

MonWeb · Nov 19, 2008#122008-11-19T17:05+00:00

Hello Mister Magic

YOU ARE THE BEST, you gave me so much help.... thanks a lot.

Your macro is working very well.

the file: base_with_www17nov.sql has 156 row and your macro has past 153 rows in file: base_without_www_17nov.sql

Only 3 is missing and I know why.
Because for example
from the file: base_with_www17nov.sql row 1 colum |Amberieu Bugey|
from the file: base_without_www_17nov.sql row 3 |Ambérieu en Bugey|
|Amberieu Bugey| is different than |Ambérieu en Bugey|

Of course we cannot correct this problem with one macro

Sorry to ask you something more

Do you have in your mind a solution or one possibility to get as a "report" for the row doesn't pasted from the file base_with_www17nov.sql?

The file base_with_www17nov.sql has in reality 10118 rows and I don't have any idea how to check the rows doesn't pasted

in the file base_with_www17nov.sql = 36490 rows

If I have a solution to get as a "report" I will do it by hand myself.

Thanks again

Mofi · Nov 20, 2008#132008-11-20T18:25+00:00

I have made some changes on the ANSI to ASCII replaces to include more special ANSI characters (hopefully correct because I'm not familiar with these characters) and made all replaces now case-sensitive except one, which must ingore the case of letters. Case-sensitive searches are faster.

I have added also a few lines to collect in clipboard 8 which cities from file "base_with_www17nov.sql" were not found in file "base_without_www_17nov.sql". After the main loop a new file is opened and these "not found strings" are pasted into this file. If all cities were found, the new file will be immediately closed without saving. So no new file with strings at end of the macro means all cities found and their websites inserted. Otherwise you can see which cities from "base_with_www17nov.sql" were not found and check these city names.

MonWeb · Nov 22, 2008#142008-11-22T09:49+00:00

Guten Tag Her Magic

The Bug report give me 734 errors

It's not a lot with one file with 11 000 rows, but replace myself by hand 734 rows is not easy....

Fortunately you have found a solution to make this report.

É give us a problem, it doesn't want to do : Find MatchCase RegExp "[ÈÉÊË]" Replace All "E"

Other problems (it's my fault

Many thanks in advance for your futur new help.

Have a nice day

Mofi · Nov 22, 2008#152008-11-22T16:51+00:00

MonWeb wrote:É give us a problem, it doesn't want to do : Find MatchCase RegExp "[ÈÉÊË]" Replace All "E"

I can't reproduce here any problem. For testing I have inserted 'É' in both files on a city and the macro replaced it in both files with an 'E'. You have to find out by yourself what could be the problem. Run the case-sensitive regular expression replace with the UltraEdit engine manually (Ctrl+R) to find out what the problem is.

And the macro is now nearly back to what I have written first. It uses again zip code + city name + website to identify the lines in file 2 which should be replaced by zip code + city name + URL of the city. Of course I could not really test it because base_with_www17nov.sql does not contain any zip code data. So I inserted always the same zip code for all cities and run the macro. The macro produced the correct result for the city with the correct zip code and all other cities were listed in the report file.