Importing from delimited text file & removing duplicates

  • Thread starter Thread starter tom.hepworth
  • Start date Start date
T

tom.hepworth

Hi
I have a problem which I hope someone can help me with because I
really don't even know where to start with it.
I am using Access 2003. I have a delimited text file which contains
about 200,000 lines. There are only 2 fields, Role and ID. The role
field contains many duplicates, out of the 200,000 records I think
there are only 16,000 unique roles.

Role ID
AB_F2S_COST_PLAN_CLK EE1
AB_F2S_COST_PLAN_CLK LT10
AB_F2S_COST_PLAN_CLK LT18
AB_F2S_COST_PLAN_CLK LT3
AB_F2S_COST_PLAN_CLK LT7
AB_F2S_COST_PLAN_CLK LT8
AB_F2S_COST_PLAN_DSP TR8
AB_F2S_COST_PLAN_DSP TH2
AB_F2S_COST_PLAN_DSP BM23


I need to produce a single unique list of roles with the ID's related
to that role all concatenated together with comma's as separaters.


e.g
Role
ID
AB_F2S_COST_PLAN_CLK EE1,LT10,LT18,LT3,LT7,LT8
AB_F2S_COST_PLAN_DSP TR8,TH2,BM23


Is this possible and if so, how ?
Thanks in advance
 
I would import the data.

It not a at all clear what you want to do with the resulting 16,000 reords?

Do you plan to exrot that list? (and, note that your new list has a "," in
the field...

This is one example in which I would actually import the whole data..

I woul also create a 2nd table with the two fields.


Then I woul run some code to produce hte list. The code is easy to write,
and would look like:
 
sorry...bumped send key....


I would import the data.

It not a at all clear what you want to do with the resulting 16,000 records?

Do you plan to export that list? (and, note that your new list has a "," in
the field...

This is one example in which I would actually import the whole data..

I would also create a 2nd table with the two fields.


Then I would run some code to produce the list. The code is easy to write,
and would look like:

Sub MakeList()

Dim rstFrom As DAO.Recordset
Dim rstTo As DAO.Recordset
Dim strRole As String
Dim strSql As String
Dim strIDList As String

strSql = "select * from tblImport order by Role"
Set rstFrom = CurrentDb.OpenRecordset(strSql)
Set rstTo = CurrentDb.OpenRecordset("tblResultList")

strRole = rstFrom!Role

Do While rstFrom.EOF = False

If rstFrom!Role <> strRole Then
rstTo.AddNew
rstTo!Role = strRole
rstTo!IdList = strIDList
rstTo.Update
strIDList = ""
strRole = rstFrom!Role
Else
If strIDList <> "" Then strIDList = strIDList & ","
strIDList = strIDList & rstFrom!ID
End If
rstFrom.MoveNext
Loop

If strIDList <> "" Then
rstTo.AddNew
rstTo!Role = strRole
rstTo!IdList = strIDList
rstTo.Update
End If

rstFrom.Close
rstTo.Close

MsgBox "done"

End Sub
 
Back
Top