Import Delimited Issues

[Steven547]

I have a txt file that I need to import into access. The problem, is that the quotes in the text file are causing the issue. The txt file looks like this: (sorry for the length)

.BROEFS2_TEAMS2_ADMIN_RW.GROUPS.LAO.NA.CAPGROUP, "CSSC.GCAT.CGTC.LAO.NA.CAPGROUP
DAK.GCAT.CGTC.LAO.NA.CAPGROUP
ELSR.GCAT.CGTC.LAO.NA.CAPGROUP
LME.GCAT.CGTC.LAO.NA.CAPGROUP
MMJK.GCAT.CGTC.LAO.NA.CAPGROUP
MPN.GCAT.CGTC.LAO.NA.CAPGROUP
SMD.GCAT.CGTC.LAO.NA.CAPGROUP
TCAR.GCAT.CGTC.LAO.NA.CAPGROUP
VMG.GCAT.CGTC.LAO.NA.CAPGROUP"
.LAO_TEAMS3_MKTGSVCS_ARCHIVES_RW.GROUPS.LAO.NA.CAPGROUP, LKF.AFD.LAO.NA.CAPGROUP
.BROWFS16_DATA_SPARK3_CM_BUILD_RW.GROUPS.LAO.NA.CAPGROUP, "EYC.DEV.CDS.LAO.NA.CAPGROUP
EYC.DEV.CDS.CGBROW.BROW.NA.CAPGROUP
PESW.DEV.CDS.LAO.NA.CAPGROUP"
.BROWFS16_DATA_SPARK3_CM_STAGING_RW.GROUPS.LAO.NA.CAPGROUP, "EYC.DEV.CDS.LAO.NA.CAPGROUP
EYC.DEV.CDS.CGBROW.BROW.NA.CAPGROUP"

Here is how it is supposed to be separated: The .broefs2 (first line) is the GROUP. The lines contained INSIDE the quotes are the members associated WITH that group. So on and so forth. How can I import this into Access so it represents correctly? When I do a delimited with quotes, it doesn't place the members in quotes in the proper column... Any ideas? Thanks.

 

[jsteph]

Steven,
It looks like there's no comma after the second dblquote. This could confuse Access. Are you saying that what's inside the quotes should logically be a child-table to the Group records?

If so, this looks like a job for some manual parsing. If not, and the file is predicatable in format, ie, the second (closing) quote is always directly followed by a dot, then you could do a find/replace on the file prior to import, and replace ". with ",. which would put the stuff inside the quotes in it's own field, but still each individual item within that field would need to be parsed to a sub-table (if I understand your data correctly).
--Jim

 

[Steven547]

Correct. What is inside the quotes, should be the "child" of the "non" quoted text. So basically:

"CSSC.GCAT.CGTC.LAO.NA.CAPGROUP
DAK.GCAT.CGTC.LAO.NA.CAPGROUP
ELSR.GCAT.CGTC.LAO.NA.CAPGROUP
LME.GCAT.CGTC.LAO.NA.CAPGROUP
MMJK.GCAT.CGTC.LAO.NA.CAPGROUP
MPN.GCAT.CGTC.LAO.NA.CAPGROUP
SMD.GCAT.CGTC.LAO.NA.CAPGROUP
TCAR.GCAT.CGTC.LAO.NA.CAPGROUP
VMG.GCAT.CGTC.LAO.NA.CAPGROUP"

Should be associated with:
.BROEFS2_TEAMS2_ADMIN_RW.GROUPS.LAO.NA.CAPGROUP

So "column A" would be the .Broefs2 and "columnB" would be the "quoted text".

 

[lespaul]

so you have all the quoted information in a single field? is a single piece of information? looks like multiple pieces of information.....if so a more normalized solution would be to have each of those groups inside the quote be associated with the parent:
[tt]
parent child
BROEFS2_TEAMS2_ADMIN_RW.GROUPS.LAO.NA.CAPGROUP CSSC.GCAT.CGTC.LAO.NA.CAPGROUP
BROEFS2_TEAMS2_ADMIN_RW.GROUPS.LAO.NA.CAPGROUP DAK.GCAT.CGTC.LAO.NA.CAPGROUP
BROEFS2_TEAMS2_ADMIN_RW.GROUPS.LAO.NA.CAPGROUP ELSR.GCAT.CGTC.LAO.NA.CAPGROUP
BROEFS2_TEAMS2_ADMIN_RW.GROUPS.LAO.NA.CAPGROUP LME.GCAT.CGTC.LAO.NA.CAPGROUP
BROEFS2_TEAMS2_ADMIN_RW.GROUPS.LAO.NA.CAPGROUP MMJK.GCAT.CGTC.LAO.NA.CAPGROUP
BROEFS2_TEAMS2_ADMIN_RW.GROUPS.LAO.NA.CAPGROUP MPN.GCAT.CGTC.LAO.NA.CAPGROUP[/tt]

Leslie

Have you met Hardy Heron?

 

[Steven547]

That's the idea, but the way the txt file was given to me was the way I show above. I was trying to replace the ( ". ) with ( ",. ) as also mentioned, but ms word is not allowing me to do that, probably because of the period.

Trying to associate those with the parent is what my ultimate goal is. This file is coming from Novell.

 

[jsteph]

Steven,
Word should make the swap no problem...however, that depends on line-breaks. I copied your sample data from this post and found line-breaks, which can be replaced using the below as the 'find' argument:
"^l.

(dbl-Quote, carat, lower-case L, and period)

So if the original file has linebreaks, use this method. Other linebreaks should stay (not sure offhand how Access differentiates betwean the line-break and the true, two-character Carriage-return/Line-Feed), but each full 'record' should have the CRLF at the end.

Second, once this is imported it's easy to link the parent to child because access should assign a record-id to each record. So when splitting the 'child' field, in your code you'll have the same record id since the child fields are all part of the parent record at this point.
--Jim

 

[Steven547]

I got it working so far. The only issue I have now, is that if I based the REPORT off the actual table, it works. BUT, if I base the report off a QUERY, the query seems to be truncating the data. I'll have to figure out a way to write code for the user so they can select what "company" they want to see with this data, instead of doing it in the query...