How to split text data from table (1 Viewer)

[SachAccess]

I did below changes in the code just to check.

MsgBox Trim$(strValues(intCounter))
'rsWrite.Fields(intCounter) = Trim$(strValues(intCounter))

All 20 values are coming perfectly in MsgBox. No issues there.
However when the For Next ends and macro goes to 'rsWrite.Update' line, it shows bug as Index or Primary Key cannot contain a Null value.

 

[Gasman]

OK, so what field is the PK field?. I would have an autonumber field?

 

[SachAccess]

Hi @Gasman , thanks, added a PK with auto number, it is working now.
Could you please help me with Data type conversion error if you get time.
Getting bug at 'rsWrite.Fields(intCounter) = Trim$(strValues(intCounter))' this line.

'https://bytes.com/topic/access/answers/501632-split-count-text-field
Sub NormalizeTable()
    Dim rsRead As DAO.Recordset
    Dim rsWrite As DAO.Recordset
    Dim strValues  As Variant
    Dim intCounter As Integer
    
    Set rsRead = CurrentDb.OpenRecordset("NewTable", dbOpenTable, dbOpenForwardOnly)
    Set rsWrite = CurrentDb.OpenRecordset("Revised_Table", dbOpenTable)
    
    Do Until rsRead.EOF
        strValues = Split(rsRead.Fields("My_ID"), "|")
        rsWrite.AddNew
        For intCounter = 0 To UBound(strValues) - 1
            'MsgBox Trim$(strValues(intCounter))
            rsWrite.Fields(intCounter) = Trim$(strValues(intCounter))
        Next
        rsWrite.Update
        rsRead.MoveNext
    Loop
    
    rsRead.Close
    rsWrite.Close
    Set rsRead = Nothing
    Set rsWrite = Nothing
    MsgBox "done"
End Sub

 

[Gasman]

Not without seeing the data.
All your data is text, so all your fields except for the autonumber should be text?

Alternative inspect what you are trying to assign? Could there be a field missing on the import?

 

[SachAccess]

Hi @Gasman thanks a lot for the help. Will this help.
Thanks a lot once again.

 

[Gasman]

I doubt it as the records are all the same?
So if the first record was added OK, then all the others would?

Now having viewed the database.....

So why does the Revised table not have the fields to hold the data you are splitting from the New Table that just has records with that data?

How are you planning on populating that table, It is identical to the New table except that you have now added an autonumber field.?

You (or perhaps I) are not grasping what you are trying to accomplish?
What is the point of splitting this data, if you have nowhere to put it?

 

[Cronk]

Create extra fields in your table to contain the split data

Given that rs.fields(0) is My-Serial_number and rs.fields(1) is the field containing the source data, My_ID,

rsWrite.Fields(intCounter) = Trim$(strValues(intCounter))

should read

rsWrite.Fields(intCounter +2) = Trim$(strValues(intCounter))

to put each segment of the original string into each of the new fields

 

[moke123]

Are you trying to do something like this?

 

[SachAccess]

Create extra fields in your table to contain the split data

Given that rs.fields(0) is My-Serial_number and rs.fields(1) is the field containing the source data, My_ID,

rsWrite.Fields(intCounter) = Trim$(strValues(intCounter))

should read

rsWrite.Fields(intCounter +2) = Trim$(strValues(intCounter))

to put each segment of the original string into each of the new fields

Thanks for the help. I tried with rsWrite.Fields(intCounter + 2) = Trim$(strValues(intCounter)) line.
It is giving me bug as Item not found in this collection.

 

[SachAccess]

Are you trying to do something like this?

Hi @moke123 , yes, you are correct, I am trying exactly this.
The code you have provided is generating perfect result.

First I am importing a text file in DB using below module.
I believe I need to add an ID column as auto number field in this table once data is imported.
Table2 in your DB has auto number field, mine does not have it.
But this what exactly I am trying to do. Thanks a lot.

'https://stackoverflow.com/questions/32390960/vba-code-import-a-text-file-into-an-access-table-with-condition
Sub MyTableImport()
    Dim sqlStr As String

    sqlStr = "SELECT * INTO NewTable "
    sqlStr = sqlStr & " FROM [Text;HDR=Yes;FMT=Delimited;Database=D:\MMM\Personal Documents\MyAccess].Sample.txt "
    
    DoCmd.SetWarnings False
    DoCmd.RunSQL sqlStr
    DoCmd.SetWarnings True
    MsgBox "Done!"
End Sub

 

[SachAccess]

I doubt it as the records are all the same?
So if the first record was added OK, then all the others would?

Now having viewed the database.....

So why does the Revised table not have the fields to hold the data you are splitting from the New Table that just has records with that data?

How are you planning on populating that table, It is identical to the New table except that you have now added an autonumber field.?

You (or perhaps I) are not grasping what you are trying to accomplish?
What is the point of splitting this data, if you have nowhere to put it?

View attachment 98021

Hi @Gasman thanks a lot for the help. I added field names in 'Revised_Table'.
However still facing issue.

I am importing a text file in my DB.
Values are like Utilitatis|causa|amicitia|est|quaesita|Utilitatis|causa|amicitia|est|quaesita|Utilitatis|causa|amicitia|est|quaesita|Utilitatis|causa|amicitia|est|quaesita|
I am trying to separate these values using pipe as delimiter.
Just like what we do in Excel using delimiter.
My assumption was, it will be similar to Excel only.
When I run the code, macro creates a table names NewTable with imported data.
I created Revised_Table manually.
I am trying to put all the delimited data in Revised_Table from NewTable.
My apologies for making it confusing.

 

[Gasman]

Access and Excel work completely differently. vba is similiar, but that is about it.
If you have your 20 fields on the revised table, then it should just be a matter of walking through one record and seeing what is produced.
I am down the hospital atm, so will be a while. Hopefully someone else might chip in.

 

[SachAccess]

Hi @Gasman please take care. Thanks a lot for all the help.

 

[Gasman]

Hi @Gasman thanks a lot for the help. I added field names in 'Revised_Table'.
However still facing issue.

I am importing a text file in my DB.
Values are like Utilitatis|causa|amicitia|est|quaesita|Utilitatis|causa|amicitia|est|quaesita|Utilitatis|causa|amicitia|est|quaesita|Utilitatis|causa|amicitia|est|quaesita|
I am trying to separate these values using pipe as delimiter.
Just like what we do in Excel using delimiter.
My assumption was, it will be similar to Excel only.
When I run the code, macro creates a table names NewTable with imported data.
I created Revised_Table manually.
I am trying to put all the delimited data in Revised_Table from NewTable.
My apologies for making it confusing.

OK back from hospital.

I know what you are trying to do, but not why you do not have the required fields in the revised table to hold the data?
Comment out the MoveNext and work on one record and get that working.
Get rid of MyID. You presumably want an autonumber field and Access will handle that for you.
Then create 20 fields to hold the data being split. name them as you want or Field1, Field2 etc

Then you could use something like

        For intCounter = 0 To UBound(strValues) - 1
            'MsgBox Trim$(strValues(intCounter))
            rsWrite.Fields(intCounter + 1) = Trim$(strValues(intCounter)) ' or rsWrite.Fields("Field" & intCounter + 1) = Trim$(strValues(intCounter))
        Next

 

[SachAccess]

Hi @Gasman I hope everything is absolutely fine at your end.
Please give me some time to reply with details.
Thanks for the help.

 

[moke123]

First I am importing a text file in DB using below module.

have you tried the TransferText Method?
https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transfertext

Will you always have the same number of fields? You will need to have enough fields in your table to correspond to the data your processing.
My example will handle any number of segments provided there are enough fields in your table..
Will there ever be null values in the string? like- Utilitatis| |amicitia|. . .
How consistent is your data? Where does it come from?

 

[SachAccess]

Hi @Gasman , please find attached file for your reference. Kindly check if you get time.
I am trying to perform this task. Still there is lot of pending work but this is my primary task.
Thanks.

 

[SachAccess]

have you tried the TransferText Method?
https://docs.microsoft.com/en-us/office/vba/api/access.docmd.transfertext

Will you always have the same number of fields? You will need to have enough fields in your table to correspond to the data your processing.
My example will handle any number of segments provided there are enough fields in your table..
Will there ever be null values in the string? like- Utilitatis| |amicitia|. . .
How consistent is your data? Where does it come from?

Hi @moke123 , thanks for the help.
Yes, number of fields will be same.
At the moment am not sure about null values in the string.
Data is text file and downloaded by the user from a system.

I just realized that type for each delimited field might be different.
It might be text, date or number.
Do I need to consider this as well.

 

[moke123]

Do I need to consider this as well.

Most likely yes, you need to plan for all contingencies.

 

[Gasman]

Not sure what you are doing here with all this code in separate modules?
I'd put everything to do with the import in one module modImport?
if this is almost a finished product then give your buttons meaningful names, not Command5
What is the point of adding an autonumber field to the input table?

I'd just create the table onetime only manually. I'd only start getting deep into creating fields for tables if you get 30 next week and then 45 the week after? IE no consistency in the data.? Plus I have never done that.

You are trying to run before learning to walk here :-(
Small steps, small steps. I find that gets me further in the shortest time and not come a cropper.