Split CSV into multiple tables

[hardy1976]

Hi,

I have a CSV file that the system is producing; it is one file with multiple sections in it eg...

[Names]
001,"John Doe"
002,"Jane Doe"
003,"An Other"
[Address]
001,"63 Huntly Street","CRANMORE","BA4 7HA"
002,"52 Southern Way","NORTH QUEENSFERRY","KY11 3YZ"
003,"20 Scrimshire Lane","ASKETT","HP27 2AS"
[Orders]
001,"0001A","Screwdiver",1,8.99
001,"0001A","hammer",1,15
001,"0001A","no 3 screws",1,5
001,"0001A","drill",1,49.99
etc etc etc

The file is huge with many sections (eg Names, Address) in it. What I need to do is load it into Access for some analysis.

Is there a way I can split the file one loading to the system into multiple tables?

Any one got an ideas on approach? HELP!

Regards
H

 

[pr2-eugin]

You might be able to read the file line by line and get the desired bits.. Look here for some sample idea, but might need to work on it a lot more..
http://www.access-programmers.co.uk/forums/showthread.php?t=257757

 

[hardy1976]

Right... I've got that running thanks for the code... however...

Some of the addresses seem to have commas in them, which means split is being thrown out how do I get around this? Can I get Split to look for commas outside speech marks?

[Address]
001,"63, Huntly Street","CRANMORE","BA4 7HA"

In the above example I want to ignore the comma after 63.

Thanks

 

[pr2-eugin]

Just a guess, for Address data, you can look for the String ," instead of just , ?

 

[namliam]

No, withing the filespec that you use you should define " as a text marks, that should keep the address unaffected.

Hmz, looking at the link you dont use file specs, sorry trigger happy, do as Paul said

 

[hardy1976]

Just a guess, for Address data, you can look for the String ," instead of just , ?

Paul - so..

x = Split(txt, ",")

becomes

x2 = Split(txt, ","")

Is that right (I guess I need x and x2 to cover both split options?

then I assume I can use something like..

address1 = x2(1)

 

[pr2-eugin]

Yes, although it would be..

x2 = Split(txt, ",""")

 

[namliam]

What about ", at the end then?

I would be tempted to "simply" import the complete file using a file spec into a dummy table then having some code to either
1) Fill down the record types, then use append queries to send them to the target tables
2) read the records from the imported table and use VBA to send them to the target tables

Kindoff depends on the way I feel on the day and perhaps on the number of target tables.
And/or how "correct" the namings records are, i.e. what is the name of the table for each of the "groups"

 

[hardy1976]

Thanks again... this is getting a bit funky now...I have...

001,"63, Huntly Street",,,,"BA4 7HA"
001,"54 High Street","CRANMORE","KENT",,"BA4 7HA"
001,"67 Green Street","CRANMORE",,,"BA4 7HA"

hmmm never easy...

 

[pr2-eugin]

Can you show the code you have implemented, with a sample .txt file?

 

[namliam]

Couldnt really resist, pretty simple code...
All you need is this:

Sub test()
    Dim rs As DAO.Recordset, rsTarget As DAO.Recordset, i As Integer
    
    DoCmd.TransferText acImportDelim, "Sample Import Specification", "tblSample", "D:\Sample.txt", False
    Set rs = CurrentDb.OpenRecordset("tblSample")
    
    Do While Not rs.EOF
        Debug.Print rs.Fields(0), rs.Fields(1), rs.Fields(2), rs.Fields(3), rs.Fields(4)
        If 1 = 2 Then
            ' this will never execute just because I love nice formatting :)
        ElseIf rs.Fields(0) = "[Names]" Then
           Set rsTarget = CurrentDb.OpenRecordset("tblNames")
        ElseIf rs.Fields(0) = "[Address]" Then
            Set rsTarget = CurrentDb.OpenRecordset("tblAddressInformation")
        ElseIf rs.Fields(0) = "[Orders]" Then
            Set rsTarget = CurrentDb.OpenRecordset("tblOrdersMadebyNames")
        Else
            rsTarget.AddNew
            For i = 0 To rsTarget.Fields.Count - 1
                rsTarget.Fields(i) = rs.Fields(i)
            Next
            rsTarget.Update
        End If
        rs.MoveNext
    Loop
    rs.Close
    rsTarget.Close
    Set rs = Nothing
    Set rsTarget = Nothing
End Sub

Since it dont do much special I down converted the attached database to 2000 hoping that noone/no-one/no one/nobody is using 97 or earlier anymore.

just make sure that you change D:\Sample.txt to the actual file, but you should get the basic idea from this.
Also note that tblAddressInformation, I actually changed column names, so you can change them to what ever you see fit and are not bound to FIELD1, FIELD2 etc.

 

[hardy1976]

Paul - the code is as per your post, but with..

x = Split(txt, ",")
z = x(0)
a = x(1)
b = x(2)
c = x(3)
d = x(4)
e = x(5)

Then the sql to insert the data into the table - I have a section of code for each heading (names, number, orders etc)... If I could get names working in this format I can crack the rest of it...

Mailman - I dont think that approach will work dues to the varying sections, happy to stand corrected though.

 

[namliam]

Mailman - I dont think that approach will work dues to the varying sections, happy to stand corrected though.

Did you actually look at the database and actually tried it? I tested it with the sample of information from your first post.

 

[hardy1976]

Did you actually look at the database and actually tried it? I tested it with the sample of information from your first post.

Mailmail looked at the code and thought it would simply load the whole file (hence the ?)... I will try it now.

 

[hardy1976]

ps - Sorry - should have tried it.

 

[namliam]

It does load the whole file that is the point, but then splits it to 3 tables after ...

Come to think of it you will need to add:

Currentdb.execute "Delete * from tblSample"

As the last line of the code (just before End Sub) to clear out the temporary data.

 

[hardy1976]

Got it working -that is very clever - and a much simpler way of doing it. THANKYOU Mailman

 

[hardy1976]

It does load the whole file that is the point, but then splits it to 3 tables after ...

Come to think of it you will need to add:

Currentdb.execute "Delete * from tblSample"

As the last line of the code (just before End Sub) to clear out the temporary data.

Yes thanks - got those bits

THANKYOU AGAIN

 

[namliam]

Remember to compact your database, since you are populating records (taking up space) and then deleting them again, access doesnt free up the data untill you do an actual compact, otherwize you run the risk of having a 1 gig database with only 100 mb of data in it.

This is called "Bloating" of your database.

 

[namliam]

I hope you been able to adjust the file spec and tables to suite your needs of the more columns in your attached file vs your sample at the start of the thread?

Oh please take note, this process only works if your target table has less or the same amount of fields as your import. If you need more fields in your target (for some reason) you need to adjust the For... Next loop a bit.:banghead: