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

Import text file to table

P: n/a
I will shortly be receiving data in the form of a text file, like this:

id: 123456
first name: Fred
surname: Bloggs
age: 26

and so on, for about 60 fields. Each line ends with a carriage
return/line feed.

My question is - how do I import the data into a table (A2003) where
the field names match those in the text file? I think I could probably
grind out a solution but it would be horribly long winded - there must
be a neat solution to this.

Any help gratefully received
Thanks
Dave

Jul 14 '06 #1
Share this Question
Share on Google+
3 Replies


P: n/a

Dave G @ K2 wrote:
I will shortly be receiving data in the form of a text file, like this:

id: 123456
first name: Fred
surname: Bloggs
age: 26

and so on, for about 60 fields. Each line ends with a carriage
return/line feed.

My question is - how do I import the data into a table (A2003) where
the field names match those in the text file? I think I could probably
grind out a solution but it would be horribly long winded - there must
be a neat solution to this.
You could do something like:
1. read the data into a text field in a table with an autonumber field.
2. use MOD to get the nth records.
3. use append/update queries to put the data where it goes.

or you could read the data into variables and evry 4th read, write to
the db using DAO or ADO.

Jul 14 '06 #2

P: n/a
"Dave G @ K2" <Da*************@gmail.comwrote in
news:11**********************@s13g2000cwa.googlegr oups.com:
I will shortly be receiving data in the form of a text file,
like this:

id: 123456
first name: Fred
surname: Bloggs
age: 26

and so on, for about 60 fields. Each line ends with a carriage
return/line feed.

My question is - how do I import the data into a table (A2003)
where the field names match those in the text file? I think I
could probably grind out a solution but it would be horribly
long winded - there must be a neat solution to this.

Any help gratefully received
Thanks
Dave
If the name: value pair is always separated with a colon,
import into a two-field temporary table, (an autonumber and a
textfield).

then code a loop that does something like this..

do until RsFrom.EOF
iBreak = instr(1,rsFrom!textField,":")
stfieldName = left(rsFrom!textField,ibreak-1)
stfieldValue = trim(mid(rsFrom!textField,ibreak+1))
if stfldname = "ID" then
if rsto.recordcount 0
rsto.update
end if
Rsto.Addnew
end if
rsTo(stfldname) = stfldvalue
rsfrom.movenext
loop
rsTo.update

(add needed declarations and error handling)
iirc, it even handles numeric types ok.

--
Bob Quintal

PA is y I've altered my email address.

--
Posted via a free Usenet account from http://www.teranews.com

Jul 14 '06 #3

P: n/a

Thanks Bob, that should do it

Jul 14 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.