conversion of text file into data base file

[jto]

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?

[jto]

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

[Yomet]

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

[jto]

thank u
but suggest me the code .

[Yomet]

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

[jto]

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 .

[Yomet]

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

[jto]

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

[Yomet]

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:

Data(0) = Left(InLine, 10) 'Or Mid(InLine, 1, 10)
Data(1) = Mid(InLine, 11, 10)
Data(2) = Mid(InLine, 23, 11)
.
.
.
Data(15) = Right(InLine, 32) 'Or Mid(InLine, 156)

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

[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

[jto]

yomet
when i am adding the code as u suggest i.e data(0)=Mid(InLine, 1, 10) and so on
but i am getting subscript out of range why?

[Yomet]

Did you change the INSERT INTO statement to take into account the existing fields? I.e. you can no longer use Data(16), Data(17), Data(18) and Data(25) but need to adjust these subscripts, as well as the others, to reflect the existing members of the Data() array.

Also, I noticed an error on my part, I put the AMT_DUE and NAME on the same line. I have updated the definition and also added the corresponding Data() index. The data definition should be as follows:
Array pos Field Start Len
===========================================
Data(0) ACCOUNT_NO 1 10
Data(1) MOBILE_NO 11 10
Data(2) BILL_REF_NO 23 11
Data(3) OTC 35 10
Data(4) RC 46 10
Data(5) USAGE 57 10
Data(6) MISC 68 10
Data(7) ADJ 79 10
Data(8) DISC 90 10
Data(9) ST 101 10
Data(10) BILLED_AMT 112 10
Data(11) LATE_FEE 123 10
Data(12) PAYMENT 134 10
Data(13) PREV_BAL 145 10
Data(14) AMT_DUE 156 10
Data(15) NAME 167 30

Hope this helps

Yomet

[Yomet]

One more thing, if it still bugs out, please send you new code and tell me which line is giving you the error. You can find that out by selecting "Debug" when the program crashes. The line in question will be highlighted in yellow.

Thx

Yomet

[jto]

yomet ,
again we r getting the same problem script out of range in the line Data(0) = Mid(InLine, 1, 10) it showing that 'script out of range ' when i am taking the cursor on Data(0) in debug mode .I am sending the new code as attachment

[Yomet]

jto,

I found the problem:
Dim Data() As String
You will have to
Dim Data(15) As String
for this to work... ;)

Yomet

[jto]

hello yomet
thank u ,from this code is working perfectly . but if text file having any header information it is also updating in data base .can we apply a condition that if data(0)='Account_no" or "-----" skip that line .
pls suggest me the condition code.

[Yomet]

jto,

You are absolutely correct in your assessement of the situation and if you look at posting #10 you will see that I already told you this, not to confuse with "told you so"... :)

You already have the code in your posting, it's a simple IF statement that you put around the
db.Execute "INSERT INTO Table1 (Acco[...]
statement.

You should be able to figure out the exact syntax of a simple If statement, otherwise you woudn't have gotten this far... ;)

Happy coding

Yomet

P.S. By using this approach you do not need to skip the first lines of the data file since this condition will automatically eliminate the header from the beginning of the file.

[jto]

Yomet ,
one thing more i want to take the required database report in a particular format , can we use crystal report . i heard that it is very good report genration tool . can u help me . if not can we take the report from vb also.

[Yomet]

jto,

I cannot help you with Crystal Reports since I do not have enough experience with that but if your client(s) have Access installed you can take all the code from VB, with slight modifications, and add it to an Access databse and use the reporting tool from Access to create your reports. This still works if you will be using another database than Access as the real backend since you can use Access as the frontend instead of VB.

Otherwise I am sure there are knowledgable Crystal Reports people in this forum.

Hope this helps

Yomet

[royaloba]

hello sir jomet

2005/11/11 17:36:13, Event:Access, Door:entry, Card No.:1234, Description:333
2005/11/11 17:36:17, Event:Access, Door:entry, Card No.:1234, Description:333
2005/11/11 17:36:20, Event:Access, Door:entry, Card No.:2222, Description:222
2005/11/11 17:36:26, Event:Access, Door:entry, Card No.:2222, Description:222

i when i read this thread, i got my problem solve on how to save the text file in database.. i have a project which time and attendance base on textlog.. my problem is how can i save the time in two TIME IN and TIME OUT the first record is for the time in and the last record willbe for the time out, base on their card number and date..

[Yomet]

royaloba,

Hi, I got your personal messages and I am happy that what I posted earlier has helped you.

I would like you to start a new thread so as to not interfere with this one. and post your code in that thread. Your last personal message got cut off (too long).

From what you say there does not seem to be much left to do so it should not take too long time.

Thanks

Yomet

[Yomet]

royaloba,

I just saw your newly created thread and will post the answer in there

Thx

Yomet