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
816 Views

Similar Articles

[PageSpeed] 13

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:

Import Export Wiazard Unavailable
Hi, I am using Outlook 2003 on Windows XP pro. I am trying to export my emails to a PST file. However when I select "Export to a file" in Import and Export Wizard and click next, nothing happens. I have search the help file and there is this article "If the Import/Export wizard is unavailable, verify that the language or country/region selected in the Regional and Language Options (Microsoft Windows XP) or Regional Options (Microsoft Windows 2000) dialog box in Control Panel matches the Outlook User Interface (UI) language. See Windows Help for more information."...

Converting PDF files into Word
I would like to convert this PDF file I have into a word document so I can edit the file. How can I do this? Wrong forum Melly - this is a Dynamics GP forum. Try an MS Word or Adobe forum. Frank Hamelly MCP-GP, MCT East Coast Dynamics www.eastcoast-dynamics.com Go to www.docudesk.com, it's for free. Next time this is the Microsoft Dynamics GP forum -- GP as in Great Plains, not GP as in General Postings. -- Best regards, -- MG.- Mariano Gomez, MIS, MCP, PMP Maximum Global Business, LLC http://www.maximumglobalbusiness.com "Melly Mel" wrote: > I would like to conve...

can't use backup file
I accidentally removed Microsft Money from my Windows XP system. I re-installed money 2000 and am now trying to restore from my money backup. But keep receiving message that my money.mny file is read only; however, it isn't. Any suggestions on what I may be doing wrong? I may at some point upgraded to Mondy 2001. Is it possible the problem is I'm trying to restore a backup from 2001 and can't do that on Money 2000? Help... Thanks In microsoft.public.money, Paswater wrote: >I accidentally removed Microsft Money from my Windows XP >system. I re-installed ...

Problem with Vista: "Run-Time error '75': Path/File access error"
Hello, I am not sure what I am doing wrong. The fact is that I cannot recreate the problem on my computer, one of my users is struggling with. The problem is that on his computer app crashes with: "Run-Time error '75': Path/File access error" message. I have traced this problem to the point app wants to create folder in: C:\ProgramData\<my app title> That path is obtained using GetSpecialfolder(CSIDL_COMMON_APPDATA) Interestingly, I cannot recreate that crash on my computer. User has Vista Business (32 bit), with Administrator privilages and ...

Error message -- can't open a publisher file from different version
I work on our church newsletter, I have publisher 2000 v. 6, I get an error message "Publisher cannot open files from a different verion". I really need to open the file another member of my team sent me. Anyone have a "free" solution? Convertor or viewer I can copy from to complete my work. Help? Dawn The only "free" solution you have is to ask the originator to save that file as a Pub 2000 file. -- JoAnn Do not meddle in the affairs of cats, for they are subtle and will piss on your computer. --Bruce Graham "Dawn" <dtomczyk@comcast.net>...

Duplicate form
I am trying to copy a form that holds a vehicle's information, it's current assignment, etc... What I need to do is when the vehicle's infor is changed (for example, assignment) I need to copy to duplicate the date and stamp the date of "change" but also keeping the history of where the vehicle was previously assigned. Any help will be greatly appreciated My initial impression is that your table structure is not normalized... that you're storing a separate, complete record for a vehicle for each assignment. Instead, what you should have is a table that holds ...

Excel quits when opening multiple files
Version: 2008 Operating System: Mac OS X 10.6 (Snow Leopard) Processor: Intel When I highlight several (say 10-15) Excel files in a folder and try to open them with a single open command, a few will load and then Excel quits (and automatically relaunches). Is this a bug in Excel? Or am I doing something wrong? Without more information it's hard to say... the size of the files, the nature of their content, the system resources available, whether you're using Spaces, etc. all have an impact on something like this -- not to mention the specific version & update levels of ...

during set up tells me that the ost file is not a offline folder
I just loaded office and tried to set up outlook - one of the first questions it asked something about offline - now it is stuck - every time I open outlook it tells me that this ost file is not an offline folder Try creating a new mail profile in the Mail applet in Control Panel. Pay = attention to the questions asked and take notes so you can come back = here with details if you encounter further problems.=20 --=20 Sue Mosher, Outlook MVP Author of Configuring Microsoft Outlook 2003 http://www.turtleflock.com/olconfig/index.htm and Microsoft Outlook Programming - Jumpstart for...

Forcing imported numbers to text format problem
I'm opening up an HTML table in Excel and Excel is formatting my dat incorrectly, such as turning a code into a date and dropping necessar leading zeros. Is there a way to tell Excel to pull everything as Text? Will thi work in my ASP page that generates the table? I've seen the quick solution of appending a ' onto the data befor import, but I dont want to be appending to the data -- Message posted from http://www.ExcelForum.com ...

depost in wrong account via import
How can my deposits which are listed under one account be changed to a different account? After importing ofx bank transaction data into money 2004 standard, some account deposit series are ending up in the wrong account thx!-Jeff In microsoft.public.money, jmoeller18 wrote: >How can my deposits which are listed under one account be changed to a >different account? >After importing ofx bank transaction data into money 2004 standard, >some account deposit series are ending up in the wrong account >thx!-Jeff To prevent the problem from happening in the future, remove the instit...

Item Import
Can you just import an excel sheet into the ITEM table in HQ, or do other tables get effected which means I would have to use a import utility to populate the other tables automatically I tried it with a test item (importing into HQ) and then ran a 250. The test item did show up in the SO DB. QSCimport seems to populate the associated tables (Supplier, etc.). HTH, Tom -- The worst words in business: "We''ve always done it that way" -- Stop Fishing for eMail. "Jerry" wrote: > Can you just import an excel sheet into the ITEM table in HQ, or do other &g...

file
all docom ent In news:1b1a01c49a36$845c9cf0$a501280a@phx.gbl, cdaley138 <anonymous@discussions.microsoft.com> posted: || all docom ent docom ent file all -- Brian Kvalheim Microsoft Publisher MVP http://www.publishermvps.com ~pay it forward~ This posting is provided "AS IS" with no warranties, and confers no rights. Oh dear! I've developed dyslexia! Letters no longer combine to form real words. -- JoAnn Paules MVP Microsoft [Publisher] "Brian Kvalheim [MSFT MVP]" <bkvalheim@publishermvps.com> wrote in message news:%23fKyarkmEHA.644@tk2msftngp1...

Saving a .PST file
Some time ago didn't Microsoft release a free add-in for O2003 to periodically copy the .PST file to a backup location? I can't find it on Office Download. Does anyone know where it is? Many thanks. Outlook 2003 Add-in: Personal Folders Backup http://www.microsoft.com/downloads/details.aspx?familyid=8b081f3a-b7d0-4b16-b8af-5a6322f4fd01&displaylang=en "Andrew Chalk" <achalk@magnacartasoftware.com> wrote in message news:%23tNSQKahFHA.1244@TK2MSFTNGP14.phx.gbl... > Some time ago didn't Microsoft release a free add-in for O2003 to > periodically copy ...

Excel files reporting read only
I have an issue where a user I provide support for is being told that a file that has not been opened for a couple of months is reporting as being locked for editing by the last user to save the file (in some cases these users no longer exist on our network). When you check the open files on the server, these files are not showing as open. If teh user clicks to close, and then immediatley re-opens the file it opens as normal. There are no temporary files in the folder that the files are located in and I have cleared the temp folders on the client and on the server. The user has full control...

Is WebServices ok to send files
Hi Gurus, I need some advice. We needed to send a file from a client to server once a day. Sometimes file size could be around 100MB. So we added a new routine to our existing Web Service. Then created a windows service to send a file whenever we want. Everything is working good so far. Is this a good approach doing it or there is any other better way doing this. Also wanted to know what is the fastest way to send files using WEB. Thanks in advance for any suggestions. Dan. "Dan" <Dan@nospaml.com> wrote in message news:uo7$f2vjKHA.2188@TK2MSFTNGP04....

How to hide project's implementation files
Hi Group I have a App project and it contains many classes. I want to hand the project to my junior but allowing him only to modify/look in a certain classes only (to meet certain client's spec). I have removed the cpp files that i want to hide and inserted its .obj file only. Since there will be release and debug version conflict i have included the release version only. So that means the project can compile without error in release version Is this approch ok, ? The best way is to make the classes into dll , but i dont have time to do that right now. -Then how can i hide the resource...

can not remove or edit hyperlink
I can't find a way to remove or edit my hyperlink . The right clic shortcut menu does not have those options appearing. -- Thanks to all Running Dell lap C 840 1.6 Ghz, 750 RAM XP PRO Office Bus 2002 You can probably remove by dragging an adjacent cell to that one -- Don Guillett SalesAid Software dguillett1@austin.rr.com "justamailman" <gboudrau@ntl.sympatico.ca> wrote in message news:eyZugMI%23GHA.2180@TK2MSFTNGP05.phx.gbl... >I can't find a way to remove or edit my hyperlink . The right clic shortcut >menu does not have those options appearing. &g...

No Reading Pane Text Size????
Where is the font selection / text size control for the Outlook 2003 reading pane (incoming emails)? I just bought a brand new dell with windows xp and all and I can't even increase the text size of incoming emails as I could with older versions of Outlook Express. I have been trying to figure this out for around 15 hours now and have come up empty. What kind of progress is this anyway???? A program rendered unable to accomplish such a simple practical thing like that!! Im very upset. My vision is poor. Unless I get an answer soon, I am considering a return of this unit to dell...

Adding sum of text
Example: A B C a 1 abcd b 2 c 3 d 4 e 5 f 6 g 7 We would like C1 to equal the amounts of B1:B4 when we type letters "abcd." Hi! Depends on how representative of the reality your little model is. As it stands, a single formula placed in, say, D1 will calculate th equivalence of abcd in C1 =VLOOKUP(MID(C1,1,1),TB,2,0)+VLOOKUP(MID(C1,2,1),TB,2,0)+VLOOKUP(MID(C1,3,1),TB,2,0)+VLOOKUP(MID(C1,4,1),TB,2,0) (entered as a single line). where TB is the Name of the range A1:B7. This will only deal (for obvious reasons) with exactly 4 letters in th string in ...

Microsoft Money 2003 File Menu
I have old Money files that I no longer need.I deleted these files but the file names still appear at the bottom of the Money File Menu.Is there some way to remove the file names from the File Menu? Thank you; In microsoft.public.money, RobJ wrote: >I have old Money files that I no longer need.I deleted >these files but the file names still appear at the bottom >of the Money File Menu.Is there some way to remove the >file names from the File Menu? You could open some other files so they become the most recently used. Or you could searc...

Table Field Data Type
I have a table called Equipment. I also have a report that displays all of the information in the Equipment table. When the report is opened a form pops up allowing the user to make a selection from one of four different combo boxes. After the selection is made and the user clicks Set Filter, the report is filtered to only show records based on the users selection. The problem I have is with a field in the Equipment table. It's an Item Number field with a data type set to Number. This form will only filter text data & I don’t know how to change that (there must be an easier way....

Removal/Edit of post
OK, I know this is not a question specific for this group, but since I can't find another place to ask this, I'll take a chance here In a week moment I was unaware of what I was doing which lead to a post with my mail adress visible and we all know what this leads to (SPAM). So, now I wonder how I get my post removed/edited to let me get rid of those SPAMS.. /Rickard Too late - within a few seconds your post was sent out to thousands of news servers all over the world - from all of which the Swen virus can presumably harvest your address, and none of which are obligated to honor a...

Bettier importing and more fields to map in POS
It would be nice to be able in import more field items than the ones you offer in your import wizard in the POS version of the software. ---------------- This post is a suggestion for Microsoft, and Microsoft responds to the suggestions with the most votes. To vote for this suggestion, click the "I Agree" button in the message pane. If you do not see the button, follow this link to open the suggestion in the Microsoft Web-based Newsreader and then click "I Agree" in the message pane. http://www.microsoft.com/Businesssolutions/Community/NewsGroups/dgbrowser/en-us/defa...

Importing text files to Excel
Does anyone know how to stop Excel 97 from assigning a date format to a column of dates imported from a text file? I want it to leave the format as "general". ...

Removing mainframe border
Folks, I would like to remove the border of my mainframe window in my MDI application. Essentially the look will be very unlike that of typical windows, no frame, no border, no caption, no menu - just black background. I have been successful in removing most of the stuff except the border surrounding mainframe. I have tried a few things so far which obviously have not worked. Setting the style and dwExStyle in the PreCreateWindow(), cs.style = WS_VISIBLE ; cs.dwExStyle &= ~WS_EX_CLIENTEDGE ; Removing the WS_EX_CLIENTEDGE in OnCreate(), ModifyStyleEx(WS_EX_CLIENTEDGE, 0); Obviously...