populate text file data to database

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
rmtogetherAsked:
Who is Participating?
 
clarkscottConnect With a Mentor Commented:
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
 
justchat_1Commented:
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
 
rmtogetherAuthor Commented:
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
Cloud Class® Course: Microsoft Office 2010

This course will introduce you to the interfaces and features of Microsoft Office 2010 Word, Excel, PowerPoint, Outlook, and Access. You will learn about the features that are shared between all products in the Office suite, as well as the new features that are product specific.

 
justchat_1Commented:
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
 
rmtogetherAuthor Commented:
hi, justchat_1

could you please hlep me detail code about this problem?
0
 
justchat_1Commented:
Sure... but where will this text file be loaded into? an array? a flexgrid? something else?
0
 
rmtogetherAuthor Commented:

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

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
 
clarkscottCommented:
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
 
clarkscottCommented:
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
 
rmtogetherAuthor Commented:
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
 
justchat_1Commented:
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
 
justchat_1Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.