Import File With Multiple Delimiters

[John W]

I've posted this question before and gotten some good advice but I'm still
having trouble getting this to work. I am trying to import delimited files
that have different types of separators. The type of separators will
identify what table the data should go in. This is actually for ANSI health
care claims if anyone is familiar with them. The files will all be one
continuous line of text. The data will be something like this:

[Table1]field1*field2*field3:~CLM(start
table2)field1*field2*field3*CLM(table2,nextrecord)...etc

Sometimes the seperator will be a symbol (* : ~) and sometimes it will be a
set of text ("GS","HL",etc)

Can someone give me an example of how to use VBA to look for certain text
and then import data to the correct table while at the same time watching to
make sure the text doesn't meet the qualifications to go to the next table.
I've done research on this but haven't come across any examples that really
fit what I'm trying to do.

Thanks for the help!

 

[Dale Fye]

The first thing I would do is use the split command to parse the string into
substrings based on whatever the Text is that Starts the various tables. You
could also do this using the instr( ) function to find the breakpoints
between the table data.

Then, I would parse each of these individual strings based on the character
combination that defines the end of a record (so you are only working with a
single record at a time), and then by the character that splits it into
fields. Ultimately, you will have an array that contains the values for the
appropriate fields.

Then, I would have a separate set of subroutines( one for each table) that
accepts an array of values, and inserts them into the appropriate table,
after making the appropriate transformations (string to date, string to
integer, string to decimal).


HTH
Dale

 

[John W]

I've never used the split command before...reading up on it I don't see why
it wouldn't work. I'll have to play around with it. Thanks!

The first thing I would do is use the split command to parse the string
into
substrings based on whatever the Text is that Starts the various tables.
You
could also do this using the instr( ) function to find the breakpoints
between the table data.

Then, I would parse each of these individual strings based on the
character
combination that defines the end of a record (so you are only working with
a
single record at a time), and then by the character that splits it into
fields. Ultimately, you will have an array that contains the values for
the
appropriate fields.

Then, I would have a separate set of subroutines( one for each table) that
accepts an array of values, and inserts them into the appropriate table,
after making the appropriate transformations (string to date, string to
integer, string to decimal).


HTH
Dale

--
Don''t forget to rate the post if it was helpful!

Email address is not valid.
Please reply to newsgroup only.

I've posted this question before and gotten some good advice but I'm
still
having trouble getting this to work. I am trying to import delimited
files
that have different types of separators. The type of separators will
identify what table the data should go in. This is actually for ANSI
health
care claims if anyone is familiar with them. The files will all be one
continuous line of text. The data will be something like this:

[Table1]field1*field2*field3:~CLM(start
table2)field1*field2*field3*CLM(table2,nextrecord)...etc

Sometimes the seperator will be a symbol (* : ~) and sometimes it will be
a
set of text ("GS","HL",etc)

Can someone give me an example of how to use VBA to look for certain text
and then import data to the correct table while at the same time watching
to
make sure the text doesn't meet the qualifications to go to the next
table.
I've done research on this but haven't come across any examples that
really
fit what I'm trying to do.

Thanks for the help!