# Data import question

**URL:** <https://boards.straightdope.com/t/data-import-question/105327>\
**Category:** Factual Questions\
**Created:** [April 24, 2002, 12:15pm UTC](https://boards.straightdope.com/t/data-import-question/105327 "2002-04-24T12:15:38Z")\
**Posts on this page:** 12\
**Page:** 1

<div class="post-metadata">

**Author:** ![grimpixie](https://avatars.discourse-cdn.com/v4/letter/g/ecb155/32.png) [@grimpixie](https://boards.straightdope.com/u/grimpixie)\
**Post date:** [April 24, 2002, 12:15pm UTC](https://boards.straightdope.com/t/data-import-question/105327/1 "2002-04-24T12:15:38Z")

</div>

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:

```auto

(_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

etc...

```

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

Any thoughts or suggestions?

Gp

---

<div class="post-metadata">

**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\
**Post date:** [April 24, 2002, 1:23pm UTC](https://boards.straightdope.com/t/data-import-question/105327/2 "2002-04-24T13:23:36Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\
**Post date:** [April 24, 2002, 1:25pm UTC](https://boards.straightdope.com/t/data-import-question/105327/3 "2002-04-24T13:25:39Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![grimpixie](https://avatars.discourse-cdn.com/v4/letter/g/ecb155/32.png) [@grimpixie](https://boards.straightdope.com/u/grimpixie)\
**Post date:** [April 24, 2002, 1:42pm UTC](https://boards.straightdope.com/t/data-import-question/105327/4 "2002-04-24T13:42:18Z")

</div>

**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…

Gp

---

<div class="post-metadata">

**Author:** ![BF](https://avatars.discourse-cdn.com/v4/letter/b/b5ac83/32.png) [@BF](https://boards.straightdope.com/u/BF)\
**Post date:** [April 24, 2002, 1:56pm UTC](https://boards.straightdope.com/t/data-import-question/105327/5 "2002-04-24T13:56:29Z")

</div>

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.

---

<div class="post-metadata">

**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\
**Post date:** [April 24, 2002, 2:31pm UTC](https://boards.straightdope.com/t/data-import-question/105327/6 "2002-04-24T14:31:52Z")

</div>

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)

---

<div class="post-metadata">

**Author:** ![grimpixie](https://avatars.discourse-cdn.com/v4/letter/g/ecb155/32.png) [@grimpixie](https://boards.straightdope.com/u/grimpixie)\
**Post date:** [April 24, 2002, 3:11pm UTC](https://boards.straightdope.com/t/data-import-question/105327/7 "2002-04-24T15:11:31Z")

</div>

> [@](#):
>
> \*Originally posted by Mangetout \*  
> \*\*Is this a one-off operation or are you looking to import data in this format in a regular basis?  
> …
> 
> 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…

Thanks  
Gp

---

<div class="post-metadata">

**Author:** ![Shagnasty](https://avatars.discourse-cdn.com/v4/letter/s/9dc877/32.png) [@Shagnasty](https://boards.straightdope.com/u/Shagnasty)\
**Post date:** [April 24, 2002, 3:17pm UTC](https://boards.straightdope.com/t/data-import-question/105327/8 "2002-04-24T15:17:56Z")

</div>

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.

[http://download.com.com/3000-2248-6182121.html?tag=lst-0-2](http://download.com.com/3000-2248-6182121.html?tag=lst-0-2)

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.

---

<div class="post-metadata">

**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\
**Post date:** [April 24, 2002, 9:24pm UTC](https://boards.straightdope.com/t/data-import-question/105327/9 "2002-04-24T21:24:04Z")

</div>

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

```auto

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]

---

<div class="post-metadata">

**Author:** ![RussellM](https://avatars.discourse-cdn.com/v4/letter/r/ed8c4c/32.png) [@RussellM](https://boards.straightdope.com/u/RussellM)\
**Post date:** [April 25, 2002, 12:41pm UTC](https://boards.straightdope.com/t/data-import-question/105327/10 "2002-04-25T12:41:43Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![Mangetout](https://sea3.discourse-cdn.com/straightdope/user_avatar/boards.straightdope.com/mangetout/32/19_2.png) [@Mangetout](https://boards.straightdope.com/u/Mangetout)\
**Post date:** [April 25, 2002, 12:59pm UTC](https://boards.straightdope.com/t/data-import-question/105327/11 "2002-04-25T12:59:21Z")

</div>

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:

```auto

**vsvn,age,yeargraduated,journalsub,journalaccesswork,electronicsources,mostusefulesource,internetuse,spend,knowlis,uselastyear
**surgeon,31-40,1982,Yes,No,Yes,BIDS,No,1-50,Yes,Yes

```

---

<div class="post-metadata">

**Author:** ![grimpixie](https://avatars.discourse-cdn.com/v4/letter/g/ecb155/32.png) [@grimpixie](https://boards.straightdope.com/u/grimpixie)\
**Post date:** [April 25, 2002, 2:02pm UTC](https://boards.straightdope.com/t/data-import-question/105327/12 "2002-04-25T14:02:39Z")

</div>

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!!

Gp