Data parsing

[Gina]

I have to parse some data (8000+ records). One field which is giving
me a particular headache is in the following format:

The quick brown fox[space][space][space]Star Wars[space][space]
[space]00:03:20

where [space] represents a space.

This field needs to be split into 3 fields:

The quick brown fox
Star Wars
00:03:20

The first 2 fields are of varying lengths, the third field is always
fixed in length. There is always 1 space between the words in the
first 2 fields and always 3 spaces separating the fields.

Can anyone suggest how to do this?

Thanks

Gina

 

[Stuart McCall]

I have to parse some data (8000+ records). One field which is giving
me a particular headache is in the following format:

The quick brown fox[space][space][space]Star Wars[space][space]
[space]00:03:20

where [space] represents a space.

This field needs to be split into 3 fields:

The quick brown fox
Star Wars
00:03:20

The first 2 fields are of varying lengths, the third field is always
fixed in length. There is always 1 space between the words in the
first 2 fields and always 3 spaces separating the fields.

Can anyone suggest how to do this?

Thanks

Gina

Dim a As Variant
Dim itm As Variant

a = Split(FieldData, " ") 'That's 3 spaces between the quotes
For Each itm In a
Debug.Print itm
Next

will produce in the immediate window:

The quick brown fox
Star Wars
00:03:20

 

[Gina]

I have to parse some data (8000+ records).  One field which is giving
me a particular headache is in the following format:

The quick brown fox[space][space][space]Star Wars[space][space]
[space]00:03:20

where [space] represents a space.

This field needs to be split into 3 fields:

The quick brown fox
Star Wars
00:03:20

The first 2 fields are of varying lengths, the third field is always
fixed in length.  There is always 1 space between the words in the
first 2 fields and always 3 spaces separating the fields.

Can anyone suggest how to do this?

Gina

Dim a As Variant
Dim itm As Variant

a = Split(FieldData, "   ")  'That's 3 spaces between the quotes
For Each itm In a
    Debug.Print itm
Next

will produce in the immediate window:

The quick brown fox
Star Wars
00:03:20- Hide quoted text -

- Show quoted text -

Hey Stuart,

Thanks for the speedy response! I can't check that right now as the
datafile is not to hand but I can see how that would work. How can I
populate the 3 fields, Field1, Field2, Field3. For example could I use
the split function in an update query? What would be the syntax for
that?

Thanks

Gina

 

[Stuart McCall]

I have to parse some data (8000+ records). One field which is giving
me a particular headache is in the following format:

The quick brown fox[space][space][space]Star Wars[space][space]
[space]00:03:20

where [space] represents a space.

This field needs to be split into 3 fields:

The quick brown fox
Star Wars
00:03:20

The first 2 fields are of varying lengths, the third field is always
fixed in length. There is always 1 space between the words in the
first 2 fields and always 3 spaces separating the fields.

Can anyone suggest how to do this?

Gina

Dim a As Variant
Dim itm As Variant

a = Split(FieldData, " ") 'That's 3 spaces between the quotes
For Each itm In a
Debug.Print itm
Next

will produce in the immediate window:

The quick brown fox
Star Wars
00:03:20- Hide quoted text -

- Show quoted text -

Hey Stuart,

Thanks for the speedy response! I can't check that right now as the
datafile is not to hand but I can see how that would work. How can I
populate the 3 fields, Field1, Field2, Field3. For example could I use
the split function in an update query? What would be the syntax for
that?

Thanks

Gina

Hi Gina

Sorry for the delay replying. I think I'd go for DAO to do the job. I'll
assume that each row's data is in a variable called FieldData (one row at a
time):

Dim rs As DAO.Recordset
Dim a As Variant

Set rs = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)

'The following would be executed in some kind of loop
'to fetch each row of data into FieldData

a = Split(FieldData, " ")
rs.AddNew
rs!Field1 = a(0)
rs!Field2 = a(1)
rs!Field3 = a(2)
rs.Update

'Go round the loop again, till finished

rs.Close
Set rs = Nothing

The above is UNTESTED air code.

Hope it helps.

 

[Gina]

news:d3df79bc-dcfb-4c8e-8a51-62bdc26aa35f@i13g2000yqd.googlegroups.com....

I have to parse some data (8000+ records). One field which is giving
me a particular headache is in the following format:
The quick brown fox[space][space][space]Star Wars[space][space]
[space]00:03:20
where [space] represents a space.
This field needs to be split into 3 fields:
The quick brown fox
Star Wars
00:03:20
The first 2 fields are of varying lengths, the third field is always
fixed in length. There is always 1 space between the words in the
first 2 fields and always 3 spaces separating the fields.
Can anyone suggest how to do this?
Thanks
Gina

Dim a As Variant
Dim itm As Variant

a = Split(FieldData, " ") 'That's 3 spaces between the quotes
For Each itm In a
Debug.Print itm
Next

will produce in the immediate window:

The quick brown fox
Star Wars
00:03:20- Hide quoted text -

- Show quoted text -
Hey Stuart,

Thanks for the speedy response!  I can't check that right now as the
datafile is not to hand but I can see how that would work.  How can I
populate the 3 fields, Field1, Field2, Field3. For example could I use
the split function in an update query?  What would be the syntax for
that?

Gina

Hi Gina

Sorry for the delay replying. I think I'd go for DAO to do the job. I'll
assume that each row's data is in a variable called FieldData (one row ata
time):

Dim rs As DAO.Recordset
Dim a As Variant

Set rs = CurrentDb.OpenRecordset("MyTable", dbOpenDynaset)

'The following would be executed in some kind of loop
'to fetch each row of data into FieldData

a = Split(FieldData, "   ")
rs.AddNew
rs!Field1 = a(0)
rs!Field2 = a(1)
rs!Field3 = a(2)
rs.Update

'Go round the loop again, till finished

rs.Close
Set rs = Nothing

The above is UNTESTED air code.

Hope it helps.- Hide quoted text -

- Show quoted text -

Thanks Stuart,

I get the idea. I can make that work.

Gina