Import data into a memo field greater than 254 chrs using vfox9 3

[taterday]

I have read and printed tek-tips on the explaination on how to import.

I need to import about 1000 chars per memo into a memo field. Be nice to maintain the formatting.

Using vfoxp 9, are there a better solutions that I can be pointed to or does some one have a sure fire that works. I would really appreciate any help on this.

 

[bborissov]

From where you should import these chars?
What do you mean with "maintain the formatting"?



Borislav Borissov
VFP9 SP2, SQL Server 2000,2005 & 2008.

 

[Mike Lewis]

Yes, as Borislav says, we need to know where this data is coming from.

Regarding "formatting", if you mean things like fonts and styles (bold, italic, etc) ... well, there's no standard way for VFP to store that information in a memo field. You can use something like HTML, but VFP wouldn't know how to interpret that.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[taterday]

line feed for the formatting, nothing special. Sorry for the confusion.

I don't have thoughts on what to use on import from. I would love a suggestion to the best way. Some users have Word, Excel, Access, text? Best would be text, so everyone would have that.

I want to follow your advise to the best way for the 1000 chars to import into my memo field.

thank you,

 

[IlyaRabyy]

To maintain the formatting (I surmise you are importing not a pure ASCII text but something like RTF or DOC), colleague TaterDay, you need to make that Memo field of yours a Binary. Thus, you'd make sure that whatever is there in the bytes in your file (and that includes what used to be called "controlling characters") will be imported into the FPT part of your table "as is", that is intact.

How to achieve that, depends on how you introduce this field - in code on run time, or using some visual designer tool.

If it's the former - it should be similar to this:

CREATE TABLE MyDBF FREE (MyMemoFld M NOCPTRANS)

if it's the latter - presuming you are using Table Designer, make sure that you chose Data Type as Memo (binary).

HTH.


Regards,

Ilya

 

[taterday]

I need any solution to be in the code at runtime. My users are not given anything but the exe to use. This importing will happen over and over again as other projects are added.

 

[TamarGranor]

Where does the data come from? What format will the users have it in?

Tamar

 

[taterday]

They will be given instruction depending on their software that they can use. Some have documentation in excel, while other have this access, and ms word.

I could give them a button, probably using visual basic, to export the data? I need to be able to import using the best solution. I am depending on someone to tell me with their experience this is what they would do. Sorry I can't give you a better answer. Since I don't know what I am doing.

 

[taterday]

My original thought was a text file or cvs. I can import this but don't know if they is the best solution. I was hoping vfp 9 would maybe have a feature that I have not used. I don't see how they can export from excel or word a field greater than 254 from these.

Any suggestions?

 

[JRB-Bldr]

Some have documentation in excel, while other have this access, and ms word.

With that in mind, I'd leave the documents in the original file format and store them somewhere on the server.

Then, by knowing where the file(s) 'live' and the filename, that data (path & filename) - not the data within the file, can be stored and referenced when needed - a MUCH better approach to use.

Good Luck,
JRB-Bldr

 

[taterday]

I agree that sounds good, but they want to share the data with other departments, some not located in the same building and do not access. Company policy. They can share tables and the exe is installed on everyone with access.

The suggestion was excellent. I wish I can use it.

 

[JRB-Bldr]

they want to share the data with other departments, some not located in the same building and do not access

If the users would be able to share the data that you provide with your originally proposed idea, then they should be equally able to share the data files in their original format - data access is the same either way.

Your application would control where the original files would be stored.

So, for example, if you planned to store your VFP data files in F:\Procedures\Data, then you would store the original files there and store their references (path & filename) in your data table.

Based on what you have told us, there would be no difference.

Good Luck,
JRB-Bldr

 

[taterday]

If we could only control our own world. Management has spoken. The info is to be in the apps memo fields. Now if I just knew how to bring into the memo fields the large amount of data.

Thank you for your advice.

 

[Olaf Doschke]

As far as I understand you want to import data you also export, so you are really free to choose whatever format. Then why don't you create dbfs, zip them for the export, unzip those and append them in the import?

Bye, Olaf.

 

[JRB-Bldr]

Management has spoken.

'Management' is not always the best qualified individual(s) to control the detailed design of a project.

There are many times when management needs to be 'educated' on the pros and cons of an approach - and then 'sold' on the best approach.

Of the 'cons' to present is the possibility of losing everyone's procedures if the VFP Memo field file gets corrupted - which has been known to happen occassionally especially when it has LOTS of data in it as you propose with 1000 chars per memo into a memo field

Finally sometimes what 'management' doesn't know about the details of a design, doesn't matter as long as the overall project meets the basic overall design goals.

Good Luck,
JRB-Bldr

 

[JRB-Bldr]

Another thought if you are forced to save the data itself would be to use a SQL Server backend. The SQL Server Blob fields are not nearly as limited in character length.

Good Luck,
JRB-Bldr

 

[JRB-Bldr]

Check your VFP9 Help.
Within it do a Search for "Capacity" and you will see that some of the previous maximums have changed:
Maximum # of characters per character string or
memory variable: 16,777,184

You will also see that a VFP Memo field can hold more than 254 characters (that applies to a Character field, not a Memo field).

Try the following to test for yourself:

* --- Create a Test Data Table ---
CREATE TABLE c:\temp\temp2.dbf FREE (test M)
* --- Populate a memory variable ---
cTest = ''
FOR LineCntr = 1 TO 1000
   cLine = REPL('A',200)
   cTest = cTest + cLine + CHR(13) + CHR(10)
ENDFOR
* --- Add a new record to Test Data Table ---
SELECT Temp2
APPEND BLANK
* --- Populate the Memo Field 'test' ---
REPLACE test WITH cTest
* --- Examine the results ---
BROWSE

Even if I still think that the approach is wrong for a number of reasons, this test should aleviate your concerns about the 254 character limitation and about retaining the CrLf formatting.

Good Luck,
JRB-Bldr

 

[taterday]

Thank you. I will attempt this later today. Have to put out another fire and attend a meeting.

I will let you know.

 

[Dan Freeman]

The only *real* limit on a memo field is the 2GB file size limit on the entire memo file (i.e. all records). Memo fields can hold a lot of cr*p.

If you have the option of specifying file formats on the incoming data, specify text files. Then you can:

Replace thememofield With FileToStr("thefile.txt")

This bypasses most string limits because it doesn't actually create any string. It's just a blast of data directly into the memo field.

If you're stuck supporting any and all incoming data formats, then you're really talking about a "Document Management System" and there's a lot to be found about that on the google machine.

 

[Olaf Doschke]

jrbbldr, dan

the 254 char problem is immanent, even if you use memos, when talking about import of data, as eg append from txt,csv,xls does not append into memo fields.

But as far as I understand this app is supposed to import, what itself exported, so there is no problem, if you export data into dbfs, as you can append those including memo fields.

Bye, Olaf.