Import CSV but splitting the import data into two tables

[wavp]

I have a .CSV file with has two sets of data, for example:

"CustomerID","CustomerName","CustomerAddress"
132,"J Blog","1 London Street,London"
"ProductID","ProductName","Qty"
10056,"13A Socket",3
10008,"Plugs",1


Brake down of the file:

-------------------------------------------------
Customer Information:

"CustomerID","CustomerName","CustomerAddress"
132,"J Blog","1 London Street,London"

Bought:

"ProductID","ProductName","Qty"
10056,"13A Socket",3
10008,"Plugs",1
-------------------------------------------------

What I would like to do is select the csv file and run a script which will import the data in the file - BUT (here is the bit I don’t know would work) - I would like to put the Customer Information data in one table and the things that customer has bought in another table.

Is this possible, if so how would I go about it?

Thanks in advance.

Wilhelm

 

[wavp]

I gave my problem a shot, and pull things from different places and this is what I came up with. What I am stuck with now is how do I tell it to read the lines below "CustomerId" and do the IF statment in regards to the that data to said table. Then locate "ProductID" and do the other IF statment.

Anyway ideas?


Dim db As Database
Dim intFile As Integer, strText As String
Dim varArray() As Variant, strSQL As String

Set db = CurrentDB

intFile = FreeFile()

'Opening the file
Open "C:\FILE.csv" For Input As #intFile
While Not EOF(intFile)
Line Input #intFile, strText

'Split the line into sections to correcpond to cells
varArray = Split(strText, "," )

'SQL to input in to said tables

If ?????? Then

strSQL = "INSERT INTO tblCustomer (ID,Name,Address) "
strSQL = strSQL & "VALUES(varArray(0),varArray(2),varArray(3));"

db.Execute strSQL

ElseIf ?????? Then

strSQL = "INSERT INTO tblBought (ID,Name,Qty) "
strSQL = strSQL & "VALUES(varArray(0),varArray(2),varArray(3));"

db.Execute strSQL

End If

Loop
Close #intFile

db.Close


Cheers, Thanks in advance

Will

 

[Friday]

Build two import specs, CustSpec and ProdSpec. CustSpec skips last three fields, Prodspec skips fields 2 and 3. Two tables, Custbl and Prodtbl. Custbl has 3 fields, CustomerID", CustomerName and CustomerAddress. Prodtbl has 4 fields, CustomerID, ProdID, ProdName and Qty. Call your import from VBA, ref the import spec for each table. Tables can be linked via the CustomerID field. You'll have to allow for removing duplicate CustomerID records.