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:
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
DoEvents
'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)
DoEvents
'get an accurate record count
On Error Resume Next
RstTmp.MoveLast
RstTmp.MoveFirst
On Error GoTo 0
SQLstr = "SELECT Intermediate.* FROM Intermediate;"
Set RstOut = db.OpenRecordset(SQLstr, dbOpenDynaset)
DoEvents
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
.AddNew
!EAN = CurrEan
!catalogue = CurrCat
!handi = CurrHandi
!qty = CurrQty
!SuppID = CurrSuppID
!Branch = CurrBranch
!ordno = CurrOrdNum
.Update
End With
End If
DoEvents
RstTmp.MoveNext
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]
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!!