Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

populate text file data to database

Posted on 2006-07-17
15
Medium Priority
?
179 Views
Last Modified: 2010-04-30
I have a text file with 2 column and about 2000 rows of value. It looks like below

709.0 1220.0
391.0 1181.0
295.0 303.0
289.0 1303.0
2714.0 303.0
2745.0 1303.0
261.0 1254.0
And so on…

how can I import them to database (create a table with 2 fields ). thank you
0
Comment
Question by:rmtogether
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
  • 4
15 Comments
 
LVL 9

Expert Comment

by:justchat_1
ID: 17127257
open the file as a space delimited spreadsheet in a database program

-or-

actually read each line, split it by a space and add the to values to a table
0
 

Author Comment

by:rmtogether
ID: 17127314
I know how to manually import data into database (Access), but is there anyway to use VB read text file and then input data into database
0
 
LVL 9

Expert Comment

by:justchat_1
ID: 17127347
Loop though the file reading one line at a time
split the data and then add arg(0) and arg(1) as the two values for each row
Loop until eof()
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:rmtogether
ID: 17127364
hi, justchat_1

could you please hlep me detail code about this problem?
0
 
LVL 9

Expert Comment

by:justchat_1
ID: 17127490
Sure... but where will this text file be loaded into? an array? a flexgrid? something else?
0
 

Author Comment

by:rmtogether
ID: 17127625

I guess load data into array and then write to database would be fine. thanks in advance
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 17129185

dim ar(2000,2) as string
dim ar_count as long
dim x as string

dim f as freefile
open "c:\yourfolder\yourfile.txt" for input as #f
'--- of course, use your own path and file name ----
on error goto EndOfTheFile

getAnother:
input #f, x
'---- if not end of file, this line will be processed ----

ar_count = ar_count + 1
ar(ar_count,1) = x
input #f, x
ar(ar_count,2) = x

goto getAnother


progend:
close



'---- you now have an array of your values.  ar_count will equal the number of sets. --


exit sub

EndOfTheFile:
    resume progend



Scott C
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 17129198
Further.....

I used STRING since you have xxx.0 format of numbers.  If you use numeric variables, the .0 would be dropped.  I don't know if this is important to you or not.....


Scott C
0
 
LVL 20

Expert Comment

by:clarkscott
ID: 17129209
Further...

Upon re-reading your example... my code ASSUMES there is a delimiter between your columns

709.0 1220.0
391.0 1181.0

perhaps a comma
709.0, 1220.0
391.0, 1181.0

maybe a tab....?   Depending on the delimiter, my code may not work.  Please be more specific.

Perhaps open the file in Notepad and copy-and-paste to comment (highlight text, <ctrl> <insert> to copy,  <shift> <insert> to paste.

Scott C
0
 

Author Comment

by:rmtogether
ID: 17131590
hi clarkscott

Yes, there is a space between 2 column. could you please help me detail code.

assume there is a database already exist. I use Access. the db file name is : db1.mdb
and I want to use VB to create table (could be any table name). the data in table is imported by the text file using VB program

thanks in advance
0
 
LVL 9

Expert Comment

by:justchat_1
ID: 17132747
clarkscott ,
there are 2 incorrect assumptions with that code:
1. About 2000 rows-if its 2001 that code wouldn't...you are better off using dynamic arrays
2. It is space delimited, which means you should read in each line and split the string by a space delimeter using the split function
0
 
LVL 20

Accepted Solution

by:
clarkscott earned 2000 total points
ID: 17136536
Instead of populating an array, we can simply read the data and write it to the mdb table as we go.

Create a new Access mdb (or open the db1.mdb that already exists).
Create a form and place a single command button on it.
On the OnClick event write this code.

'------------------ open your text file ------------------------------------
dim f as freefile
open "c:\yourfolder\yourfile.txt" for input as #f

'----------------- open your mdb table ---------------------------------
'----------------- this uses DAO.  You must set a reference to Microsoft DAO (version 2.5,3.5, 3.6 - whatever you have.  Tools-References- scroll down the list and check the box) ---


dim db as database
dim rst as recordset
set db = currentdb
set rst = db.openrecordset("db1",dbopendynaset)

dim x as string

dim x1 as string
dim x2 as string
dim xdelim as integer

dim xLineCount as long



getAnother:

on error goto EndOfTheFile

line input #f, x
'---- if end of file - the line input will cause error - jump to error handler (EndOfTheFile) - then goto progend. -----

x1 = ""
x2 = ""

'---- this returns the location of the space (number of characters) -------
xdelim = instr(x, " ")
'---- breaks the variable x into 2 separate variables --------------------
x1 = left(x, xdelim-1)
x2 = right(x, len(x)-xdelim)

on error goto SomeOtherError

'----- create new record in db1 ----
rst.addnew


'===================================================
'----- if your mdb - db1 fields are text (string) then use these 2 lines ------
rst!YourField1Name = x1
rst!YourField2Name = x2
'----- if your mdb -db1 fields are numeric then use these 2 lines -----
rst!YourField1Name = val(x1)
rst!YourField2Name = val(x2)
'=============== don't use BOTH sets of these lines !!!! pick a set =======
xLineCount = xLineCount + 1

'---- this line finishes the record ------
rst.update


'----- keep returning to the text file for the next line ----
goto getAnother


progend:
on error resume next
close
rst.close
db.close


dim msg as string
msg = "Finished. " & xLineCount & " records extracted."
msgbox msg

exit sub

EndOfTheFile:
     resume progend


SomeOtherError:
    msgbox err.description
    resume progend





Scott C

0
 
LVL 9

Expert Comment

by:justchat_1
ID: 17161982
replace this:
'---- this returns the location of the space (number of characters) -------
xdelim = instr(x, " ")
'---- breaks the variable x into 2 separate variables --------------------
x1 = left(x, xdelim-1)
x2 = right(x, len(x)-xdelim)

with this:
x1=split(x," ")(0)
x2=split(x," ")(1)

...otherwise not bad
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I’ve seen a number of people looking for examples of how to access web services from VB6.  I’ve been using a test harness I built in VB6 (using many resources I found online) that I use for small projects to work out how to communicate with web serv…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question