i have a data file in text format , i want to convert this text file again in to oracle data base file for this i want coding in vb such that to calculte from this byte to that byte should be update in particular field etc. pls help me?

the text file as below
DIR CALLED-NUMBER DATE TIME DURTN UNITS
-------------------------------------------------------------------
267421 957414221475 19/10/2005 23:55:17 4:41 2
261044 07548272566 19/10/2005* 23:53:14 6:55 7
264192 09898372722 19/10/2005* 23:55:29 5:06 11
241387 9827289972 19/10/2005 00:01:44 0:11 1
236456 9425354987 19/10/2005* 23:57:14 5:35 6
296350 957413278907 19/10/2005 00:01:38 1:25 1
269230 9425103806 19/10/2005 00:06:05 0:01 1
293416 9425329313 19/10/2005 00:05:53 2:26 3
269233 9425355219 19/10/2005 00:08:04 0:19 1
235064 9827504888 19/10/2005 00:08:00 0:48 2
237977 9827743136 19/10/2005 00:05:41 4:03 6
269233 9425091549 19/10/2005 00:09:07 1:27 2
220105 9826270786 19/10/2005 00:10:06 1:29 2
and i want just to read the text file as character wise and update the field of data file just like from 1 to 6 character it takes and repace/update the field , tel_no of database and then from 9 to 18 to call_no field of database and so on . is it possible by using vb code . pls suggest me the code

Hi jto,

I suggest you make a sub that finds the fields depending on the space character since it seems to delimit your data, for this you can use the SPLIT function to return the data elements in an array.

From there you can easily cut out what you don't want, i.e. the first two characters of the number called and then use ODBC to insert that data into your Oracle database.

Otherwise you could make a load definition in Oracle and use SQL Loader to import the data or, if you need to manipulate the data, create a PL/SQL script that would read your data file.

Hope this helps

Yomet

thank u
but suggest me the code .

JTO,

Here is a sample project that reads the data you posted and SPLITs it into an array where you then can manipulate the data and later use to store into your database.

Hope this helps

Happy coding

Yomet

thank u yomet
u r seeing interest in my queries and for codeing also . but because i am not so perfect in vb u can say me a learner . u have given the code which r showing the result of each line one by one . ok but i want that , i will make a access file because i want to take this record as printout and also for other processing so i required six field in datafile namely DIR_No, CALLED_NUMBER, DATE, TIME, DURTN & UNITS , software should collect first 'DIR' and update into DIR_No field and so on . and at last i want a report with same field separated by line and contains hadder in each page . hope u have understood my problem

Again thanks for help

I wish u a Very Happy New year .

Happy New Year to you too JTO,

I have now added the functionality to insert the data into the included Access database. The easiest way for you to insert into another kind of database, i.e. Oracle, MS SQL Server, etc., would be to link those tables into the Acecss database and use the same code. But if you want to redo the code for your specific database you can do that too.

A couple of comments about the code:
- The field names Date and Time need to be inside square brackets [] because those are reserved words of VBA
- The use of DateValue('" & Format(ToDate(...)) & "') is because manipulating dates that only contain numbers can cause confusion, i.e. 10/05/2005 could be May 10th or Oct 5th depending on how you interpret the order.
- The use of TimeValue('00:" & Data(4) & "') is to make sure that Access does NOT interpret the duration as h:m but well as m:s

From here you should be able to do the rest, I think.

Happy coding

Yomet

hi yomet
thanks for coding when i have used the same concept for another thpe of database with more field and more datas ,it is working but it is updating in only three field not all 16 fields and after approx. 166 recodrs it was showing the error "script out of range why?
pls help me i am sending the code with text file
once again thanks

jto,

From what I can see there are a few problems with your data file. All this comes from the fact that you are using the SPLIT function and this function splits on EVERY SPACE, if two spaces are together it thinks that it has an empty data field in between those spaces, which is not true in your case.

The SubScript Out Of Range comes from your adjustment to the SPLIT function's peculiarities. You only have 16 data fields in your file, however all the empty spaces (data fields for SPLIT) boosts that count and so you can use Data(25) in your code. Here is the problem, when your code encounters the line
---------- ---------- ----------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ---------- ------------------------------
it no longer has 25 data fields, only 18, the last two spaces make the 2 extra fields, so now your code is causing errors, had I known that this was the actual data format I would not have suggested using SPLIT since it cannot handle this kind of data.

There is basically only one way to deal with this kind of data, creating a data definition and then 'manually' splitting the line using the Mid() function, like this:
Data definition (sorry about the reformatting by the web page):

Field Start Len
===========================
ACCOUNT_NO 1 10
MOBILE_NO 11 10
BILL_REF_NO 23 11
OTC 35 10
RC 46 10
USAGE 57 10
MISC 68 10
ADJ 79 10
DISC 90 10
ST 101 10
BILLED_AMT 112 10
LATE_FEE 123 10
PAYMENT 134 10
PREV_BAL 145 10
AMT_DUE NAME 156 30

Splitting code: This also changes the definition of the array from Data() to Data(15), otherwise VB will not know how many elements the array should have. You could also define it as Data(1 to 16) and modify your code accordingly, either way is the same it's only a question of preference and habit of working with 0-based or 1-based arrays.

The second problem, of not updating more than 3 items, arises from the definition of the table and the empty data fields in your input data. All the fields in the table have the property Allow Zero Length set to No so when you try to add a field that contains a zero length string the table rejects it. The solution is simple, change the definition of your table to allow for zero length strings.

I have NOT tested these solutions but I am confident that they will solve your problems.

Happy coding

Yomet

Another thing, I would add code to reject any line that has Data(0) = "ACCOUNT_NO" or "----------" this way you will eliminate these lines from the table data.

Ciao

Yomet