Importing dataset and adding counts to entries

[Abuba3000]

Hi All,

I have an extract in csv format that I want to import into a database to be able to use it with other data sources.

In the file I have 3 columns: Company Ref, Company Name, Contact email

If we have 3 contacts for a supplier then there will be 3 lines e.g.

AAA001,xyz,john@xyz.com
AAA001,xyz,emma@xyz.com
AAA001,xyz,chris@xyz.com

What I want to do is when I import the file, is add a count of contact for the supplier, e.g:

AAA001,xyz,1,john@xyz.com
AAA001,xyz,2,emma@xyz.com
AAA001,xyz,3,chris@xyz.com

So I can convert the data into a table to show one line per supplier and a column for contact 1, contact 2, etc. (It may possible to import straight in to this format but I'm not sure).

AAA001 xyz john@[EMAIL="xyz.comemma@xyz.com"]xyz.com[/EMAIL] [EMAIL="emma@xyz.com"]emma@xyz.com [/EMAIL]chris@xyz.com


We have a maximum limit of 12 contacts per supplier on this dataset.

Any help you can provide will be great

 

[UncleCatwoman]

Build a temp table to match the format of your .csv file. For this example we'll call it tbl_TEMP.
Build the permanent table you want, with the 12 contact fields. For this, we'll call it tbl_REAL.

Write your code to do the following:

> Import your .csv file into tbl_TEMP
> Pull tbl_TEMP as a recordset (rsT), sorted by CompanyRef
> Pull tbl_REAL as a recordset (rsR)

Dim strLastCRef AS String
Dim i as Integer
strLastCRef="start"
i = 0

rsT.MoveFirst
Do Until rsT.EOF
' is it a new CompanyRef?
If rst!CompanyRef <> strLastCRef Then
' close the previous record
If strLastCRef <> "start" then rsR.Update
' and start a new one
rsR.AddNew
rsR.CompanyRef = rsT.CompanyRef
rsR.CompanyName = rsT.CompanyName
rsR.Contact1 = rsT.ContactEmail
' reset the variables
strLastCRef = rsT.CompanyRef
i = 1
' but if it's the same company then ...
Else
i = i + 1
rsR.Fields("Contact" & i).Value = rsT.ContactEmail
End If
' move to the next record
rsT.MoveNext
Loop
' when you're done, close the last record
rsR.Update

> clean up and finish

Hope this helps!!

 

[Abuba3000]

Thanks UncleCatWoman.

It's erroring at rsR.CompanyRef = rsT.CompanyRef before it runs with an error message saying “Method or data
member not found”.

Not sure if my DIM and SET are right:

Dim db As DAO.Database
Set db = CurrentDb
Dim rsR As DAO.Recordset
Dim rsT As DAO.Recordset
Set rsT = db.OpenRecordset(tbl_TEMP, dbOpenTable)
Set rsR = db.OpenRecordset(tbl_REAL, dbOpenTable)

 

[UncleCatwoman]

Poop, I made a mistake in my example.

With DAO, the syntax should be rsR!CompanyRef = rsT!CompanyRef. Recordset - exclamation point - field name.

And if you have spaces in your field names, you'll need to put them in brackets. rsR![Company Ref] = rsT![Company Ref]

Sorry for the confusion.

 

[Abuba3000]

Thanks UncleCatwoman it works brilliantly!

Save's me having to prep the file in Excel using offset etc