Import or Append what else could work for a big comma-delimited file to be transferred into *.dbf ? 6

[german12]

When I have a textfile like this:

aa105, bbb, ccc, ddd......
aa106a, bb, cc176, ddfa34....

and a very huge amount of further lines.

it is of course possible to transfer that into a *.dbf - file by using

[FOR lExpression] [[TYPE] [DELIMITED [WITH Delimiter | WITH BLANK | WITH TAB | WITH CHARACTER Delimiter] | DIF | FW2 | MOD | PDOX | RPD | SDF | SYLK | WK1 | WK3 | WKS | WR1 | WRK | CSV | XLS | XL5 [SHEET cSheetName] | XL8 [SHEET cSheetName]]] [AS nCodePage]]

but that only works, when the amount of data does not exceed the restricted 2 GB Limit for a *.dbf in VFP.

Is there any possibility to pick only the 2nd. "column" in the sample above, when only that is needed?
(It is the "column" with bbb, bb above).
So instead of dividing a textfile "horizontal" into a lot of parts, could it be that it could be done "vertical", by filtering only every 2nd. string (or 3rd,4th)(which could it make possible to split the text into several
*dbf's and therefore it could be possible to create just a few *dbf's?

Again - to take that sample above:
Can I create the following dbfs somehow?:

DBF#1: aa105
aa106a

DBF#2: bbb
bb

DBF#3:cc
cc176

DBF#4:ddd
ddfa34

Could low-level functions eventually help here?
Unfortunately I have no experience with that commands.

Thanks in advance

Klaus


Peace worldwide - it starts here...

 

[Chris Miller]

I see what Mike is after, so far I only used Memory Mapped Files for IPC (interprocess communication), not for handling file mapping from a real file.
Here's a good introduction to the topic:

https://www.labri.fr/perso/betrema/winnt/manamemo.html

I don't yet know how this could work with VFPs APPEND FROM filename TYPE DELIMITED or TYPE CSV. But if Mike say it's an option I think he already has done it. I guess making use of APPEND FROM ARRAY with arrays created by ALINES.

I said I think there must be API methods faster then reading line by line with the Scripting.FileSystemObject in my post at 15 Aug 21 20:24. I think the easiest way is to make use of ReadFile API:

https://docs.microsoft.com/en-us/windows/win32/api/fileapi/nf-fileapi-readfile.

I actually work on this with the detail feature of asynchronous reading aka Overlapped I/O, which means you could make ReadFile read a chunk while you process the previous chunk in parallel.

Chriss

 

[GriffMG]

Olaf, you can make VFP SQL do joins on record number - it's just another field to the VFPOLEDB so

SELECT Recno() as RecNumber from myTable

Is valid... maybe not so much with MySQL or M$SQL

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[german12]

The possibilities of working with single column files in VFP
In some ways "extends" the 2 GB limit significantly.

I played with it a little.

From the postcode file pp-2018.csv (179.3 MB) I got line by line
16 individual dbf files with 1 column each created.

(The above program (by Mike Lewis / Chris Miller) was used.)

This resulted in 775,409 dbf data records per dbf-file.

Then I took out the file with the 1st column (field width 28 and well filled) and copied this file 65 times into a large file (with append mode)

This large file now had
50,401,585 records and the
File size was 1.965 MB
(So ​​almost the limit for VFP.)

VFP had no problems after that file had been indexed.
Found a certain record from 50 Mio in a hardly measurable short time by a seek.

There are certainly many problems with the vertical structure of the
Single column dbf's for groupings, summations, selections etc. but at least the 2-GB-limit of VFP is only reached in this example with

50 million data records (characters) with a field width of 28.


That's what I wanted to know with this example.

Klaus

Peace worldwide - it starts here...

 

[german12]

This is the file "Gesamt.dbf" with > 50 mio records.

Peace worldwide - it starts here...

 

[Chris Miller]

Klaus, thanks for describing what you did.

You can easily compute how many rows are possible by (2^31-HEADER()-2)/RECSIZE().

On the other hand that does not take into account CDX and FPT, which have varying size per record and more or less bloat, but that's less important when you go for char fields only.

But I don't know how you got to 250 dbfs. I split the large csv into over 250 csv files, but that can be a lower number. Just depends how large you want each batch of records you want. I only used one Import cursor and then 6 DBFs. So even when you count in the Import cursor as a DBF, only 7 DBFs are involved.

The 262 split csv files can be erased after the import.



Chriss

 

[german12]

Sorry Chriss,
you are right, I confused the number of csv with the number of dbf-files.

Thanks for your correction and the formula.
I have that sentence deleted in my document.

Klaus


Peace worldwide - it starts here...

 

[german12]

Hi Mike (Yearwood),

https://www.tek-tips.com/viewthread.cfm?qid=178781...[/URL]]

I read the thread above where you and Olaf discussed a certain topic (Rushmore / SQL and various commands in VFP regarding their speed).

That was 3 years ago now.
Olaf can no longer be seen in this forum with contributions.

As I have seen, it was a very complex subject that you could not agree on.

Let me put it this way: I would think it would be a shame if your knowledge no longer appears here - I am one of those people who would like to learn something more - and many others still are here.
Many things are also difficult to understand - but it would be very nice if you were there again.

There is always someone with whom one cannot agree - but about a discussion with you that we all (including you) still have
being able to learn something - that would be good, wouldn't it?

Nobody is perfect - and whoever says that about himself is not perfect for that reason alone.

I would be very happy if you were there - and I'm certainly not alone in that.

Nice greetings from Germany
Klaus

Peace worldwide - it starts here...

 

[german12]

Hi Mike (Yearwood),
now I have finally understood what annoys you.

As you know, English is not my mother tongue - that's why
perhaps that has not been understood quite correctly.

This was written by me:

Hi Mike Yearwood,
That sounds interesting
Can you use a code example to illustrate how to access the API Memory Mapped Files?
I would also be interested in the opinion of the other members in this VFP forum on this topic.

With "this topic" I should have written "YOUR topic" -
and that was really my intention too.

I wanted to hear what others know about YOUR topic.
I hadn't heard of it before.

Excuse me PLEASE.

In no way did I want to annoy you or ignore your suggestion - on the contrary - I wanted to stimulate the discussion and learn more about it.
I hadn't seen a code example yet.

I did not know your exchange of views with Olaf on this subject at the time.
After a 20-year break after retirement, I'm not back here for long.


I think and hope that it is clear that I have no one here
insult or make a any comment unimportant.

This forum is far too valuable for that.

Klaus

Peace worldwide - it starts here...

 

[Mike Lewis]

create cursor mycursor (a c(10))
a=10
?a/2

I'm embarrassed to say that I was once almost caught by that myself. Not in a test for hiring, but in some code which a client programmer showed me. A greatly simplified version:

x = "Something"
* some more code, none of which changes x
IF x = "Something"
  DO This
ELSE
  DO That
ENDIF

So why did it [tt]DO That[/tt] rather than [tt]DO This[/tt]? It took a long time before it dawned on me.

Much as I want, I cannot SELECT * EXCEPT FIELD1, FIELD8 FROM TABLE

True. But I vaguely remember someone writing some code that generates a SELECT statement. You give it the name of the FROM table, and tell it which fields from that table to exclude, and it generates a SELECT with all the other fields included. Not quite as convenient as [tt]SELECT * EXCEPT ...[/tt], but it does save a bit of typing.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[tomk3]

Hello,

sorry to jump in :
vfpa may be a solution, it supports files up to 2048 TB with memo up to 128 GB

http://www.baiyujia.com/vfpadvanced/f_vfpa_history.asp

(search large)

Regards
tom

 

[GriffMG]

Mike, why does it do that not this?

Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Chris Miller]

Griff, here's another hint:

Create Cursor _screen (caption I)
insert into _screen values (42)

? _screen.caption
? caption

Chriss

 

[Mike Lewis]

Another hint:

The reason for [tt]DO That[/tt] is the same underlying issue as in Mike Y's [tt]?a/2[/tt].

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Steve Meyerson]

FWIW here's my attempt to simplify the oft "Data mismatch" error:

a/2 looks first for a FIELD named "a" to divide by 2
m.a/2 looks for a MEMORY VARIABLE named "a" to divide by 2

Moral of the story: Preface variables with "m."

Steve

 

[Mike Lewis]

Steve, you are spot on. The point Mike Y was making is that VFP allows both fields and variables to have the same names (with some exceptions), and where there is ambiguity, it is the field that has priority.

That was also my thinking in the example that I posted.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[GriffMG]

If I spot a typo or little error, I try to let the poster know discreetly if I can... and appreciate the reciprocation.



Regards

Griff
Keep [Smile]ing

There are 10 kinds of people in the world, those who understand binary and those who don't.

I'm trying to cut down on the use of shrieks (exclamation marks), I'm told they are !good for you. ​

 

[Mike Lewis]

Mike Yearwood, thanks for that interesting code. I also tend to avoid [tt]SELECT *[/tt]. Regarding my comment about [tt]SELECT * EXCEPT[/tt], I was merely reporting what someone once showed me (unfortunately, I don't remember who or when).

it seems there is some kind of animal farm rule in place

Nice Orwellian reference there, Chris.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[german12]

Mike Yearwood,

when I look back what you said here in this thread, I find this:

No one should need to be invited. Everyone should be contributing their input freely and without fear.

and I also find this

Chriss likes to dismiss my input as if his is somehow perfect. I want nothing more to do with him. Please ask him to leave me alone and stay out of my discussions.

That sounds like

Thou shalt have no other gods beside me

I started this long thread with my original question.
Many people have participated so far.

Steve Meyerson
atlopes
Chris Miller
Dan Olsson
Mike Lewis
HandleMG
tomk3
...and you

I like that - because the variety of opinions means that
a problem is seen from all sides - and not just from one side.

I don't mind at all if a comment is similar to a comment that has already been made.

For me this is just a confirmation that the best solution is when the answers are more and more confirmed.

Therefore - please - let it stay the way it is.

If only dialogues are running here, then it is no longer a forum,
but just a lesson.


I firmly believe that nobody here wants to offend or put themselves in the foreground.
There are a lot of good experts here - and I am always delighted when they spread their knowledge here - and for allegations of rudeness or insult or even requests to exclude a participant from a thread may be in other forums - but here's for it no place.

There is also a lack of evidence that it is so... and they would also be pointless.

I will continue to look forward to anyone who helps me with my threads here.
I don't think that I'm alone with my feeling.

Best regards
Klaus



Peace worldwide - it starts here...

 

[Chris Miller]

I think it's the moment to come out and admit I'm all the accounts here, the other two Mikes (Lewis & Gagnon), Marco Plaza, Koen, Dan, Tom, atlopes, Dennis, ggreen, Griff, Steve, Klaus, Andrew, Rajesh, Gerrit, Nigel, Mary, Tamar, you name it.

You could do the same and create a forum yourself. It's fun to talk to yourself. No objections, no other opinions, no feelings, your rules.

Chriss

 

[Mike Lewis]

You could do the same and create a forum yourself. It's fun to talk to yourself. No objections, no other opinions, no feelings, your rules.

Actually, I once did exactly that. I created an internal forum for a client. Before going live, I tested it by creating five or six user accounts, all of whom were me. I gave one of them the name Alan Smithee, which I thought was quite clever. But the significance of the name was lost on the other members.

Unfortunately, the forum never went live. The client was worried about security issues and cancelled the project.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads