Solved

populate text file data to database

Posted on 2006-07-17
15
167 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
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
 

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
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
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…

911 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

19 Experts available now in Live!

Get 1:1 Help Now