Data import question

I have some emailed survey responses that I want to import into an Access datbase so that I can analyse them. The problem is that the reponses are in the following format:

(_vsvn)  surgeon
(_age)  31-40
(_yeargraduated)  1982
(_journalsub)  joursubyes
(_journalaccesswork)  journalaccessworkno
(_electronicsources)  esourcesyes
(_mostusefulesource)  Wos, BIDS
(_internetuse)  internetno
(_spend)  spend1-50
(_knowlis)  knowlisyes
(_uselastyear)  uselastyearyes


…where the field names are in the brackets, and the data follows after.

Any thoughts or suggestions?


I am failing to see extacly what the problem is. Is it the age range (31-40) or multiple entries in mostusefulsource that are causing problems? Tell us what fields are an issue and maybe we can help.

I am failing to see exactly what the problem is. Is it the age range (31-40) or multiple entries in mostusefulsource that are causing problems? Tell us what fields are an issue and maybe we can help.

Shagnasty - simply getting the data in is the basic problem, since it is not ordered in a nice intuitive way (e.g. each record on its own line, fields separated by semicolons, etc), so I am not sure how I should go about it…


Just off the top of my head, could you parse the data and then concatenate them into strings with a delimiter? Is there two spaces between the field name and the data? If so you may be able to do a string parse and insert a comma or other delimiter between the data.

Is this a one-off operation or are you looking to import data in this format in a regular basis?

I think the solution is going to include VBA, probably not attempting to import the file as a table, but rather accessing it as a text file, parsing and writing/appending records to a table using code, or importing it all to a single-(text)field table, then stepping through that line-by-line and parsing into another table.

If you fancy a crack at this approach, I have done something quite similar, so I could post a code sample (when I can find it)

Once-off - some 200 survey replies that I don’t fancy keying in by hand… the VBA approach sounds like a challenge that I wouldn’t mind having a go at… if you can dig it out that is…


There is a shareware program called ParseRat that does something similar to what you want to do. The 30-day trial is free so you may want to take a look at it.

Are these records in one big file or are they in 200 individual files. I have done MANY conversions like this, so if you want to e-mail me a sample, I can take a quick crack at it.

OK, here’s the code from a similar project I worked on (my file was a fixed-width pseudo-EDI order file, which actually had more than one value per line, but the method works either way; before the code below is run, I have already imported the file into a two-field table called EDI Raw the entirety of each line going into the field imaginatively named Field1 (the second field in the table is an Autonumber, field name ID - this keeps the records in the right order).

Dim db As Database
Dim SQLstr As String
Dim RstTmp As Recordset
Dim RstOut As Recordset
Dim recordloop As Long
Dim InpStr As String
Dim CurrSuppID As String
Dim CurrBranch As String
Dim CurrOrdNum As String
Dim CurrEan As String
Dim CurrHandi As String
Dim CurrCat As String
Dim CurrQty As String
Dim CurrFldStart As String

Set db = CurrentDb()

'strip out irrelevant records
SQLstr = "DELETE [EDI Raw].Field1 FROM [EDI Raw] WHERE ((([EDI Raw].Field1) Like 'TYP*' Or ([EDI Raw].Field1) Like 'SDTC*' Or ([EDI Raw].Field1) Like 'CDT*' Or ([EDI Raw].Field1) Like 'FIL*'));"
db.Execute SQLstr

'put into a record set
SQLstr = "SELECT [EDI Raw].Field1, [EDI Raw].ID FROM [EDI Raw] ORDER BY [EDI Raw].ID;"
Set RstTmp = db.OpenRecordset(SQLstr, dbOpenSnapshot)

'get an accurate record count
On Error Resume Next
On Error GoTo 0

SQLstr = "SELECT Intermediate.* FROM Intermediate;"
Set RstOut = db.OpenRecordset(SQLstr, dbOpenDynaset)

For recordloop = 1 To RstTmp.RecordCount
InpStr = RstTmp.Fields!field1.Value
CurrFldStart = Left(InpStr, 4)

If CurrFldStart = "SDT " Then 'this line contains the supplier ID
    CurrSuppID = Trim(Mid(InpStr, 19, 17))
End If

If CurrFldStart = "CLO " Then 'this line contains the store ID
    CurrBranch = Trim(Mid(InpStr, 32, 17))
End If

If CurrFldStart = "ORD " Then 'this line contains the order number
    CurrOrdNum = Trim(Mid(InpStr, 5, 17))
End If

If CurrFldStart = "OLD " Then 'this line identifies the product
    CurrEan = Trim(Mid(InpStr, 5, 13))
    CurrCat = Trim(Mid(InpStr, 19, 30))
End If

If CurrFldStart = "OLDA" Then 'this line contains the customer's product ID
    CurrHandi = Trim(Mid(InpStr, 21, 30))
End If

If CurrFldStart = "OLDB" Then 'this line contains the order qty
    CurrQty = Trim(Mid(InpStr, 5, 15))
    With RstOut
        !EAN = CurrEan
        !catalogue = CurrCat
        !handi = CurrHandi
        !qty = CurrQty
        !SuppID = CurrSuppID
        !Branch = CurrBranch
        !ordno = CurrOrdNum
    End With
End If


Next recordloop

The file I was importing was more freely-structured than yours; what would happen,for example, is that a line starting ‘OLD’ gives me the order reference details, which I store in variables to push into every line until the next ‘OLD’ line comes along to change it, but in between these lines, there will be an indefinite number of order lines (each of which is culled for relevant data) only when we get to a line that begins ‘OLDB’ - the last part of an order item - are the accumulated values appended into a line in the output table (called Intermediate).

[sup]And yes, I know I should have used a Select…Case statement, but I didn’t know how when I wrote this code and I’ve not had the time or the urge to go back and rewrite it now[/sup]

Why make it so complicated?

In the import wizard, choose delimited text

Choose the delimiter as Other, and type “)” in the box provided

This will read the data in with two columns containing
Field 1 Field2
(_vsvn surgeon
(_age 31-40

If you want to you can choose to skip field 1 to just return the data you need.


Russell: that would import all of the values into a pair of fields, I get rhe impression that grimpixie wants to widen the data so that for each chunk of records, a single row is appended to a table, like this:


Indeed - Mangetout has got it right - and thanks to his helpful code (with a bit of tweaking), the data is safely (and quickly) imported!!! Thanks very much for the help!!