255 field limit - How to import a file with more than 255 fields?

[montypython1]

Greetings,

What is the best way to import a file with more than 255 fields (since VFP has a 255 field limit)?

My input file has approximately 300 fields. I actually only need about 150 of these fields, but unfortunately some of the required fields are above 255. I read a Tek-Tips posting from about 10 years ago, but did not fully understand the solutions (ex: talking about lower-level solutions, etc).

Has anyone come across this problem? And has anyone come up with a good solution for importing a file with more than 255 fields?

Thanks for any advice.
Dave Higgins

 

[Dan Freeman]

255 is a hard and fast limit, both on reading and writing.

You'll either have to use low level file functions to pick out the needed data elements, or, if the number of ROWS is low enough you could read the entire file into an array and then parse each resulting row.

lnRows = Alines(FiletoStr("thefile.txt"))
For lnCount = 1 to lnRows
* process each row in the array
Endfor

But when you're already working around limits, I'm betting you'll end up with other problems as well.

There isn't a good *general* way to do this. You'll have to fiddle and figure out which way works best in YOUR situation.

 

[Dan Freeman]

I bungled the Alines() syntax, I see, (check the help file) but the idea is still one to fiddle with.

 

[Olaf Doschke]

It would help, if you'd link to the found thread, instead of just mentioning you don't understood it.

dan has a good base idea, but if the file has 300 columns, it presumably also is large. You shouldn't process more than 16MB in strings, some string functions can handle larger strings, but nobody knows for sure which ones and which not, I'm not sure about ALINES, it is likely to be one of the functions capable to handle larger strings, as arrays are also not limited to 64k elements anymore since VFP9, and that wouldn't make sense, if ALINES, a function creating an array, coouldn't handle more than 64k lines for example.

Nevertheless an approach to read in single lines surely will stay below that string size limit.

So instead of ALINES() creating single line array elements, you could use FOPEN and FGETS to read single lines in the first place.

lnFH = FOPEN(filename)
If lnFH>0
   Do While !FEOF(lnFH)
      lcLine = FGETS(lnFH,8192)
      * process a single line, extract the wanted 150 columns out of all
   EndDo
   Flcose(lnFH)
Else
   Messaebox("couldn't open filename, specify a filename.")
Endif

8192 is a size limit, it does mean FGETS reads lines shorter than 8192 bytes or 8192 bytes at max. Unfortunately FGETS does not read wider lines, the max bytes limit itself is limited to 8192, but that still gives about 27 chars per column, should be sufficient if several columns are short chars, dates, numbers or such. It does not mean FGETS reads chunks of 8192 bytes, it reads until either carriage return or 8192 bytes is reached, whatever comes first.

And "process a single line" could look like this, if the data is comma delimited:

For lnColumn = 1 To Getwordcount(lcLine,",")
   lcValue = Getwordnum(lcLine,lnColumn,",")
   * put lcValue or VAL(lcValue) or CTOD(lcValue) 
   * or whatever converions from text to the column 
   * type into the corresponding import table field, 
   * depending on the field type of the column, 
   * or skip that column, if you don't need to import it.
Endfor

That said there is no easy one size fits all solution, if data is in text files and you have no specifications of column types you will have a lot of work specifying the column types. But this is also true, if you could use APPEND with a file containing less than 255 fields.

Bye, Olaf.

 

[Mike Lewis]

Dave,

You didn't say what format your input file is in.

The other replies are assuming it's a text file, with comma-delimited fields. If that's right, I'd go along with the idea of reading it via FOPEN() and FGETS(). But I suggest you consider reading a single line at a time, and then copying the fields to a single-dimension array, using ALINES() with a comma as the "parse character" (fourth parameter). (The array would have one row for each of the fields in the current input record.)

You would than append a blank record to the output table. Then loop through the array, replacing the required values into that record.

Does that make sense? If necessary, I can throw together a bit of code to show you what I have in mind?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[montypython1]

Hello Dan, Olaf and Mike,

Thank you for your suggestions.

I should have mentioned that the file is NOT very large (less than 2MB, even though it is 300 fields wide). It is "pipe-delimited", if that makes a difference with using FOPEN() and FGETS().

If FGETS() limits each line to 8192 characters, I think I will be okay (but just barely ... 300 fields with an average field length of less than 30 characters each).

I'll try your suggestions and let you know.

Thanks,
Dave

 

[Mike Lewis]

Dave,

The fact that it's pipe-delimited won't make any difference whatever to FOPEN() or FGETS().

If you adopt my suggestion of reading one line at a time, and copying that to an array with ALINES(), then all you need to do is change the fourth paramter to ALINES() from a comma to a pipe.

Given the whole file is relatively small, another option might be to use FILETROSTR() to read the entire file into a variable. Then use ALINES() to separate the lines into an array. Then loop through that array, calling ALINES() again to separate the fields into another array.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Olaf Doschke]

Hi Dave,

If files are that short, reading the whole file via FILETOSTR() makes sense and you don't have the barely working limit of 8192 bytes per line from FGETS(). There surely is a way to get this working with FOPEN(), FREAD() etc., but it will be messier code than using FILETOSTR() and ALINES().

ALINES() will process longer lines, too. ALINES() really just looks for the Parsechar (4th parameter), if given, or line feed (chr(10)) as default end of a line.

Bye, Olaf.

 

[johann59]

Hi Dave,

My main tool I work with is Visual FoxPro and 90% of the files I have to process come in an Excel format, so I'm just going to put out there my 2 cents.

If this is a one time deal, could you bring those records into excel and then just delete the columns that you do not need? There is a bit of work involved, but if this thing is just this once, it might be worth it. I've had to do that a couple of times. I know that doing it thru code is better, but since it doesn't happen a lot, I just got it done that way.

If it is going to be a frequent issue, you still could import in excel and create a macro that deletes certain columns...

And forgive me for this, since I do not post a lot (I don't consider myself an expert), I have to say this forum ROCKS! I've learned so much from all the posts. I almost come here on a daily basis and its awesome to see the advice from guys like Olaf, Mike, Dan, Griff, JRB, and lots of more people. Thanks to all.

 

[Dan Freeman]

Excel is actually a pretty good idea. The entire process can be automated IF (and it's a big if) you can always guarantee having Excel installed.

 

[Olaf Doschke]

Yes, johann, this is actually a good idea. It should even be more intuitive and easy to delete columns in excel. What I'd double check is, if excel mangles values and makes unwanted conversions, eg it could remove leading zeros, where they are wanted, things like that.

Excel does allow to specify the delimiter and column types during load/import, so you can specify that, but it's of course cumbersome for 300 columns. I'd select all columns and set them to Text, so excel does not touch the contents. You might want to choose the correct type of columns, if excel does convert them correctly. And then you can APPEND FROM some.xls TYPE XLS or export the data as txt file, csv or pipe delimited again, whatever.

Bye, Olaf.

 

[montypython1]

Hi Dan and Johann,

Very clever idea using Excel (with a macro) for the data conversion. My only concern with this approach would be speed, since this not a one-time conversion. This conversion process will need to run multiple times per day (and I would prefer not to require Excel to pop up every time they open the VFP application).

From a speed standpoint, I suspect that Mike and Olaf's suggestion to use FILETROSTR() along with ALINES() might be more efficient. If I can't get this approach to work, I will certainly utilize Excel per your suggestions.

Thanks again,
Dave

 

[Mike Lewis]

Dave,

You say "I would prefer not to require Excel to pop up every time they open the VFP application".

There's no need for Excel to be visible to the user. In this scenario, it should be possible to run it invisibly.

That said, personally I wouldn't go for the Excel option in this case. As you rightly say, it would definitely be slower. Also, you've got to be sure that Excel is installed on all the users' computers; and there might be compatibility issues, for example with things like date formats. I would stay with the earlier suggestions, and only consider Excel if all else failed.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips, training, consultancy

 

[Dan Freeman]

From a speed standpoint, I suspect that Mike and Olaf's suggestion to use FILETROSTR() along with ALINES() might be more efficient. If I can't get this approach to work, I will certainly utilize Excel per your suggestions.

Well if you look back to the first reply in this thread you'll see that FileToSTr() was my first suggestion.

If I *were* to use Excel, it wouldn't be visible (it's faster that way) and I wouldn't use a macro. The sole reason for using Excel would be that it is more commodious in the number of columns it accommodates and it's insanely good at parsing. I would only resort to that, though, if other options turn out to have problems.

 

[montypython1]

Dan,

My apologies ... In my previous post, I had meant to also give you credit for your suggestion of using FILETOSTR(). For the reasons you mentioned, I am currently working through the file using the approach that you, Mike and Olaf suggested. Hopefully I won't need to use the Excel option, but still am impressed that Johann thought of it.

Thank you again,
Dave