×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

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

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

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

(OP)
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

Quote (APPEND FROM FileName | ?[FIELDS FieldList)

[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...

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

Hi Klaus,

I have done a similar task many many times. There are many ways of doing this. In general, I usually do it this way (you might have to look up the syntax of these functions):

1. Create each dbf.

2. Convert the csv file to a string using the ALINES() and FILETOSTR() functions. Each line will be one row of the array.

3. Use FOR...NEXT loop to process each line.

4. Use GETWORDCOUNT() and GETWORDNUM() functions to parse each line into individual memory variables and INSERT each mvar into its corresponding table. You might even do this in a single command.

Hopefully, this can get you started, at least until you have specific questions.

Steve


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

Klaus,

Considering that if the source data itself is bigger than 2 GB, there is no way that VFP will be able to properly process the file natively, you may contemplate using the CSVProcessor class at https://github.com/atlopes/csv for this.

CODE --> VFP

* 
* previously: put the CSVProcessor class in scope, something like
*  DO LOCFILE("csv.prg")
*

* prepare the CSV sample data

LOCAL SampleData AS String

TEXT TO m.SampleData NOSHOW
aa105, bbb, ccc, ddd
aa106a, bb, cc176, ddfa34
ENDTEXT

* prepare the CSVProcessor object

LOCAL CSVP AS CSVProcessor

m.CSVP = CREATEOBJECT("CSVProcessor")

* the CSV source data has no header row (so it seems)
m.CSVP.HeaderRow = .F.
* and we want to trim the source data
m.CSVP.InTrimmer = 3

* we need just to import the 2nd and 4th columns, the Bs and the Ds
CREATE CURSOR TargetCursor (Bs Varchar(20), Ds Varchar(20))

m.CSVP.FieldMapping.Add("csv column not imported")
m.CSVP.FieldMapping.Add("Bs")
m.CSVP.FieldMapping.Add("csv column not imported")
m.CSVP.FieldMapping.Add("Ds")

* into this cursor

m.CSVP.Workarea = "TargetCursor"

* note: if the CSV data has a header row, the filtering can be made
*   by mapping columns of the target cursor to columns in the CSV file.

* load from the source string (but it could be from a file)

IF m.CSVP.ImportString(m.SampleData) == 0
	SELECT TargetCursor
	GO TOP
	BROWSE
ELSE
	ERROR "Failed to import data"
ENDIF 

The result:

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

Unfortunately, Steve's method won't work if the text file exceeds 2 GB. The 2 GB limit applies to text files just as it does to DBFs. If your file is bigger than that, you won't be able to use APPEND FROM, or FILETOSTR(), or any of the low-level file functions to read the file.

One solution would be to find a way of splitting the file into multiple smaller text files before importing it. I can't off-hand think of a way of doing that outside of VFP.

Another approach would be to use an external process, via Automation or a COM object, to split the file. One possibility would be to use the File System Object. Something like this:

CODE -->

loFS = CREATEOBJECT("Scripting.FileSystemObject")
loFile= loFS.OpenTextFile("c:\MyFile.txt", 1)
DO WHILE NOT loFile.AtEndOfStream
  lcLine = loFile.ReadLine()
  * code here to add the lines to an array or to 
  * write them out to multiple smaller files
ENDDO 	
loFile.Close() 

That should work regardless of the size of the text file. But be warned: it will be horribly slow.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

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

We had a whole thread about UK address data, which also exceeded 2GB: thread184-1810484: Use a separate file, file type or other method to access nearly 29 million addresses

I see you contributed, too, and have seen Steve split the data.
And you also know my follow up thread thread184-1810935: Extracting addresses from UK.gov price paid data

So you still are looking for a way to handle >2GB files. It does not only matter how much of that data is imported into the target DBF, VFP simply chokes on reading more than 2GB, which it also still needs to do when you use the FIELDS clause to only import into a few fields.

One further problem is, if you specify a field list, the appending still imports coluumn1,2,3 of the CSV file into the fields specified, there is no way to skip columns in the csv. VFP does not map CSV columns to DBF columns and when you limit the fields still uses that mapping for the given fields and skips unspecified DBF target columns in the CSV.

Chriss

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

I'd look into other tools more specialized into CSV file processing. MSSQL import is one way to have no problem with table and csv file sizes, then get what you need from there. Steve did mention usage of a split tool and I used Power Shell. I think the best programming language to work with CSV files is PERL (it's in the expansion of the language name Practical Extraction and Report Language, - as it was new csv was (and it still is) one very common data format and so extracting data from it for simple reporting(lists) was what this language can do best. Python may also work with a CSV module. Both PERL and Python will surely support only fetching column 2 or any combination of columns from a csv simply by having something that reads line by line and allows you to pick items from an array and stream that into a new file or to stdout.



Chriss

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

Mike,

I tried your code just reading the pp-complete.csv data (about 4GB) and that works and needed almost 10 minutes. But the Powershell splitting I used took over 30 minutes. While all that depends on drive speed and splitting does not only read but also write out files, I don't think you can call this slow.

I don't see how you can speed up the reading just because you only want column2. No matter what reads through the CSV data has to parse for commas and line feeds and read in more than just the wanted column, the only way you could skip reading bytes and skip to the next record column2 start would be knowing how many bytes to skip without parsing for linefeed and commas. How would that be possible? Only with fixed size records in the CSV file.

The only part you can speed up by only wanting column2 in the DBF is writing to the DBF, of course. as you only write a small percentage of the data the whole process likely needs something between just reading and splitting up all data.

I still think splitting files is the way to go if you need to process the same data multiple times.

Chriss

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

Hi,
I was going to suggest Notepad++ but found it also has a 2GB limit. Googled "Notepad++ big file" found some suggestions for breaking up bigger files. HTH
Steve

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

Quote (german12)

Is there any possibility to pick only the 2nd. "column" in the sample above, when only that is needed?
I'm sorry if I misunderstood this, but if you only is interested in the second column, wouldn't it be solved if you create a DBF with only two columns and then APPEND from?

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

Dan, you still fail with that idea as you can't read larger than 2 GB files. Otherwise that's an idea for column 2 with just a bit excess data, but when you would need the last column that would mean more waste, you could only make that waste portion smaller when defining c(1) columns up the the one you want or more general for all the columns you don't want.

Still, this will only read in <2GB files.

Chriss

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

It might be worth looking for a stand-alone utility that can split large files into smaller chunks, and which can be driven by command-line parameters, and therefor can be executed within VFP using RUN or ShellExecute(). The one that comes immediately to mind is Split, which is part of the GNU Core Utilities (and which is available for Windows), but there might be others.

But an issue to watch out for: Such utilities tend to split the file at some specified number of megabytes, or at a percentage of the whole file. What you need is a way to split after a given number of lines, otherwise you risk having a break in the middle of a line, which would screw up the records on either side of the split.

It would be worth searching for a suitable utility.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

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

One other possibility ....

Can you open the file in Excel? (Recent versions of Excel can handle 1,048,576 rows. I don't know what the limit is on overall file size.)

If so, you could use Excel Automation to open the file, delete the unwanted columns, and save the result back to a file, which you can then append in the usual way.

I don't know for sure if this will work, but it will cost very little effort to try.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

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

(OP)
Thank you to all of you for your many interesting considerations on a topic that keeps occupying me.
As Chris correctly noted, my question is based on the threads
thread184-1810484: Use a separate file, file type or other method to access nearly 29 million addresses: Use a separate file, file type or other method to access nearly 29 million addresses
and
thread184-1810935: Extracting addresses from UK.gov price paid data: Extracting addresses from UK.gov price paid data

The problem is that the amount of data that has to be processed today has become more and more enormous over time - and it's not just the 27 million postal addresses.

The founders of VFP probably could not have imagined it themselves at the time, otherwise they would certainly have built in a method that would have read in files with a different format than their own line by line.

In the above threads, the csv file first had to be split into 262 segments, and it was clear to me that it mainly had to do with the horizontal split of the huge csv file.

Now I wanted to know that the csv file could also be read vertically - and in order not to let the previous threads get too long, I tried to ask in a separate thread with a small model as an example.
That would have the advantage that you would have a lot less work, and
if each VFP file were fed only with "columns" from the csv file, then you would only have as many * .dbf's as the fields required.
I thought that you would then work with a lot fewer files in VFP afterwards, each of which would not reach the 2 GB limit as quickly
and could make programming and searchability much easier.

It would have been too good.....

Klaus

Peace worldwide - it starts here...

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

Klaus, yes we understand the problem. Have you had a chance to consider any of the solutions suggested so far?

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

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

It didn't let go of me how this could be done faster, because 4GB in 10 minutes is only about 7 MB per second and that's really slow. I can simply copy the file from one to another directory (copy, not just move) in about a minute.

I don't have a solution yet, but you should in fact be able to read the file much faster.

The Windows API should have functions for low level file handling.

Chriss

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

Quote (Klaus)

Now I wanted to know that the csv file could also be read vertically

You got the answer: No. In a CSV file columns are defined by commas, of course, but as the values have varying width, you can't just read one column without also parsing the rest.
Let's just consider the case of second column, then after that is found the parser could skip to the linefeed next. Well, but what does it mean to skip to line feed? It means reading following bytes from the file until 13,10 is found. So you can't skip something, really. CSV can just be processed fully.

As you saw the file reading itself should be able to go faster. One problem besides the 2GB limit is that VFP isn't asynchronous. You can't let the OS read the file while your VFP process already processes blocks read, VFP's own FREAD will wait until the bytes are read in. APPEND has it better, as both file reading and processing is all in one in a C routine that could make use of parallel processing. But the 2GB limit is hindering this idea to work, it's not just a limit of DBFs and related files, it's a limit for all files.

If you simply FOPEN() a file larger than 2GB you get "invalid seek offset". One of the first operations FOPEN does is determining the file size by seeking to the end and there is the limit of VFP about file processing.

So ideally you need something else that can read a file in and provide chunks of the file to VFP somehow. In the simplest case a FREAD equivalent in the Windows API that reads in say 8KB per call, even if this won't be possible asynchronous and you can't work on the first 8KB while the OS reads the next 8KB, this should be possible to run faster. But it will also run through all bytes of the file, CSV is no structure that can seek to "column2" just like you can do in a DBF. To find Column2 it has to orient by line feeds and commas. And that means reading all bytes. When you get the file presented in an editor the linefeeds are processed to, well, lines, you yourself then can easily "see" column2, but this is only possible because the editor readd the whole file and made a line feed for the line feed control codes 13,10.

Chriss

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

(OP)

Quote (Mike - Have you had a chance to consider any of the solutions suggested so far?)


Mike, I think I understood your suggestion to try an external process.
I don't mind if it's slow - if the csv doesn't have to be constantly updated, then you could certainly work with it.

Your code is good, because it immediately "feeds" a number of
dbf files with the comma separated columns from the CSV and
that's already what I wanted.
Of course there has to be a "brake" to prevent overflow
in VFP - but first you have to see how far
one comes with your suggestion.
Perhaps in most cases, even with large CSV files, it is not even necessary because the load on the individual VFP files increases
done slowly.

Here is my primitive code

*!* This program looks into a multitext.txt - file
*!* which has this contents:

*!* aaa,bbbb,ccccc
*!* dd, eee
*!* fff,ggg,hhh,iiiiiii
*!* jjj, kk, l, mmmm, nnnn, oooo



CODE -->

CLEAR
CLOSE DATA
loFS = CREATEOBJECT("Scripting.FileSystemObject")
loFile= loFS.OpenTextFile("multitext.txt", 1)
DO WHILE NOT loFile.AtEndOfStream

	lcLine = loFile.ReadLine()

*Count the words in the line, which are delimited with comma
	thiswords = GETWORDCOUNT(lcLine, ",")

*In this sample I created some dbf's from No.1 to No.5, assuming that
*I only need the csv-column from 1 to 5
*All dbf's have only one field which is called named "col1" to keep it simple.

	FOR i = 1 TO thiswords
		filenum = "dbf"+ALLTRIM(STR(i))
		W= GETWORDNUM(lcLine,i, ",")
		W = ALLTRIM(W)
		USE &filenum
		APPEND BLANK
		REPLACE col1  WITH W
	ENDFOR

* code here to add the lines to an array or to
* write them out to multiple smaller files
ENDDO
loFile.CLOSE

* If the file is too big, I have to code
* a "break" - but that should be done later.


DO showme
*IN linesuch.prg
DO killme
*IN linesuch.prg

*In this case I uses dbf's -
*it should of course be cursors due to speed

PROCEDURE showme
	USE dbf1
	BROWSE
	USE dbf2
	BROWSE
	USE dbf3
	BROWSE
	USE dbf4
	BROWSE
	USE dbf5
	BROWSE
ENDPROC

* To try it again
PROCEDURE killme
	USE dbf1
	ZAP
	USE dbf2
	ZAP
	USE dbf3
	ZAP
	USE dbf4
	ZAP
	USE dbf5
	ZAP
ENDPROC

CLOSE DATA 

Thanks for your help
Klaus

Peace worldwide - it starts here...

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

(OP)
This is the result:

Peace worldwide - it starts here...

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

This just has the same problem VFP has with CSV. If a character column is delimited with quotes, a comma within them is not a column separator. This case might not be important to you.

I don't see a big benefit of single column DBFs, when you'd like to do SQL and joins, there's a key necessary. I did use two column tables with key,char columns for sake of normalizing addresses. And then the actual address record has to have all the keys to the details.

And in case of the addresses it would work okay, as you don't do much more than looking up an address and put it together for printing an invoice/bill/delivery note and last not least the address on an envelope or packet label. When thinking of an online shop example. Similar for invoicing customers for services like freelance programming.


Edit: This actually has a name: Column store. But the way to make use of this for either performance or storage benefits only is achieved by special indexes on such columnar data. Let's say you sort each column and in a numeric column that has lots of similar values, a row difference often may be 0, so you can store all numbers by just storing the first number and then differences to the next column. That often means 0 difference and that's easy to compress. But now getting at row N means reading n rows and adding all the differences.

Just one example.

Querying only field2, 5, 7 from a wide table you only need the files for these fields. You don't need to skip the bytes of other columns, so disk accesss and reading bytes gets down, but that will need a query engine specialized on this. There's much to do to get this concept working better than usual row based data.

Chriss

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

(OP)
Chris - thanks for your very prompt answer.

Quote (Chriss)

This just has the same problem VFP has with CSV. If a character column is delimited with quotes, a comma within them is not a column separator. This case might not be important to you.

I agree - it depends on the structure of the csv - and what has to be done in VFP: That is really a problem.

Quote (Chriss)

I don't see a big benefit of single column DBFs, when you'd like to do SQL and joins, there's a key necessary.

I see a benefit in speed
You can remember, that I tried using the FINDSTR command which could find a certain expression in a file of 27 million addresses within 6 seconds.
I can imagine that with a SEEK command in single column-dbf (eg. with the name of a person) would be much quicker - and as the recno() in all other dbf's are identical for the full
details of the same person (name, location, street etc.) the search could be quioker.
But that has still to be tested.

I repeat that, because that was in the beginning what Steve wrote in his thread:

Quote (Steve.)

The purpose of this project is for someone to be able to enter a postcode, search for it and the address will be auto filled elsewhere.

I see my thread caused a lot of discussion - that was and is very educational - I hope not just for me.
Regards
Klaus




Peace worldwide - it starts here...

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

Okay,

Quote (Steve)

The purpose of this project is for someone to be able to enter a postcode, search for it and the address will be auto filled elsewhere.

A postcode alone will not identify an address, so there's not enough benefit of the single columns. Also you don't reduce all London addresses, London will be repeated many times. So, while the vertical split of data into single columns may be able to overcome the 2 GB problem, you'd have a lot of redundancy for the price of identical record numbers. I'd prefer a central table with all the foreign keys.

For example, when you look for London addresses in the DBC my earlier code generates, you get a city Id of 597 and can find all London addresses by SELECT * FROM addresses WHERE cityid = 597. It's about 2 million. And the DBC only stores LONDON once in the cities.dbf

So in the normalized structure you only need one Id column for each data column, which adds +4 bytes per row, but saves about 2 million times LONDON, that's not just 12 million bytes but as the city column is char(30) 2 million * 30 bytes, circa 60 MB.

The goal doesn't have to be smallest storage, that's clear, so your idea also works for the case the list of records is your final goal.

With the normalized form including keys for each value I could go one more step and put all texts, no matter whether they are city names, postcodes or whatever into one table and have a "full text search": When you locate/seek for one string you can take the id and select all addresses that have this id in any column.

Having a key and not just the recno, you get back VFPs rushmore when you have two or more details of a record, in the first step you find the two or more keys and in the next step you can select records which have the combination of these ids, for which VFP can make use of rushmore.

Chriss

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

Klaus,

I think yuou are making this too complicated.

You said at the start of the thread that you only want the second column. In that case, you could so something like this:

CODE -->

* Assume you want to add second column of text file to 
* field Col2 of table MyDBF.
USE MyDBF IN 0
SELECT MyDBF
loFS = CREATEOBJECT("Scripting.FileSystemObject")
loFile= loFS.OpenTextFile("multitext.txt.txt", 1)
DO WHILE NOT loFile.AtEndOfStream
  lcLine = loFile.ReadLine()
  ALINES(laLines, lcLine, ",")
  INSERT INTO MyDBF (Col2) VALUES (laLines(2))
ENDDO 	
loFile.Close()
USE IN SELECT("MyDBF") 

Since you only want one column, there's probably no need to check the file size. If you are worried that the total size of just the second column could exceed 2 GB (which seems unlikely), you could keep track of the size of the DBF by multiplying the record count by the width of the field.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

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

To store all columns in column DBFs, I'd use ALINES, too, but only when I want to insert all array elements, and thats what Klaus does.

While I did this I also realized not all rows in the text file have data for each column, so the data does not align in same rows. A normal CSV would have the same number of columns for each row. I fixed this with a csv text with 3 columns in every row.

CODE

Cd c:\programming
TEXT TO multitext Noshow
aaa,bbbb,ccccc
dd, eee, fff
ggg, hhh, iii
jjj, kk, llll
mmmm, nnnn, oooo
ENDTEXT

Strtofile(multitext,"multitext.txt")

Clear
Close Data

For i = 1 To 3
	Create Cursor ("column"+Alltrim(Str(i))) (Data c(10))
	If i>1
		Select column1
		Set Relation To Recno() Into ("column"+Alltrim(Str(i))) Additive
	Endif
Endfor

loFS = Createobject("Scripting.FileSystemObject")
loFile = loFS.OpenTextFile("multitext.txt", 1)
Do While Not loFile.AtEndOfStream
	lcRow = loFile.ReadLine()

	For i = 1 To Alines(laColumns, lcRow, ",")
		Insert Into ("column"+Alltrim(Str(i))) Values (laColumns[i])
	Endfor
Enddo
loFile.Close

Scan
	? column1.Data, column2.Data, column3.Data
Endscan 

This also makes use of record number based relations. This way a loop through column1 also skips in the other two cursors to the same row. This sounds as if you can use this as a single table, but it fails at SET FILTER not spanning all column cursors. And the relations are one-way only, so locating in column1 aligns the row position in the other cursors, but when you locate or seek in column2 or 3 you'd need relations starting in these dbfs/cursors to all other cursors. I fear this will not go well with many columns, not just because you need two relations per pair of colums, but this might cause feedback which ends in stalling VFP. You can do without the relations as you can alwas taake the recno of whatever cursor you last seeked or located in and go to the same recno in all other coluumn cursors, that might be the easiest way to handle this.

You can forget SQL, though. You can't make SQL use a relation or join by recordnumbers. So you're bound to work without SQL.




Chriss

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

Here's a short and simple enough introduction to column store databases and intsead of using DBFs you could consider using one of these databases via ODBC.

https://blog.pythian.com/why-column-stores/

Newer SQL Server version also support column stores.

Chriss

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

Klaus, going back to your initial concern, regardless of how you would address (a small pun intended) the issue:

Quote (Klaus)

The problem is that the amount of data that has to be processed today has become more and more enormous over time - and it's not just the 27 million postal addresses.

That's what web services are for. Of course, they must be available for a specific type of information or process. For instance, looking for the UK addresses: https://getaddress.io/ (in another thread, I already mentioned the Here platform with geocoding-related and location search services covering 70 countries worldwide).

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

(OP)
Thank you Mike and Chriss.

Mike:
As I already mentioned, the example file was only intended as a small demo.
With the demo I first wanted to know whether a csv file can be read vertically.
With your suggestion, this was possible for the model - and then I thought again of a huge csv file such as the 27 million csv postcode number file, the number of columns of which is of course very small when compared with the number of lines.

I therefore understand your comment and thank you for improving the program.

Quote (Mike)

I think you are making this too complicated.
You said at the start of the thread that you only want the second column


Chriss
Also your comments on a much larger number of
processing possibilities in a file without redundancies makes sense to me.
Of course, this also makes a lot more work at first - but when everything has been cleared, it must be much faster when you only compare, sum or select using SQL with keys and indexed data.

For me it was about whether you can ignore the 2 GB limits in VFP when you have a very large text file and when it comes to searching or comparing a certain term.

Sometimes it is perhaps also time problems that do not allow
to go too deep into such a file, to split etc.

I appreciate the countless improvements and
References to advantages and disadvantages - I'm really grateful for that
and always will be.

Regards
Klaus

Peace worldwide - it starts here...

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

It seems to me it should be fairly simple to convert (say) 50 single-column dbfs with (say) a million records into (say) 50 (or so) 50-column dbf's, limiting their size to <2GB of course.

That would simplify processing the dbfs, perhaps in a single loop.

All what's needed is to split the csv file using one of the suggestions above.

Steve

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

Klaus,

thanks. And your ideas are not wrong at all, as you can see there even exist databases specialised on columnar storage of data. It just has pros and cons and VFP isn't really made for it. A DBF with just one column is still row storage, you only have either recno as "virtual key" but then need to be aware of problems due to recnos going out of sync if you only delete in one DBF and PACK. And you still don't gain anything special, really, as in a dataabse sepcializsed on columnar data.

The one showcase mentioned in one article I (think I) linked to, was saying when you only need one column of data from a table the separate storage of columns means you just read through this one file instead of needing to skip over all the other columns. But especially this advantage is lost in VFP when you wanted two columns of same rows, when you first only select one of them you already lose the information which record numbers they were. But you might think in other ways like SET FILTER, LOCATE, SEEK. You can take the advantage of faster aggregation, ie when you want SUM(column3) you can get that from column3.dbf only. But when it then comes to filtering what rows by other columns you'd start to reinvent the wheel by reinventing rushmore getting the recnos of relevant data and then fetching those recnos from the column you eventually want to SUM partially (like the total of an order).

You only could have these advantage in full when the VFP SQL engine would allow to have a simple way of reading multiple DBFs as if they were 1 on the outset of same number of records and same deletion state. That could be a nice new feature, but that ship has sailed. I think the relation mechanism reprogrammed a little and a new column table type that extends one "normal" main DBF would work good for that, this new file type would ne included in APPENDS and INSERTS, DELTTES etc. done on the main DBF, just like the main CDX belonging to a DBF is updated with any change, these new column table file would need no deletion flag and a simple header just for the column name and type.

Well, you can have some of that when you don't go for the extreme, you can extend a main record with not just one but a set of columns. For example you could store persons with id, first, last name and have an extension table of employees pointing to the person table for persons that are employed and this table could hold data like hired date, wage, etc. that are only relevant for employees.

All of which is just normalized data, in that case 1:1 related. Or better yet 1:0-1 related. Many database developers actually use that concept but don't share the key in person and employee record, instead employees have their own autoincrement key and, well, just a normal personid for the general person information. This still just wastes 4 bytes per employee as it has an unrelated own primary key and a foreign key, but this is not the worst standard to use.

The advantage of groups of columns is, of course, that this limits the number of joins to be done to get whole records and becomes a quite normal relational DBMS data storage concept.

Chriss

PS: And the one counterargument against column storage is you can't split this up any further. Well when you combine this with horizontal splitting it becomes even less good to handle, in that case you better only split horizontal and any projecct that needed more than 2GB made use of exactly that. You can usually put most often needed data in one DBF and archived/rest data in another or several more or partition by any other way that makes it easy to know which DBFs have the data you need.

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

(OP)
Chriss,
Chriss, thanks for the extensive information about column stores and the pros and cons. and I also have the interesting comment
read about it by John Schulz.

I have to admit that I have never heard of this before, but I now understand the advantages and disadvantages and find the whole topic exciting ..

This week I won't be able to make any attempts - (my golden wedding anniversary is on Thursday) but I'll stick with it for the time being - I'm now particularly interested in the speed of some actions and also at what point when saving in columns for VFP the air becomes tight, and when an unsplitted csv file is really too big for this method of data transfer for VFP.

I'll get back to you here.
thanks again

Regards Klaus

Peace worldwide - it starts here...

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

(OP)

Quote (Steve)

It seems to me it should be fairly simple to convert (say) 50 single-column dbfs with (say) a million records into (say) 50 (or so) 50-column dbf's, limiting their size to <2GB of course.

Steve, maybe I misunderstand something.
Why/how do you you want to convert a dbf with a million records into a lot of smaller dbf's to limit their size to < 2 GB?
When the million-record - column.dbf = >2 GB you can't do that in VFP.
???

Klaus



Peace worldwide - it starts here...

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

Klaus,

I may have the wrong idea here, but what I meant was after slicing the csv horizontally into segments under the 2GB limit, you could then parse the data from them into a dbf having the same number of columns (fields). The max number of records allowed will depend on the dbf's record langth in order to keep the dbf size under 2GB.

I don't know how many dbfs would be necessary. Your program could handle them one at a time, perhaps in a loop.

Steve

p.s. Keep in mind Mike's caution about splitting the last row in a splitted csv file when split size (in bytes) are specified.

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

Steve,

in the preprocessing scenario slicing a large csv horizontally has the goal to be able to use APPEND into a cursor that has the record structure. You're right to be cautious, as a DBF can grow larger than the split csv as char fields are padded and APPEND skips memo fields.

But you simply make smaller splits with just 1000 or 10000 lines each from the CSV to have no problem with too large DBFs. In my case I got 17MB parts, they don't pose a problem.

Klaus idea mainly is to not need the split preprocessing, as Mike has shown a way to read large text/csv files line by line. I fear this line by line processing takes much longer than APPEND and does not pay in a faster import even though no time is spent on splitting the large csv file. I think Klaus idea is not just about the import stage, but I might have got him wrong all the time and he just wanted to eliminate the need to prepare an import dbf or cursor and instead just have char column cursors that can cope with any value up to 255 bytes length.

One advantage of line by line processing could be handling commas within character values better than APPEND.

Chriss

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

(OP)

Quote (Chriss)

Klaus idea mainly is to not need the split preprocessing, as Mike has shown a way to read large text/csv files line by line

Steve:
What Chriss writes is exactly what I want.
It all depends on the circumstances

a) How often does the large csv file have to be updated?
How long does the import take, is the time reasonable?

b) For example, if a csv file is 4.7 GB,
you have to generate 4700/17= approx. 276 dbf's for VFP in order to
not to reach the limit of 2 GB.
But when the csv only has say 20 columns, this results in 20 one-column-dbf's only.
c) Which fields do I have to access for my VFP project?

c) How big is the difference in access speed,
when it's just a column to look for?
In the link (sent by chriss)
https://blog.pythian.com/why-column-stores/
the following case was described:
An example of how significant the differences in performance column stores can provide, I was working with a customer last year with a database containing twenty million records related to purchasing information. Using MySQL MyISAM a typical query they would make against the purchase order table would take 30-40 minutes. Those same queries using MariaDB AX (a column store variant of MySQL) took between 0.1 seconds and 8 seconds.

d) How big / long can the one-column dbf be until limits are also reached with VFP. (depending on the column width and number of rows in the CSV).

e) Does the csv also have fields with quotation marks and commas in a column (problem)

All of this - and certainly more (see arguments in statements by Chriss and Mike) - must be checked.

But there are cases conceivable where this leads to the limits
to be achieved by VFP much later and where no csv-splits are necessary and no memo-fields can
cause problems when converting.

I hope to be able to say more about it soon.

Best regards
Klaus

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

Hi Chris:

The column store looks good and way beyond my knowledge. Good luck with it. As an aside,

One advantage of line by line processing could be handling commas within character values better than APPEND.

Quote (Chris)


Yes, and as I'm sure you know, it can get complicated. I have dealt with embedded commas, quotes, special characters and even some normal characters. For example, an apostrophe in a company name is not allowed in one of my apps (IRS). Another consideration is dollar signs, commas, quotes (and who knows what else) surrounding numbers going into a numeric (or integer) field requiring handling of decimals sometimes. Of course, every case is different.

Steve

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

Okay, nice feedback Steve and Klaus.

I also already have a simple idea as I looked at result data. Wehn ALINES() splits at commas it leaves quotes in character fields and most of the time you then have the actual value delimited by the quotes inside the dbf column field. That's simple to fix by removing them. But when the closing delimiter quote is missing that indicates there waws a comma in the string, you can put that back, increment the index counter for the ALINES array and add elements until you have one ending in a quote. Done.

Also ALINES producing one or two more elements than usual are indicating there is a problem. So you only need special parsing when the number of ALINES array element is too high. In a correct CSV it can never be too low, only too high, so the correct value indicates no comma problem.

Another special case is a linefeed within a quote delimited field that's not a new record, so in that case you would need to continue working on the field until one of the next ALINE array elements has the closing quote at it's end.

I think there will be a few more special cases, you already gave some examples. Those may be handled in postprocessing, when you at first only import everything as text. In the best case you could at least prepare one or more DBFs to have the data types you know are correct. But even then a "2021-12-31" string does not automatically convert to a VFP date field.

How should we name this? AppendX? Or Appendix? Joking aside this could become an AppendCSV solution that works better than IMPORT or APPEND FROM textfile. Even without much effort. No need to parse character by character because ALINES won't make a difference for commas in quotes; It already does good work that can be enhanced.

Chriss

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

Could I just check ...

Have we determined if Klaus's data actually contains embedded commas, double-quotes or other string delimiters? In one of the earlier bits of code posted here, Klaus used GETWORDNUM() with a comma as the third parameter, suggesting that there are no embedded commas or string-delimiters. If that's the case, some of the previous discussion will be irrelevant.

Apologies if this point has already been discussed. I haven't read all the most recent posts in as much detail as I should.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

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

I think Klaus idea is much more general and I test with the pp-complete.csv and that has commas embedded and quotes as delimiters. And as Steve joined the thread this makes sense. To me it all points towards being able to generally use VFP to process large csv files at least to a stage you have either horizontally or vertically split data in several column dbfs (vertical or in partition dbfs (horizontal).

Klaus announced he's working on something. Let's see.

Chriss

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

Thanks for clarifying that, Chris. It makes sense to discuss the general problem of parsing and importing large text files, as opposed to finding an immediate answer to Klaus's original question. It's an interesting topic.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

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

(OP)

Quote (Mike)

It makes sense to discuss the general problem of parsing and importing large text files, as opposed to finding an immediate answer to Klaus's original question. It's an interesting topic.

I agree with you, Mike.

The immediate answer to my question and for me a satisfactory answer
was already given by the fact that you mentioned a very good solution here,
(combined with the warning that it was terribly slow.)
See 4th answer in this thread.

Quote (Mike)

Another approach would be to use an external process, via automation or a COM object, to split the file. One possibility would be to use the File System Object. Something like this .....

Klaus

Peace worldwide - it starts here...

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

The best practice IMO (experience), for keys is to use GUID. GUID are used for replication in SQL Server. If you use integer keys you already are causing one of the problems associated with sequential guids, which is a newish Microsoft SQL Server feature, that "problem" being the next one is guessable.

In the system I'm painfully supporting these days, they copied the keys into the child tables, order# is in the header, order # and line# in the line item and order #, line item and detail # in the detail record. That means a 30byte key in the detail table. Cascade updates are really bad here and insertion of records is really slow. I say one meaningless primary key per table should be the standard. That makes construction of screens really simple.

Mike Yearwood - Former FoxPro MVP

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

(OP)

Quote (Mike Yearwood)

One thing none of you mentioned is the Windows API Memory Mapped Files.
With that it should be possible to define sub 2Gb sections of the huge file and append from each section.

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.

Thank you in advance.
Klaus



Peace worldwide - it starts here...

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

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....

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....

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

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

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

CODE

SELECT Recno() as RecNumber from myTable 

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

Regards

Griff
Keep Smileing

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.

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

(OP)
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

[b]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...

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

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

Peace worldwide - it starts here...

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

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

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

(OP)
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...

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

(OP)
Hi Mike (Yearwood),

Quote (Mike Yearwood: What is wrong with my opinion? I found Olaf's attitude and comments offensive in this thread: https://www.tek-tips.com/viewthread.cfm?qid=178781...)


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...

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

You failed to take note that I found your statement:

"I would also be interested in the opinion of the other members in this VFP forum on this topic."

annoying.

Mike Yearwood - Former FoxPro MVP

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

(OP)
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:

Quote (Klaus)

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...

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

Hi Klaus

Understood. Here's what I think. No one should need to be invited. Everyone should be contributing their input freely and without fear.

These forums are about sharing knowledge. In that regard, I can prove my statements in most cases with timing and showing things like process monitor. I do not think it fair for someone to start a big argument where their position is subjective when I use objective evidence. That interferes with sharing knowledge and learning.

I eliminate potential FoxPro hires with 3 lines of code. No one should be that bad.

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

That crashes with data type mismatch. Some argue that not be allowed. I often adopt huge systems where I cannot control the data structure or the naming conventions. To me, the only thing that matters is what the compiler wants/supports/enforces. Much as I want, I cannot SELECT * EXCEPT FIELD1, FIELD8 FROM TABLE. The compiler does not allow that. That is the reality, despite someone's opinion. Also, the statement premature optimization is the root of all evil, is being taken to mean don't optimize. The author of that phrase said if we can get even a 12% optimization easily, we should take it.

Mike Yearwood - Former FoxPro MVP

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

Quote:

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:

CODE -->

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

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

Quote:


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 SELECT * EXCEPT ..., but it does save a bit of typing.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

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

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...
(search large)

Regards
tom

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

Mike, why does it do that not this?

Regards

Griff
Keep Smileing

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.

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

Griff, here's another hint:

CODE -->

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

? _screen.caption
? caption 

Chriss

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

Another hint:

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

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

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

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


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

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

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

So, according to Chrissssss, you all are not allowed to comment on my discussion with Klaus, because you were not invited. I am to take Chrisssss example as an insult because it is trying to clarify my post. Is that the kind of forum we want? I don't intend to offend anyone. I see an inaccuracy, I demonstrate how it is inaccurate and show how to correct it. If I intend offense, I promise you, you'll have no doubt.

Mike Yearwood - Former FoxPro MVP

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

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 Smileing

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.

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

In other sites, those who disagree with prefacing things with mdot attacked and bullied me regularly. I finally had enough of it earlier this year. This is a known built-in "feature" of Fox. You can attempt to get around it, but I and others get burned by it because of systems without any standards. I now work with a system that uses scatter memvar, scatter name and so when there is a piece of code I cannot tell if a variable is supposed to be a field, a memvar or a property of an object. I have actually had to mdot just to correct code that referenced the table, rather than the property of the same name. table.field vs m.table.field.

I asked vfpa to add a switch to turn this feature off and he delivered. I hope that sticks in the craw of the anti-mdotters.

Mike Yearwood - Former FoxPro MVP

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

Mike Lewis I don't usually do a select * from table. The SQL Server community frowns on select *. I showed F1 Technologies a technique to really speed up a form design. On page 1 is a grid of records matching a query. On page 2 are controls tied to that same alias. So assume 50 columns of data and 100 rows. That's 5000 'cells' of data.

Rather than doing that, I showed them to do one query of 5 columns for the 100 rows. That's 500. Then when navigating to the 2nd page, grab the remaining 45 columns of data for that one row. Total of 545 'cells' of data. Mike Feltman came up to me at a conference and said it worked great, in front of Drew Speedie. To which Drew said, you told them a performance technique you didn't tell me? Ooops. ;)

I wrote an article where I created place-holders for insertion of text 'snippets'. You just gave me an idea.

CODE -->

lcFields = oSnippetFactory.GetSnippet('TableNameFields')
*Remove the fields I don't want from the string.
TEXT TO m.lcSQL TEXTMERGE NOSHOW
SELECT <<m.lcFields>> FROM TableName
ENDTEXT
if mysqlexec(globalconnectionhandle,m.lcSQL)<0
  return .f.
endif 

Mike Yearwood - Former FoxPro MVP

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

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

Quote:

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

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

(OP)
Mike Yearwood,

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

Quote (Mike Yearwood)

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

and I also find this

Quote (Mike Yearwood)

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

Quote:

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...

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

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

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

Quote:

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

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

Yes, I know Mike (of course wink).

There are so many ready to use forums and just like a blog you even get them hosted for free - well, for adverts, usually.
Or you can take an open source one and install it on your own domain.

And things like spam prevention and more are usually built in already and any active project of forum software will also fix problems.
https://www.woltlab.com/ as a less known quality example.

Chriss

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

I've hosted and moderated forums... what an education that is.

This is pretty sane by comparison.

Regards

Griff
Keep Smileing

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.

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

That internal forum I created was in fact based on existing open-source forum software. But just customising it for the client's needs was a major pain. And security was a constant worry. I hope I never have to do anything like that again.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

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

AND NOW YOU'RE ALL BULLYING ME. I attempted to speak with Chris before. He told me "He asked for it" when I corrected him. That to me means his expectation is that unless he asks, no one shall disturb his peace.

Mike Yearwood - Former FoxPro MVP

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

(OP)

Quote (Mike Yearwood)

What you call experts, I call unscientific typical programmers.

In Germany the answer to such sentences is this:
"Hochmut kommt vor dem Fall"
translated:
Arrogance comes before the fall

Klaus


Peace worldwide - it starts here...

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

Better arrogance than ignorance.

Mike Yearwood - Former FoxPro MVP

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

Quote (german12)

In Germany the answer to such sentences is this:
"Hochmut kommt vor dem Fall"
translated:
Arrogance comes before the fall

That's actually from the Bible, Proverbs 16:18

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

The more usual expresesion in English is "Pride comes before the fall". Similar to arrogance, but not quite the same.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

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

(OP)

Quote (Dan Olsson)

That's actually from the Bible, Proverbs 16:18
Thank you Dan - I did not know that.

Quote (Mike Lewis)

The more usual expression in English is "Pride comes before the fall". Similar to arrogance, but not quite the same.
That is also interesting compared with the german expression

btw: My translation "Hochmut" = "arrogance"in english was made by Google.

Pride means "Stolz" in german - one can be proud of his children, his country etc.
In our fairy tales there is also often written about a " proud knight. " (="stolzer Ritter")
So this means in most cases a positive characteristic one can have.

An exception is the term "false pride" - "falscher Stolz" which speaks for itself because of that attribut "false" - e.g. one cannot be proud of always being able to teach others.

That leads to a social distance in the long run.

A man has just been arrested here because he did not comply with the demands of a tank attendant who warned him not to come into the store without a face mask. (protection against Covid)
The man then shot the gas station attendant because of it.
He certainly couldn't be proud of that.

With "arrogance" (masculine) is meant a condescending being, so ALWAYS a negative quality. Haughty people place an unrealistically high value on their own worth, rank, or ability.

There is the word arrogance for this - they are people who pretend to be on the outside - but on the inside they feel inferior. They relate their self-esteem to the fact that they devalue, humiliate or belittle others.

There are many similar words here for arrogance but all of them mean a negative quality.
(german: Überheblichkeit, Anmaßung, Hochmut, Selbstgefälligkeit, Vermessenheit, Blasiertheit, Großspurigkeit - und ein ganz altes Wort:
Dünkel).

Klaus


This knowledge is not my knowledge - if I said so, I would be arrogant.

Peace worldwide - it starts here...

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

Given that the origin of the phrase was the Bible, then the translation between English and German is not relevant. Presumably both versions were independent translations from the original Hebrew (or Aramaic?), or, more likely, from the early Greek version which was in more widespread use.

A quick search of various English versions of the Bible shows the following:

Wycliffe's Bible (14th C Middle English)
Pride goth befor contricioun, & befor falling the spirit shall ben enhauncid.

Coverdale Bible (1535)
After pryde commeth a fall, but a lowly sprete bryngeth greate worshipe.

King James Bible (1611)
Pride goeth before destruction, and a haughty spirit before a fall.

New English Bible (1970)
??? (Can't find a copy on-line; has anyone got a copy to hand?)

Whether pride is different from arrogance, and whether arrogance is better than ignorance, I'll leave it to others to discuss.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

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

This also reminds me of the caution to think the bible is root of most proverbs or sayings. If at all, it's what bible translators made of it.

Chriss

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

FWIW in older Swedish Bibles the word "Högmod" is used, almost exactly the same as in German.
Google has an English translation as "hauteur", probably from French.
But more modern ones has "stolthet" i.e. pride in English.

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

That's interesting, Dan. The English word hauteur (which is a noun) is very similar to haughtiness, which in turn is perhaps similar to arrogance.

I think the French word hauteur (also a noun) usually simply means height or altitude. There are also French words arrogant (adjective) and arrogance (noun).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

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

I checked a fairly recent Jewish translation. (My Hebrew isn't good enough to translate it myself.) The whole verse is translated as:

Pride goes before ruin,
Arrogance, before failure.

Tamar

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close