Solved

populate text file data to database

Posted on 2006-07-17
15
170 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
Industry Leaders: 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 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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

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

Suggested Solutions

Introduction I needed to skip over some file processing within a For...Next loop in some old production code and wished that VB (classic) had a statement that would drop down to the end of the current iteration, bypassing the statements that were c…
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
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…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

740 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