Consulting

Results 1 to 7 of 7

Thread: matching words from transliteration and replacing using vba

  1. #1

    Question matching words from transliteration and replacing using vba

    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

  2. #2
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    Quote Originally Posted by synthex View Post
    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
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  3. #3
    Quote Originally Posted by mancubus View Post
    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
    18-10-2018 14-51-41.jpg

  4. #4
    VBAX Guru mancubus's Avatar
    Joined
    Dec 2010
    Location
    "Where I lay my head is home" :D
    Posts
    2,644
    thank you.

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

    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.
    PLS DO NOT PM; OPEN A THREAD INSTEAD!!!

    1) Posting Code
    [CODE]PasteYourCodeHere[/CODE]
    (or paste your code, select it, click # button)

    2) Uploading File(s)
    Go Advanced / Attachments - Manage Attachments / Add Files / Select Files / Select the file(s) (multiple files can be selected while holding Ctrl key) / Upload Files / Done
    Replace company specific / sensitive / confidential data. Include so many rows and sheets etc in the uploaded workbook to enable the helpers visualize the data and table structure. Helpers do not need the entire workbook.

    3) Testing the Codes
    always back up your files before testing the codes.

    4) Marking the Thread as Solved
    from Thread Tools (on the top right corner, above the first message)

  5. #5
    Quote Originally Posted by mancubus View Post
    thank you.

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

    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 AsInteger
    rowCount
    =Sheet1.Range("A"& rows.Count).End(xlUp).Row
    rowCount2
    =Sheet2.Range("A"& rows.Count).End(xlUp).Row
    MsgBox rowCount
    MsgBox rowCount2
    For i =2To rowCount
    For j =2To rowCount2
    IfSheet1.Range("H"& i).Value=Sheet2.Range("A"& j).ValueThen
    Sheet1.Range("H"& i).Value=Sheet2.Range("B"& j)
    EndIf
    Next j
    Next i
    EndSub
    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
    Attached Files Attached Files

  6. #6
    Knowledge Base Approver VBAX Guru macropod's Avatar
    Joined
    Jul 2008
    Posts
    4,435
    Location
    Cross-posted at: https://social.msdn.microsoft.com/Fo...vba?forum=vsto


    Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/faq...._new_faq_item3
    Cheers
    Paul Edstein
    [Fmr MS MVP - Word]

  7. #7
    Quote Originally Posted by macropod View Post
    Cross-posted at: https://social.msdn.microsoft.com/Fo...vba?forum=vsto


    Please read VBA Express' policy on Cross-Posting in item 3 of the rules: http://www.vbaexpress.com/forum/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?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •