Import a Text File

I am becoming really frustrated with this problem.  I'm trying to import a comma separated, variable length text file with Field Names in the first line into a paradox/dbase table that doesn't already exist.  The schema file will NOT exist before the import process.  I can't believe this is so dificult someone hasn't some up with a simple set of code to do this.  I've tried the Borland newsgroups, torry's, Deli. DSP, etc. all with no luck.  As long as the solution handles the above, is free/freeware and doesn't require the maker's dialog boxes (I've built my own), I'll be happy.

I already have a set of functions that get me about 80% of the way, by creating the sch file then opening the table.  The exception occurs when field names are used on number fields.  For example, "Age" would appear on the first line and all values who be n (integer).  The BDE assumes that since the Age 'field' is of integer type, 'Age' itself is an integer.  Once open in executed on the table, it crashes (Age isn't an number).  I can send anyone what I have already or you can some up with a better working solution.

I will increase the points if necessary.  Thanks.
LVL 3
d4jaj1Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ZifNabCommented:
I have a little bit time to look at your code this evening. Just send me the relevant code. and I 'll look at it. But that doesn't proves that I can solve it ;-).
ZifNabCommented:
So, what do you do exactly and where, when appears the error?
You open ASCII file, you convert it to SCH file, .... and poof?
d4jaj1Author Commented:
It happens after the schema file is created and just before teh table is opened.  I can send you 3 text files that illistrat exactly when it crashes.
Become a CompTIA Certified Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

ZifNabCommented:
Ok, send me the files.
ZifNabCommented:
Ok I found problems too, lets match these :

ContactExport.txt  : List index out of bounds
ContactExport2.txt : No problem (bu tall fields are strings)
ContractExport3.txt : Value out of bounds (or something like that)

Same erros as yours?
d4jaj1Author Commented:
Yes.  Thanks for taking the time to look it over.
ZifNabCommented:
Hi d4jaj1,

 With ContactExport.txt the error comes at the 10th field (or 9th if you start from 0) at the 411th line of Convert. It's with an exchange function. The strange thing is that I can't find out why he does it there.

I'll let you know if I find out more.

Regards, Zif.
keithcslCommented:
hi d4jaj1,

how big r ur text files? i have a text file about 1.8 MG (about 11,000 records with 26 fields each) and i can import to a dBase table in 35 seconds. however, my table is already created, but it shouldn't be a problem reading the first line and creating the table from there.

i read the text file record by record, field by field, and the fields consist of numeric, chars and booleans. i am still not happy with my speed performance and i am working on a way to make it go faster. however, i am interested in knowing ur method of importing the data.

please comment and i am happy to discuss more...

Regards
keith
d4jaj1Author Commented:
I can send you the code I have.  I got it off of teh Delphi Newsgroup.  In its current state, it fails when 1)fields are not delimited and 2) fields are delimited, but are Integers.  Maybe there are some things in my files that will help your speed issue and there is something in yours that will get mine to work at all.  Let me know what your email address is if you want a copy of the file.

Any luck on your end Zif?
ZifNabCommented:
Hi d4jaj1,
 Sorry no luck... and I hate when this happens! I hope someone can help you.
Regards,Zif.
d4jaj1Author Commented:
Keith,

Any thoughts? Did you want to see what I already have?
interCommented:
Hi,
Would you please send me your test files. I am trying to write a program for you.
Igor
d4jaj1Author Commented:
You are a special, special person.  Thaaaaaaannnnkkkk You!!!!!!  I can send the test files and the routine I already have.  If you can post your email address here, I'll send it right away.


interCommented:
Hi d4jaj1, Zif and all...

How silly I am,
inter@kosgeb.tekmer.gov.tr

ALSO:
I currently done the following, you may comment on anything. My ultimate aim is to supply a FREE code for the programmers who whish to import a file properly delimited by some character into a table. DONE:(all in a delphi program, no DBDesktop, no schema files etc..)
1  - From the first none empy line determine the field names
2  - From the following few lines tries to determine the field types, I use here majority voting, i.e. for example 4 out of 7 lines are interpreted as having same field types, the program assumes that the columns are of that type
3  - Creates a table from withing the delphi program with the schema found in 1 and 2
NOT DONE:
1 - I currently do not decide if there is an error in the line:
    a - Missing Delimiter character, which leads to less number of columns when parsed
    b - In compatible field values
2 - I do not code the import routine, but decide the following:
    a - Since I have already wrote a parser parsing is no problem
    b - I intended to use the CachedUpdates to speed up writing into the database
    c - If there exists a '*' character in the field names line I may create the table with that fields being index fields


Regards,
Igor
 


d4jaj1Author Commented:
I have sent the files.  Not sure how you would solve teh missing delimiter issue.  How would you know if the dilimiter was missing or it was supposed to be a long field?  If this is a component, I''d suggest allowing the user to decide whetehr to use CachedUpdates or not.  Using this feature can mess up other procedures/BDE functions within a Delphi app, depening of where the tables are, ocal share settings, etc.  Never heard of the '*' epresneing an index, but that sounds good.  If this is the standard somewhere, might as well code for it.

Thanks again.
interCommented:
Almost done friend,

Please elaborate on the 'speed of parsing and conversion'. In other words what is your speed requirements?

Igor
d4jaj1Author Commented:
I'm so desparate I just want something that works ;o).  Just kidding.  I don't have any present judgements of what's fast for parsing recoords.  I can however, state from a user's perspective how long something should take.

Since this is a Contact Mangager Import program, the total record count should be in the range of 20 to 1000 records(who knows more than a 1,000 people?) adding anywhere from 5 to 20 fields.  So, to add a 1,000 records (small in DB terms) to a newly created 'local' Paradox file, shouldn't take more than a few seconds.  Of course the time it takes to import a file depends on the users hardware, but on on a 486 system w/8 meg RAM, I'm thinking no more than 10-15 seconds maximum (3-6 ideal)from the time they click 'go' to the time a message is displayed saying 'done'.  10 seconds is actually long, so teh shorter the better.  The routine I sent seems pretty fast, though I haven't tried it on a 1,000 records nor does it work all the time.

Let me know if you have any other questions.
ZifNabCommented:
Ok, I'm back, for the moment... .Still have these damn problems to get in E-E. How are things going? Regards, Zif... .And euh.. happy easter.
d4jaj1Author Commented:
Inter sent me a import file with some questions.  I repied and am waiting for his reply to my email.  I haven't tried teh code yat, I'm waiting for hos reply.  It deals with determining teh field length for each field - whether to look at the first 20 records or go through teh entire file.  I suggested going through the entire file.  We'll see what Inter thinks.
ZifNabCommented:
Knowing Inter he 'll find a solution. Regards.
interCommented:
Finaly,

Here I am, my local time is 18:00 and work is over. So, I finaly have a time to do the modification. It should not be last more than an hour so, if you are there keep contact, I want to send the code(and executable) before going home.

Regards,
Igor
interCommented:
I have tested and send the code and executable. If you there please respond.
(Thanks Zif, for your kind words, if such a thing seems to be handy for you just ask...)
Regards,
Igor
ZifNabCommented:
Hi Igor,

Sure, I really would like to see the result of your work... If possible and d4jaj1 doesn't mind.

Regards, Zif.
d4jaj1Author Commented:
Sorry it took so long for me to respond - butI couldn't get to Experts-exhange.  The site kept giving me "failed to make connection errors..." I assume you're all gone now, so I'll play with this when I get home tonight (which I think is morning for your guys) and let you know what happened.

Thanks Igor and No, Zif - the more the better.
d4jaj1Author Commented:
Inter,

It didn't work for me.  I tried the exe version first and got an error of "can't create table".  I did see the field names on the left and the field types on the right, but no schema file was created (don't know if I was supposed to see one).

So, I tried to open the DPR and see where the exact error occurs. I didn't even get that far - I guess there was another error before the table gets created.  The Error is "...EConvertError with message 'Female' is not a valid integer value" and occurs on line 247 in the DetermineFieldNames function.

Does this work for you?  Maybe we're using different Delphi or BDE versions.  I'm using D2 with BDE 3.0 on the text.txt file your sent.  Zif, I'll forward you the file on email.
interCommented:
Good morning,

The program works and my 247th line just the following
247      S := S + ' '#0;
248      Ptr := @S[1];
249      I := 1;
So I start to think of a Version problem. Any way, at least the executable should work as is. Please try the exe on a simpler small file. By now the program creates a c:\temp\TEST.DB

If there please respond
Igor

d4jaj1Author Commented:
I'm here, but not for long - it's 4 in the morning here.  I need to get at least 4-5 hours sleep.  In nay case, I tied the exe file first (without the IDE open) and got a "can't create table error".  What version are you using?
interCommented:
Hi,

I think, I have Bde3 also. The parsing part is really not related with BDE version, so thats what I wonder. Anyway, have a nice and comfortable sleep. I will analyze  the field type determination part.

Also, we have a database master, ZIF are your there?, if so HELP please.
Regards,
Igor
ZifNabCommented:
Hi d4jaj1,

I tried the exe-file, I've got no problem! But maybe you've to make a directory c:\temp
becuase table is created in there!

I use D2 like you, but with exe-file this makes no difference.

Regards, Zif.
ZifNabCommented:
Yo all!,

I guess it's what I said in my last comment! I changed my c:temp to another name and guess what error i got. ... Can't create table!.....

Yo have to make the directory c:\temp. This directory isn't automatically created!

Inter, me a database master? Thanks for the compliment but, I don't think I am master...
Could your friend do something with TCDEvents?

Regards, Zif.

d4jaj1 have a pleasant sleep!

Inter, program looks really great!


d4jaj1Author Commented:
Okay, I'm up now.  I wonder how many hours ahead you guys are from US eastern standard?  any way, I created the c:\temp directory and it worked fine - importing 1999 records in about 29 seconds.  For some reason though - it doesn't work while the IDE is running!  I should have pointed out before the compiler stoped at line 247, however the error occured a the function/call directly before the 247 line (I'm not sure what that is, my Delphi isn't open now).  I'm sure this is in teh parsing area, not the table creation.  Any thoughts?  Does it work on the test file while teh IDE is running on your systems?

As for the directory, I was going to change the database path to extractfilepath(application.exename);
ZifNabCommented:
Goodmorning d4jaj1!

Here in Belgium it's 18:05 h. or 6:05 pm. I'll test it on my compiler.

Regards, ZiF.
ZifNabCommented:
Hi d4jaj1,

I've tried it and it works on my computer! NT 4.0 sp3.0, BDE 4.50, D2
I opened the project in Delphi, run it and imported test.txt

No problem at all....

Sorry I can't help you right away.
Regards, Zif.
d4jaj1Author Commented:
I did some more testing any only found 3 problems.  1) the program will crash if the text file is empty (completely blank - no field names either).  2) this the text file has field names, but does not have any records, it will create the table and add the field names as records (I think - it told me 15 records processed)  3) the CSV file type doesn't exist in the open dialog box (this is simple to fix).  If I change the extention to TXT, it imports teh file correctly.

Now, if I can get it to work in the IDE I could help with this debugging.
ZifNabCommented:
strange that you can't get it to work in the IDE.....
ZifNabCommented:
d4jaj1,

What really happens when you load the project into Delphi and then run it?
1) easy to solve
2) we'll have to look at the source.
3) as you told, easy to solve.

regards, Zif.
d4jaj1Author Commented:
All,

It's weird.  The line the error occurs on is 247 with the code Ft := FindFieldType(ExtractNextWord(Ptr, Delim, '"'));.

BUT - it only happens after a few records are processed.  I stepped through the code add it appears to work okay until it gets to a certain line (which one  I don't know).  Works at runtime - doesn't at design.  Why me ;0|
ZifNabCommented:
Come on! Head up! We'll find a solution!!

I'll look tomorrow to this thread, so leave as much as information you can!

c.u. Tomorrow! Regards, Zif.
interCommented:
Hi and good morning(local time 9:30 a.m.)

Regarding the program stopped at IDE set
  Tools->Options->compiler->Break on exception check box to false.
The probable reason is I determine the fields with EXCEPTION such as
  let F : string be a word to be determined. For example to check if it is integer
  try
    StrToInt(F);
    FieldType := ftInteger; // if there is no exception this line is executed
  except
    // the field is not integer
  end;

Any way doing such thing with exception is not the optimum way of determining the field type but it is EASY. Namely, the exception handling requires several hundered of asm lines. I fix the problems you have addressed and also try to find the field types without using Delphi routines (such as StrToInt, StrToFloat, StrToDate, etc...)

Regards,
Igor
ZifNabCommented:
Hi all,

Local time 9:50.... goodmorning....

Yep, Igor, that's it 'break on exception' (didn't thought on that)

Ok, your're already working on the changes....

I'm here if somebody needs me...

Regards, Zif.
ZifNabCommented:
Hi Inter,

I don't know why it isn't good to find field type on exception, but anyway a solution for niot using exception is this : (At least I think it is!)

1. Asume field is of type string;
2. Iterate every char through string.
    If only numbers are in string [0..9] --> fieldtype is of integer
     (here you can go further --> how big is string with numbers? What is max number?
      ---> fieldtype is longint, byte, ....
    If only numbers [0..9] and ['.',','] then fieldtype is float, double, ....
    ....
3.  If not these above ---> fieldtype is string..

What do you think of this approach?

Regards, Zif.
interCommented:
Hi there friends,

Thanks for your comments Zif, I make the modifications. Trying to determine integers, floats or booleans are easy but the is a DATETIME problem. So I rewrite the routines(or just get from sysutils and modify) so that they do not raise exception. So the total processing time of 30 secs on 1999 records drop to 19 secs because preprocessing boosts up. I am going to send the code to both of you. If you have comments or want additional functionality please write down to me (This question grows beyond ones reach I got your comment mails from EE but able to get complete page in 30 mins!)
Zif.. : You are a true friend as well as a good programmer, accept this, there is no way you can hide from the fact :-)

Regards,
Igor
ZifNabCommented:
Hi guys,

Inter : 10x for the compliment, but I think you're miles better then me, so are others.

Now, I was thinking...
We better give the user the possibility to choose the type :
Why?
 Well for instance, the date, time types but also what if the integer value is an
 autocreate field? We look at the first 14 records and choose which type it is....
 We choose the wrong format then....

So, maybe it's good to propose a field, but still let the user decide if that type has to be changed are not....

Regards, ZiF.

 
d4jaj1Author Commented:
Hi everyone, just work up (it's nice to have a laptop).  I'm looking at Inter's email right now and will post my comments in a few moments.
d4jaj1Author Commented:
Okay, here's what I found.  The import routine worked correctly with text and CSV files where
  Blank File - process aborted gracefully
  Titles, but no records - process aborted gracefully
  No Delimiter between fields
  Delimiter bewteen some fields
  Delimiter between all fields.

Did not test what happens if a delimiter is within a field - do you know?.  Great work Inter.  I corrected the filter on the open dialog box to CSV from CVS.  I had a question - I noticed you added 2 fields to the form, delimiter & null string....

I assume the delimiter field really represents the 'separator'.  If that is the case, this routine would work for any custom delimiter except - maybe Tab Delimited files?

Secondly - when would a null terminated string occur?

Thanks again for a great utility.  I suggest you add this to Torry's Deli, etc. and get credit for a job well done.  You ARE a master programmer.  Go ahead and answer this question so I can reward you the points.

PS What ever happened to that Super Grid you guys were building?
ZifNabCommented:
Hi all,

SuperGrid, well that project stands still for the moment..... Otherwise I can look for another job I think.... I've too much other things to do for my project at work... .too bad :-(

I was just thinking... Is program protected against fields with spaces in their name?

Regards, Zif
interCommented:
Dear friend,

1 -Yes, by delimiter I mean field seperator, and that edit box is functional(you may change it to the drop down list or like in MsOffice clone radio buttons and a custom type to give non printable characters such as tab-i'll also fix it)

2- I am sorry I can not understand the second item

Thanks for compliments also, I am a bit lazzy about the Grid and do not know about Zif. If Zif don't mind(or suggest smth else to share the points) I'll answer the question.

Regards,
Igor
ZifNabCommented:
Hi all,

Just give Inter all the points, he deserves all of them.

Regards, Zif.
interCommented:
Thanks much,

I open a new discussion for this question. Please look Delphi Topic Area. We make it complete before publishing.

Regards,
Igor

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Delphi

From novice to tech pro — start learning today.