Solved

populate text file data to database

Posted on 2006-07-17
15
166 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
  • 5
  • 4
  • 4
15 Comments
 
LVL 9

Expert Comment

by:justchat_1
Comment Utility
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
Comment Utility
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
Comment Utility
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
 

Author Comment

by:rmtogether
Comment Utility
hi, justchat_1

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

Expert Comment

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

Author Comment

by:rmtogether
Comment Utility

I guess load data into array and then write to database would be fine. thanks in advance
0
Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

 
LVL 20

Expert Comment

by:clarkscott
Comment Utility

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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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 500 total points
Comment Utility
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
Comment Utility
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

The debugging module of the VB 6 IDE can be accessed by way of the Debug menu item. That menu item can normally be found in the IDE's main menu line as shown in this picture.   There is also a companion Debug Toolbar that looks like the followin…
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 Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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…

762 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now