Import CSV File From Database PRO to Access: Unusual Record Splitting Problem

[Benjamin Bolduc]

Hello everyone,

I'm trying to import data from our current Database Pro v1.0 DB to an Access DB that I'm creating.

Our DBPRO is essentially a flat-file data entry program. It has a "subform" for history events that isn't actually in it's own table, but all concatenated in a single [History] field.

Basically, when viewed in DBPRO, it's broken into different records, yet it's actually stored as one. DBPRO uses °, ±, □, and 0's to separate the different "fields", but Access can't seem to break it down automatically.

When I export the data to a CSV file, everything else comes through with minimal problems. The [History] field, of course, comes in as a huge block of concatenated records.

I've attached an example of this below. I included only the field in question, ([History]), and the primary key, ([Last Name/Cust]). The first tab in my example is a single record, recently imported. The second tab shows how I need it to be, broken into multiple records.

Is there anyway I can split these records, while maintaining the primary key? It's my goal to have all the other information in one table, and the history records in a separate one.

Thanks so much for your help! I've researched all over, and just can't seem to find a similar problem, or solution.

Ben Bolduc

 

[Benjamin Bolduc]

Hello everyone,

I understand that this is an unusual and difficult problem. If there is anyway I can clarify something to make it easier please let me know.

A push in the right direction is all I can hope for.

Thank you so much for your help, I truly appreciate it.

Ben Bolduc

 

[neileg]

Have a look at the Split() function in Access.

 

[Benjamin Bolduc]

Well seems like a good start. I've never used the Split() function before.

What do you think is the best way of applying it to my situation? I couldn't find an example in the help file, so I'm not sure what to do with it.

Thanks!

Ben Bolduc

 

[elbweb]

try this.. if access can't handle it, use excel, there is an import text wizard in excel that seems a lot more flexable then the text solutions in access... if that doesnt work, if you could give me a sample ill show you how to parse through it and build a table with it through vba

 

[dsigner]

My way out when these things don't go as I expect is to use samller steps.
Bring the csv file into an access table as is. Then use standard access to put the data where you want it with update queries.

 

[PeterF]

In my opinion you need to create a history table with separate record for each log, as suggested before the split function can do this but you have to write some code to enter it in a new table, your code would look something like this:

Function ConvertMemoField()
Dim StrSql As String
Dim RecordsToConvert
Dim NewRecordFromMemo As Variant
Dim i As Integer

'I imported your table to a new table called tblCurrent
'and named the fields LastNameCust and History


'Create a recordset with the fields to convert
StrSql = ("select * from tblcurrent")
Set RecordsToConvert = CurrentDb.OpenRecordset(StrSql)

'step trough the recordset
    With RecordsToConvert
        Do While Not .EOF
                    Debug.Print !history  'print for test purposes
'here you separate the records
            NewRecordFromMemo = Split(!history, "°") 
'here you need to ad some code to split the fields
                For i = 0 To UBound(NewRecordFromMemo)
                    Debug.Print !LastNameCust & vbTab & NewRecordFromMemo(i)

'Here you need to enter the code to enter the record in a new table
                Next i
            .MoveNext
        Loop
    End With
        

Set NewRecordFromMemo = Nothing
End Function

You can copy the code above in a new module and run it in the VBA editor and edit to suit your needs.

 

[Benjamin Bolduc]

PeterF, you are my hero!

I copied your code into a module and changed my table properties to match accordingly.

I run the code and it cycles through, but when it stops, there are no new records anywhere. I thought maybe they'd be in thier own table, but maybe I'm not fully grasping what the code does.

If I could bother you once more for some clarification, I would truly appreciate it.

Thanks!

Ben Bolduc

 

[Benjamin Bolduc]

I've been trying to get this code to work, but it just doesn't seem to put the data anywhere. Any attempt I've made to transfer the RecordsToConvert recordset to a table have resulted in a type mismatch error.

Also, I tried to parse this in excel using the conversion wizard, with no luck.

I feel like I'm on the cusp of figuring this thing out. Anyone have any ideas?

 

[elbweb]

Ben,

In order to edit the code to put the data somewhere, your going to have to know a little bit about recordsets, and things like that. Also, assuming you got the code working correctly, you would just need to put in some code to add a new record every time the split occurs.

You see where there is the debug.print occurs, i hope you understand what that is doing, becase it is taking the parsed data, and just printing it in the debug window.

http://www.access-programmers.co.uk/forums/showthread.php?t=111842

this thread i have a pretty lengthy exlination about how to use recordsets, so good luck