Importing from delimited text file & removing duplicates

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

0
tom
6/14/2007 6:10:35 PM
access.formscoding 7493 articles. 0 followers. Follow

3 Replies
814 Views

Similar Articles

[PageSpeed] 49

Take a look at http://www.mvps.org/access/modules/mdl0004.htm and/or 
http://www.mvps.org/access/modules/mdl0008.htm at "The Access Web"

You do realize, I hope, that you shouldn't be storing that comma-delimited 
list in a table.

-- 
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)


<tom.hepworth@jt-int.com> wrote in message 
news:1181844635.696071.64630@q19g2000prn.googlegroups.com...
> 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
> 


0
Douglas
6/14/2007 6:21:28 PM
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:


<tom.hepworth@jt-int.com> wrote in message 
news:1181844635.696071.64630@q19g2000prn.googlegroups.com...
> 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
> 


0
Albert
6/14/2007 6:35:40 PM
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


-- 
Albert D. Kallal    (Access MVP)
Edmonton, Alberta Canada
pleaseNOOSpamKallal@msn.com


0
Albert
6/14/2007 6:37:00 PM
Reply:

Similar Artilces:

Duplicate messages ++++ HELP....
I am using Outlook 2002. Setup with one email account only as on offline user to 5.5 Exchange. When I do the send/receive via dial up I pull down whatever messages are on the Server down to the .PST file. Yet when I dial in again I get the same messages over and over. Again I only have one email account setup on the laptop. And it is setup to work offline with a .PST file. Everything I have read and found pertaining to this is if you have multiple acounts setup under the send/receive. This is not the case here. Getting tired of Microsofts constant change and lack of documentation of ...

Percentage calculation on text columns
I have a sheet that represents exam candidates, their grades over 3 assignments with an average score, in the final COLUMN there is an indication of whether they have passed or failed in the format "Pass" "Fail". I need a formula that will count the overall group and give a percentage of the group that has failed. Can anyone help? -- Connor ------------------------------------------------------------------------ Connor's Profile: http://www.excelforum.com/member.php?action=getinfo&userid=27970 View this thread: http://www.excelforum.com/showthread.php?threadid=4...

exporting/importing databases
Can someone tell me if it is possible to bring a database created in microsoft access into microsoft publisher 2002. any suggestions, I am trying to create a mailing list and I have about 900 entries to transfer. thanks ...

OE address book
I am using Windows XP and attempting to use Outlook Express 6. I am trying, without much success, to import my Netscape 7.1 address book into OE6. I saved my Netscape address book as an LDIF file, opened OE6, clicked on "import-other address book-LDIF" and selected my saved Netscape address book to import. I receive the message "application error in importer DLL C:\Program Files\Outlook Express\wabimp.dll", and nothing is imported. What am I doing wrong, or do I need to reinstall OE6? How would I reinstall OE6 if I have to? Thank you. K.C. This newsgroup is ...

How do I group text to graphic?
In my document I'm trying to embed a graphic into the text. How do I do this to where when I delete text before the graphic it moves with text instead of me having to redo embedding? BeanieBrain <BeanieBrain@discussions.microsoft.com> was very recently heard to utter: > In my document I'm trying to embed a graphic into the text. How do I > do this to where when I delete text before the graphic it moves with > text instead of me having to redo embedding? Which version of Publisher are you using? -- Ed Bennett - MVP Microsoft Publisher i'm using both 2000 and...

Conditional Opening of Excel file
Hi, I have a complex workbook that relies on various SUMIF's from othe reference workbooks. If these aren't open, then obviously the formula don't work. As it's for someone else; I want a piece of VB so that when they *open the main workbook, it checks to see if the ref file is open, and i not, it automatically opens it for them. A bit like this sub worksheetopen '(Not sure where this should go in parent book) if workbook("Referencefile.xls") is NOT open then workbook("referencefile.xls").open endif endsub Any ideas folks?:confused -- Message p...

corrupt OTM file/project
hi I have rpeivously posted her asking about how to recover the code from an OTM file that won't start in Outlook VB editor. To be honest I am activelly looking for a solution since there was a lot of code in it, and without any notice, the VB editor stopped working.� Currently I am very disappointed at MS because of their lack of support for this kind of problem. We, in our company, are seriouslly reconsidering our investment in MS products that we had palnned for the near future and honestly I do not know what to think or expect. Does anyone know a way to repair or retrieve cod...

Importing of Gift Card numbers and balances
I have a customer that has a list of Gift Card numbers and balances from their old POS system. Is there a way to import those balances and numbers as vouchers? Or is there an alternate way to use these existing Gift Cards? Thanks in advance. Phil ...

Importing Addresses/Contacts from Outlook 2000 to 2002
ok...I have a new laptop running Windows XP, and Office 2002, including Outlook 2002. My old laptop is running Windows 2000. I have reinstalled Windows 2000 several times on many machines, and each time all I have had to do was replace outlook.pst on the new install with my back- up'd version, and everything worked fine. Now on my XP machine, I did the same thing (overwrote the existing-new outlook.pst) by putting my old outlook.pst file from Windows 2000 into the proper location on my new (XP) laptop. EVERYTHING worked perfect (old email is all there, Contacts show up, folders...

Importing messages from Outlook 2003 into 2000?
I have Outlook 2003 on my computer at work and Outlook 2000 on my computer at home. I would like to move some messages from my work to my home machine. Unfortunately, Outlook 2000 doesn't seem able to understand the .pst files generated by Outlook 2003. Is there any way around this? Colin Make sure you create and copy items into a legacy (Outlook 97 - 2002) pst file. See File | New | Outlook Data file. Pick the Outlook 97 - 2002 type. Copy items form mailbox to this new pst. Close it when done (right click on personal folders and select close/disconnect.) "Colin Caulkins&...

Can an Excel file be converted into Visio?
Does anyone know if this is even possible? Is there a converter pack or a website that would illustrate this process? Thanks, Jim ...

Import Vista Winmail into Outlook 2007
Please advise how I can import Vista WinMail emails and contacts into Outlook 2007? Thanks Malcolm Both apps need to be installed & configured on the same PC Export msgs from Mail to Outlook In OL Import Contacts (Though you may have to export Mail contacts to a scv file, then import that into OL) "Buchanan Family" <buchann@iimetro.com.au> wrote in message news:245F49C7-A7B1-455B-9E12-33CA1372C66E@microsoft.com... > Please advise how I can import Vista WinMail emails and contacts into > Outlook 2007? > Thanks > Malcolm > All works, Thanks Malcolm ...

Get the file path from Textbox
hello i want to get the path of file from textbox and then attach it to already opened window(it would be a Plugin)i am unable to get is it possible i give file path in Textbox and on clicking a button on form say "Attach it",it attacch file on mail window? thanks -adnan -- Message posted via http://www.officekb.com ...

How to concatenate multiple values in text box
I'm trying to code a control source for a text box to concatenate and handle various possible values. There are three fields I need to handle: For example, if the values for the three fields are ... Person_Relation = "Parent" Person_Relation_Other = <NULL> Person_Relation_Type = "Biological" .... my text box should show: Parent :Biological; (The ":" and ";" will be replaced by "(" and ")" but for now it's easier to use something other than parentheses for testing.) If instead the values for the three fields are...

Error on Importing Access Query
I am trying to import Access Query to Excel so I can generate pivot table and chart with live data. However importing external data reads column header only, no values. I have done this before, a while ago. I don't understand why this is not working. Can somebody help me? ...

import vs include
So the difference between import and include is that: import = used for binary library like DLL or .Lib files. it's very similar to include that it load all the header (function definition) from the DLL file so that we can use the header file just like include include = just include header (source file) ========================== #import "c:\Program Files\Common Files\System\ADO\msado15.dll" \ rename("EOF", "EndOfFile") worlman385@yahoo.com wrote: > So the difference between import and include is that: There is no "import" in S...

Removing Letters and Dashes
I learned here from one of you geniuses a nifty trick of how to display only a number when there are also letters in the cell. I learned that if I have in cell A1 the value of =93P100C=94 and, I type in another cell, =3DLOOKUP(9.9E+307,--LEFT(MID(A1,--LEFT(MIN(FIND ({0,1,2,3,4,5,6,7,8,9},A1&"01=AD23456789"))),LEN(A1)),ROW($1:$99))), the result will be 100. I would also love to be able to do the following: 1) If cell A1 is =93A22k55-77=94, I would like the formula in another cell to return the value 225577 (all the numbers in cell A1) 2) If cell A1 is =93A22k55-77=9...

Open XML File Converter
Version: 2004 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel Any word on an update to this converter that resolves the "There is not enough memory or disk space to convert this document" bug on SL? I've seen it reported elsewhere so I'm assuming it's a known issue, but can't find any relevant info here. Thanks. On 11/2/09 2:33 PM, in article 59b7fd3a.-1@webcrossing.caR9absDaxw, "elliotcolbert@officeformac.com" <elliotcolbert@officeformac.com> wrote: > Any word on an update to this converter that resolves the "There is not e...

Need to upgrade CRM but only have MSCRM database file
Does anybody have any suggestions how to upgrade crm 1.2 to crm 3.0 when all you have is the MSCRM and the METADATA database files from the crm 1.2 install? I assume you mean you have lost the active directory it was installed on with version 1.2? In this case you would be forced to extract data out of these databases with code and import to a new CRM ============================== John O'Donnell Microsoft CRM MVP http://www.crowechizek.com/microsoft "John Steskal" <JohnSteskal@discussions.microsoft.com> wrote in message news:9E2FC01F-E422-49CD-A808-66D5A02B4E59@...

Lost data on import
Excel 2002 New web query downloads the string "<10E6/L" OK but on import creates a blank cell. However the string "<10E6\L" imports OK. I've tried adjusting properties and formatting options without success. The following web query url demonstrates the situation: http://www.rcpaqap.com.au/micro/dataentry/testquery.cfm Any ideas on the cause and solution would be appreciated. Alban I saved a webpage in Excel from your data (Cut and paste) and have saved it here. It appears to work for me http://www.zen34775.zen.co.uk/Book1.htm (Link will be removed sho...

How can I type more than one line of text into a cell in Excel?
I am using Excel to organize contact information, names, addresses, phone numbers, etc. I would like all of this information in one cell and in the standard format of: name address phone etc. I do not know how to make Excel accept more than one line of text, unless I cut and paste it from a Word document. Could someone please tell me how to format the cells so that more than one line of text can be accepted in a cell? Hi use ALT+ENTER for inserting line breaks -- Regards Frank Kabel Frankfurt, Germany "watermark" <watermark@discussions.microsoft.com> schrieb im Newsb...

Visual Studio can't open resource file in Resource View after IDE crash
Hello, I have a strange problem with Visual Studio .NET 2003, I hope that this is the appropriate newsgroup. After adding an icon resource to my project, Visual Studio crashed, and after restarting it, it was unable to open my project's resource file in the resource view pane. It displays the name of the rc file, but when I click the "+" beside it to open the resources, nothing happens, and the "+" disappears. There is no error message. Restarting Visual Studio and rebuilding my project didn't help. I can open all other projects' resource files without problem...

Importing old data
We have some old data in a DOS program called Q & A. Does anyone know of a way to import this data into Excel or Access? Any help will be most appreciated. Art It may be worth having a look on Google... P "Art" <mfhusa@aol.com> wrote in message news:007e01c37c71$75a397b0$a401280a@phx.gbl... > We have some old data in a DOS program called Q & A. Does > anyone know of a way to import this data into Excel or > Access? Any help will be most appreciated. > > Art ...

File Corruption?
I got a Microsoft Money 2005 "internal error" message, which shuts down the program every time I tried to generate the "Tax-related transaction" report and started shutting down when I accessed "Help" (program, including "help" and other reports had been working fine). MS support said it was a program file problem and to reinstall or upgrade to Money 2006 (at my suggestion as I was considering it anyway). I upgraded to 2006 nd continue to experience the same problem with the tax-related transaction and other reports. MS support now says that it ...

XML mapping file
Guys, I am programming c# and need some source code that works like a mapper? Any ideas where is the best place to find something close to this already implemented? E.g. I need to map a number of fields. Instrumentname IrSwap -> SWAP FXoption -> FXOPT So I would like to store this in an XML file and use in my C# code. One XML file for each field to map.. Regards, Lars Schouw ...