473,326 Members | 2,175 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

Importing data from text files of non-standard layout

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
6 2661
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
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
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
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
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
"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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: dave | last post by:
Hello there, I am at my wit's end ! I have used the following script succesfully to upload an image to my web space. But what I really want to be able to do is to update an existing record in a...
4
by: tgru | last post by:
Hello, I need to import a bunch of .csv files. The problem I am having is the "non data" information in the files creating bogus rows and column definitions. Here is an example of the csv file....
12
by: expect | last post by:
What's the big deal with importing text files? I have a 70 MB file to import and it's been one problem after another. I used the copy command and it appears that it's just not possible. I finally...
11
by: Grim Reaper | last post by:
I am importing a .csv file into Access that has 37 fields. My problem is that sometimes the last field only has data at the end of the column (it looks like when you import a file into Access, for...
1
by: D Mat | last post by:
Hi, I'm trying to get MS Access 2000 to automatically import a series of (~200) flat text, tab delimited, data files into a single Access table, with consistent fields and rows. The files have...
1
by: sparks | last post by:
I have never done this and wanted to ask people who have what is the best way. One person said import it to excel, then import it into access table. but since this will be done a lot, I am...
3
by: James | last post by:
Hi, I'm importing some csv files with this code /// start of code snippet int iPos = strFileName.LastIndexOf(@"\"); string strPath = strFileName.Substring(0,iPos); string strSelect =...
29
by: Natan | last post by:
When you create and aspx page, this is generated by default: using System; using System.Collections; using System.Collections.Specialized; using System.Configuration; using System.Text; using...
5
by: hharriel | last post by:
Hi, I am hoping someone can help me with an issue I am having with excel and ms access. I have collected data (which are in individual excel files) from 49 different school districts. All...
12
by: JMO | last post by:
I can import a csv file with no problem. I can also add columns to the datagrid upon import. I want to be able to start importing at the 3rd row. This will pick up the headers necessary for the...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.