Convert Text String (Not Uniform) to Access db

[Jk254]

Hello experts,

I am a newbie at writing code which would be helpful in this task.

I have a lot of records in the text string exmple below. Here is the scenario;

The colored char (bolded font) are related to specific fields that I am interested in. The uncolored chars (regular font) are fields I am not interested in.
If I can elaborate:
V1 would be equal to field 1
ABCDEFGHI = Field 2
0012345 = Field 3 and so on
Now here is the problem in some of the text strings there is some additional information that causes the fields not to be in sync. Here is an example. I have 2 text strings but for the Text String 2 there is some additional information colored in yellow chars (italic font) which I do not need and when I use delimiters it throws my fields off sync because it picks up both the field I need In Blue colored chars (Bolded font) and also chars I don’t need (regular font). Also in the Text String 2, APT SUBZERO (italic font) is not there making it more out of sync. Also in Text String 1 the person has no middle initial but “PER*MEM” (Italic Font) which is also causing my fields not to sync up when using delimiter. I believe by using Code or VBA a command can be put to pick the blue colored chars only (Bolded font) in both strings. I am still new at using code and would like some help in figuring this out

My ultimate objective is to convert it in access to an access db table when I can then copy it to excel so that in future when I get a similar file I can run it through the same db using the code already set up. Thank you



Text String1*ABC*Y*18*001*V1*A*E**AC**Y*REF*0F*ABCDEFGHI*REF*1L*0012345*REF*23*XYZ123456*LBC*256*EA*20111215*DTP*473*EA*20120401*DTP*474*EA*20120430*DTP*473*EA*20120501*MN1*IL*1*JANE*DOW*PER*MEM*ZP**TEL*555555555*P3*1234 S SOUTHPOLE APT SUBZERO*APT SUBZERO*N4*SOUTHPOLE*AP*00000**CY*101*DMG*EA*19131213
Text string 2
*ABC*Y*18*001*V1*A*E**AC**Y*REF*0F*ABCDEFGHI*REF*1L*0012345*REF*23*XYZ123456*LBC*256*EA*20111215*DTP*373*EA*20120401*DTP*374*DTP*473*EA*20120401*DTP*474*EA*20120430*DTP*473*EA*20120501*MN1*IL*1*JANE*DOW*E*ZP**TEL*555555555*P3*1234 S SOUTHPOLE *N4*SOUTHPOLE*AP*00000**CY*101*DMG*EA*19131213

[John_G]

I think you are out of luck here. MS Access deals with text, yes, but when reading text from a file or otherwise manipulating it, Access knows nothing about formatting, colour, font, etc about the text. Formatting is a display feature - nothing else. The only way to do that would be to include the formatting information as part of the text, the way HTML does, e.g. <b>This is bold </b> .

What is the source of those long strings? Is there a way of including delimiters (*) even when some data (fields?) is missing? That would at least make all the string the same in terms of fields counts.

John

[help_me_with_access]

is the text REALLY colored or is that just the example that you're using here on the forum?

and hey John, is the <b> tag really still used in HTML? we're at v.5 now for HTML...I thought <strong> was a replacement for that loooong ago!

[John_G]

Help_Me -

Take a look at the source for this web page! The main (constant) parts use <strong>; our messages use <b>. Go figure.

J.

[help_me_with_access]

well then whoever obviously wrote the vbulletin update wasn't being consistent?

perhaps they were more concerned with the XSS security issues, like making sure the logout hash validation worked properly? Not sure why though....it's not like Matrix is storing credit cards numbers here...