split the imported text in access table

[shenasiB]

Hello, I need you support to split the imported text in access table as follows.

1st word: фсдффд дс дддд ддддДФ ФФФФ
2nd word: 120-0000000007-55
3rd word: -150.00 (as a number)


4th word: 289 (as a number)
5th word: џџџ га ггго xxxxx за yyyyyy
6th word: 670000262-0217 and
7th word: /

The trim is not working in order to have only one space between the word probably due to converting of text in UNICODE UT8.

Please for you assistance. Attached is the txt file and the table in access

all the best

Shenasi

[Welshgasman]

Unless they are always the same length, you are going to struggle?
Plus you have not shown how the data is to start with?

Quite important I would have thought?

[ranman256]

make a SPEC definition

---------------
PREP
---------------
1st, manually import the text file: New data source, From File, Text File,
choose the file
in the 'import screen' ,bottom left corner is ADVANCED
define your fields in the text as an IMPORT SPEC.
SAVE this spec. (i.e: "SpecName")


use this spec name to import text files


now you can make an append query to append the txtFile2Import fields to the target table.

[Welshgasman]

I would Split() on whatever that character is between the data you require.
Then loop through each element of the array and test for that character as Left(,1)
If not that character then move the data to your field, keeping track of which number field it is.

That is not going to work fully as you have that character in amongst the first set of data?
That character is CHR(32)

Edit:
You might get away with replacing chr(32)*3 with chr(32)*2 until no more replacements.
Then replace chr(32) & "-" with CHR(32) & CHR(32) & "-"

Then split() by CHR(32) & CHR(32) and do what I first mentioned.

[shenasiB]

thank you for this guidance, it seems is good to try it. The second line is not working, the error typo mismatch appeared. The space multiplied with 3 times is not allowed. The 3rd line is also not working. Please for further instructions?
strPomosenString = UTF8_Decode(strRecDetail(5))
strText = Replace(strPomosenString, Chr(32) * 3, Chr(32) * 2)
strText = Replace(Chr(32) & "-", Chr(32) & Chr(32) & "-")

[Welshgasman]

No, that was me showing how many characters you need.
In the replace you would have chr(32)&chr32)&chr(32) and the same with the two chr(32) characters.
You will need to loop that replace of 3 for 2 until no replacement occurs, so you will need to assign the replace to another variable and compare to the first variable, then move the data and replace again until they are the same.

[shenasiB]

thank you. Perfect, I came to one space between each word. Now what is further instruction to treat all the 5 words as one word "фсдффд дс дддд ддддДФ ФФФФ" because some time there are 4 or 6 words until to next word which is 120-0000000007-55, the 3rd word is easy because has all has sign of minus. The 5th word will be solved same as the first word.

[Welshgasman]

No, my idea was you need two chr(32) between each element of data.
One Chr(32) is not going to cut it, that is why I suggested two.

I was curious to see if I could do it, so you are lucky, normally I would not do this, unless it interested me.

Sub SpliText(pstrString As String)
Dim i As Integer, iLen As Integer, iNewLen As Integer
Dim strAField() As String


iLen = Len(pstrString)


Do Until iLen = iNewLen
    iLen = Len(pstrString)
    pstrString = Replace(pstrString, Chr(32) & Chr(32) & Chr(32), Chr(32) & Chr(32))
    iNewLen = Len(pstrString)
Loop
pstrString = Replace(pstrString, Chr(32) & "-", Chr(32) & Chr(32) & "-")
strAField = Split(pstrString, Chr(32) & Chr(32))
For i = 0 To UBound(strAField)
    Debug.Print strAField(i)
Next




End Sub

splitext("?????? ?? ???? ?????? ????                                             120-0000000007-55 -150.00            289   ??? ?? ???? xxxxx ?? yyyyyy         670000262-0217          /")

Result, allowing for I do not have those characters.

?????? ?? ???? ?????? ????
120-0000000007-55
-150.00
289
??? ?? ???? xxxxx ?? yyyyyy
670000262-0217
/

You will need to use the Val() function on those numeric fields as Split() returns a string.

However that should get you almost there?
NB: I have just used Debug.Print to confirm the array contents. Up to you to use them correctly.

[shenasiB]

thank you very much. The code is working perfectly.

It is very appreciated.