By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,658 Members | 969 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,658 IT Pros & Developers. It's quick & easy.

Importing data from text files of non-standard layout

P: n/a
I need to import data from a website, but the text is arranged badly:

Name:John Doe
Title:Grunt
ID:314159
Name:Jane Doe
Title:Queen-Bee
ID:271828
etc...

I tried importing the data with the Get External Data (from a text
file) using the colon as a delimiter but (obviously) I just got two
useless columns. I have limited experience with SQL, VB, Access, so
does anyone know an easy way to do this? Or even a hard way? Thanks
in advance.

--sam
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
Sam Lazarus wrote:
I need to import data from a website, but the text is arranged badly:

Name:John Doe
Title:Grunt
ID:314159
Name:Jane Doe
Title:Queen-Bee
ID:271828
etc...

I tried importing the data with the Get External Data (from a text
file) using the colon as a delimiter but (obviously) I just got two
useless columns. I have limited experience with SQL, VB, Access, so
does anyone know an easy way to do this? Or even a hard way? Thanks
in advance.

--sam


Don't know if this would work, good chance it will. Create a temp table
to import the records in. Have 3 columns; AutoNumber, TypeOfData (name,
title,id), and DataField (Text). Now import them into the temp table.

Now create 3 queries. Q1, Q2, Q3

The first query would select the ID and DataField for all records with
TypeOfData = "Name".

The second query have 2 fields; and expression IDLink and DataField for
all records with TypeOfData = "Title". The expression would be entered
in the query builder like
IDLink : [ID] - 1
The third query have 2 fields; and expression IDLink and DataField for
all records with TypeOfData = "ID". The expression would be entered in
the query builder like
IDLink : [ID] - 2

Now create a fourth query. Add queries Q1, Q2, Q3. Create a relation
between Q1's ID and Q2 and Q3's IDLink.

Now drag the DataField from Q1, Q2, and Q3. Run it. If it looks OK
then get back into design mode and from the menu select AppendQuery and
associate the datafields to the fields you want to append in the
production table.

Nov 13 '05 #2

P: n/a
Sam,

It's late, and I don't have a lot of time right now ... but this should get
you started.
Try using the "Line Input" method to read the text from the text file, one
line at a time.
You know that the colon is the delimiter that seperates the Field Name from
the Data, so use the Left and Mid functions to sperate the string into 2
variables as I have below.

You can then add further code to open a recordset on the table that you wish
to populate with the imported data.
I'd try using a Select Case statement on the "strField" variable to determin
which field "strData" belongs in, then poke it in.
************************************************** *******
Private Sub cmdImportText_Click()

Dim TextLine
Dim strField As String
Dim strData As String

Open "C:\TEMP\Names.txt" For Input As #1 ' Open file.

Do While Not EOF(1) ' Loop until end of file.

Line Input #1, TextLine ' Read line into variable.
TextLine = Trim(TextLine) 'Get rid of any leading or trailing spaces

strField = Left(TextLine, InStr(1, TextLine, ":") - 1)
strData = Mid(TextLine, InStr(TextLine, ":") + 1)
Debug.Print "This is the original line of text ... " & TextLine '
Print to Debug window.
Debug.Print "This is the field name ... " & strField
Debug.Print "This is the data ... " & strData
Debug.Print "" 'Print a blank line for spacing purposes

Loop

Close #1 ' Close file.
End Sub

************************************************** *******
This is the output from Debug (Intermediate) Window below:
________________________________________________

This is the original line of text ... Name:John Doe
This is the field name ... Name
This is the data ... John Doe

This is the original line of text ... Title:Grunt
This is the field name ... Title
This is the data ... Grunt

This is the original line of text ... ID:314159
This is the field name ... ID
This is the data ... 314159

This is the original line of text ... Name:Jane Doe
This is the field name ... Name
This is the data ... Jane Doe

This is the original line of text ... Title:Queen-Bee
This is the field name ... Title
This is the data ... Queen-Bee

This is the original line of text ... ID:271828
This is the field name ... ID
This is the data ... 271828

--
HTH,
Don
=============================
Use My*****@Telus.Net for e-mail
Disclaimer:
Professional PartsPerson
Amateur Database Programmer {:o)

I'm an Access97 user, so all posted code
samples are also Access97- based
unless otherwise noted.

Do Until SinksIn = True
File/Save, <slam fingers in desk drawer>
Loop

================================

"Sam Lazarus" <xa*******@hotmail.com> wrote in message
news:26**************************@posting.google.c om...
I need to import data from a website, but the text is arranged badly:

Name:John Doe
Title:Grunt
ID:314159
Name:Jane Doe
Title:Queen-Bee
ID:271828
etc...

I tried importing the data with the Get External Data (from a text
file) using the colon as a delimiter but (obviously) I just got two
useless columns. I have limited experience with SQL, VB, Access, so
does anyone know an easy way to do this? Or even a hard way? Thanks
in advance.

--sam

Nov 13 '05 #3

P: n/a
Don,
not one to leave well enough alone, I fixed your code... make any
comments you want...
One thing I didn't mention is that my table looks like this:

CREATE TABLE TempTable(
FullName TEXT(50),
Title TEXT(50),
ID TEXT(50));

I'm reading 3 records from the textfile in a row and writing them to
an array (astrInfo) and then transferring the data to the
recordset/table after that. Couldn't think of any other way to save
the 3 values while I had to loop.

Good Puzzle! Thanks for the head start, Don.

Pieter

'--START CODE----
Option Compare Database
Option Explicit

Public Sub ImportText(ByVal strFileName As String)
'************************************************* ********
Dim TextLine
Dim strField As String
Dim strData As String
Dim intCounter As Integer
Dim astrInfo(0 To 2) As String

'--database variables
Dim db As DAO.Database
Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs = db.OpenRecordset("tblTemp", dbopentable)

Open strFileName For Input As #1 ' Open file.

Do While Not EOF(1) ' Loop until end of file.

For intCounter = 0 To 2
' Read line into variable.
Line Input #1, TextLine

'Get rid of any leading or trailing spaces
TextLine = Trim(TextLine)

'stuff the 3 values from the 3 textfile lines into an array
astrInfo(intCounter) = Right$(TextLine, Len(TextLine) -
InStr(1, TextLine, ":", vbTextCompare))

Next intCounter

'-write the values in the array to the table. The array values
and the table fields are in the same order, so they have the same
zero-based index

rs.AddNew
For intCounter = 0 To 2
rs.Fields(intCounter) = astrInfo(intCounter)
Next intCounter
rs.Update

Loop

rs.Close
Set rs = Nothing
Set db = Nothing
Close #1 ' Close file.
End Sub

'---END CODE----
Nov 13 '05 #4

P: n/a
xa*******@hotmail.com (Sam Lazarus) wrote in message news:<26**************************@posting.google. com>...
I need to import data from a website, but the text is arranged badly:

Name:John Doe
Title:Grunt
ID:314159
Name:Jane Doe
Title:Queen-Bee
ID:271828
etc...

I tried importing the data with the Get External Data (from a text
file) using the colon as a delimiter but (obviously) I just got two
useless columns. I have limited experience with SQL, VB, Access, so
does anyone know an easy way to do this? Or even a hard way? Thanks
in advance.

--sam


I came across the same problem a number of years ago. I found that a
DOS version of awk worked very well for converting this kind of data
into something that could be easily imported into Access. It seemed a
little easier than going the Line Input route and using VBA string
functions. Your mileage may vary.

James A. Fortune
Nov 13 '05 #5

P: n/a
Hi Pieter,

Yup ... I don't see much wrong with that approach. The only problem that I
could forsee is if the field order was somehow changed.


Nov 13 '05 #6

P: n/a
"Don Leverton" <My*****@Telus.Net> wrote in message news:<62KAc.1060$7d2.963@clgrps13>...
Hi Pieter,

Yup ... I don't see much wrong with that approach. The only problem that I
could forsee is if the field order was somehow changed.


That was a cheat - so that I could get the field collection subscripts
to line up, I think.
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.