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

 

[Pat Hartman]

The problem is that your file has no easily identifiable structure. You have two different record types and the only way I can think of to distinguish them is by looking at the third field in a record. If it is numeric, the row is a "product" row. Otherwise, it is a "customer" row.

You'll need to modify the code to store the customerID from each customer record because it is one of the fields that you need to include when building the products table. So your second insert can't be just the three fields from the "product" record it needs to be CustID, ProductID, ProductName, and Qty. Otherwise, there will be no way to relate the orders together.

 

[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.