PDA

View Full Version : matching words from transliteration and replacing using vba



synthex
10-18-2018, 03:43 AM
There is txt files
here this structure (C:/partdata.txt)
I can't attach txt file on this forum, so here the link
https://dropmefiles.com/vzgGv


there is another file with transliteration
C:/23.txt

it contains

old new
MAKFA МАКФА
makar макароны
макар. макароны
MAKFA МАКФА
Макар макароны
макар. макароны
мак макароны
Мак макароны
макф макфа
маkфа макфа
kрупа крупа
https://dropmefiles.com/O7VWu

old column is word which must be replaced, but new column is on what word from old column must be replased in partdata.txt in GOODS_NAME column.

example of partdata.txt
ID ITEM_SUM BARCODE QUANTITY PRICE NDS10 NDS18 GOODS_NAME ID_C_REGCODES_CASH_VOUCHER DISCOUNTNAME DISCOUNTSUM
3972 43.99 1 43.99 4 0 2049750 MAKFA Макар.УЛИТКИ 450г 3971 0

GOODS_NAME columns
MAKFA Макар.УЛИТКИ
so Макар. there is in old column of 23.txt and it should be replaced on макароны in partdata.txt from new column.

how to create vba script in separate file 1.xlsx(script must be run from xlsx, cause txt can have 10M rows) that could be match the words from the good_names column of partdata.txt file with the words in the translit file 23.txt,
and if it found the word in good_names column of partdata.txt that there is in the old column of translit file 23.txt, then replace it word on new one from the new column of 23 txt in good_names column of partdata.txt like in my example
.
output
ID ITEM_SUM BARCODE QUANTITY PRICE NDS10 NDS18 GOODS_NAME ID_C_REGCODES_CASH_VOUCHER DISCOUNTNAME DISCOUNTSUM
3972 43.99 1 43.99 4 0 2049750 MAKFA макароны УЛИТКИ 450г 3971 0

mancubus
10-18-2018, 04:41 AM
I can't attach txt file on this forum, so here the link


Attachments
Manage Attachments:
Valid file extensions: accdb bmp csv doc docm docx gif jpe jpeg jpg pdf png pptm pptx txt xls xlsb xlsm xlsx zip

synthex
10-18-2018, 04:53 AM
Attachments
Manage Attachments:
Valid file extensions: accdb bmp csv doc docm docx gif jpe jpeg jpg pdf png pptm pptx txt xls xlsb xlsm xlsx zip


i saw it, but when i tried attach, i got this error
23060

mancubus
10-18-2018, 07:54 AM
thank you.

i (somehow) remember some threads with uploaded txt files. :dunno

can you open the txt file in excel and save as csv.
then try to upload it.

or maybe one of our members who has access to file sharing sites may download and offer a solution.

synthex
10-20-2018, 04:46 AM
thank you.

i (somehow) remember some threads with uploaded txt files. :dunno

can you open the txt file in excel and save as csv.
then try to upload it.

or maybe one of our members who has access to file sharing sites may download and offer a solution.

mancubus
I tried do self, but got the error

Sub test3()
Dim rowCount As Integer
rowCount = Sheet1.Range("A" & rows.Count).End(xlUp).Row
rowCount2 = Sheet2.Range("A" & rows.Count).End(xlUp).Row
MsgBox rowCount
MsgBox rowCount2
For i = 2 To rowCount
For j = 2 To rowCount2
If Sheet1.Range("H" & i).Value = Sheet2.Range("A" & j).Value Then
Sheet1.Range("H" & i).Value = Sheet2.Range("B" & j)
End If
Next j
Next i
End Sub
error in this string

rowCount2 = Sheet2.Range("A" & rows.Count).End(xlUp).Row


For convenience, I copied all two txt files, and then import the data from the two copies into the Excel worksheet separately. I assume that the data of the copy of the /partdata.txt is imported into the first worksheet, and the 23.txt copy of the data is imported into the second sheet.
why didn't work?


here 24.csv=23.txt , I attached

macropod
10-20-2018, 11:40 PM
Cross-posted at: https://social.msdn.microsoft.com/Forums/en-US/7397fb72-6f9e-482f-9414-22dfac740d6d/matching-words-from-transliteration-and-replacing-using-vba?forum=vsto


Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3

synthex
10-21-2018, 01:31 AM
Cross-posted at: https://social.msdn.microsoft.com/Forums/en-US/7397fb72-6f9e-482f-9414-22dfac740d6d/matching-words-from-transliteration-and-replacing-using-vba?forum=vsto


Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq.php?faq=new_faq_item#faq_new_faq_item3

macropod, i am sorry, i didn't know that do cross-post is prohibitedю
but maybe you can help me in this task?