Stuck - Database update via .csv file

[Jeff Barnes]

I need some help figuring out some data manipulation.

I have a database with 90000 entries.
I have a .csv file with 22000 entries.

In the .csv file there are 3 entries per line (cOld, cNew, cName)

I need to go through the .csv file and match cOld to my database filed MyDB->ID and cName in MyDB->Name
If cOld == MyDB->ID and cName == MyDB->Name I need to update MyDB->ID with cNew.

I tried doing a DO WHILE ! EOF() / ENDDO and in that I would search each line of the .csv file for a match.
This takes at least 12 hours to run. There must be an easier/faster way.

Any ideas?

[karinha]

Are you unable to transform .CSV into .TXT? Wouldn't it be easier to import the .TXT into the .DBF?

Regards, saludos.

[Jeff Barnes]

The .csv file is a text file.
I can't just import the data, I need to update the data in the database based on the info in the .csv file

[Otto]

Jeff, can't you use APPEND FROM?
Best regards,
Otto

[Jeff Barnes]

Hi Otto

I can't just do an append.
I am updating a database with new ID numbers.
In the csv file, there is the old ID, new ID and name (22000 of them).
The csv file will only show 1 entry for each "old ID" however, there can be multiple entries in my database for the same "old ID".
I need to update ALL records that match "old ID and name" and change the old ID to the new ID.

I hope that made sense

[Otto]

Jeff, you could first create from the csv a dbf.
Best regards,
Otto

[Marc Venken]

Have a look here : function readCSV

viewtopic.php?f=3&t=45199&sid=e8fd5a67b9626e28fc1ce4a7da0eb769#p276253

For each token you can do the check with the DBF (indexed) and update if needed.

Seek the ID in the dbf. If found, start a second loop as long as the ID = OLDID and change the data. Exit the second loop and process the CSV for next values

Can it help ?

[Jeff Barnes]

Hi Marc,

Thank you.
This is what I needed: "Seek the ID in the dbf. If found, start a second loop as long as the ID = OLDID and change the data. Exit the second loop and process the CSV for next values"

It was the second loop that I was missing. This should run much much faster

[Antonio Linares]

Dear Jeff,

From chatgpt:

1. Load the CSV into a Memory Array
Read the .csv file into a memory array or hash table. This allows quick lookups by cOld and cName.

2. Iterate Over the Database Efficiently
Use a single loop through your database and check for matches in the array or hash table instead of iterating over the .csv file repeatedly.

3. Batch Updates
When a match is found, update MyDB->ID with cNew directly.

Code: Select all  Expand view  RUN

FUNCTION UpdateDatabase()
   LOCAL aCSV := {}, cLine, nFileHandle, hTable := {}, cCompositeKey

   // Open the database
   USE MyDB EXCLUSIVE NEW

   // Load CSV into memory
   nFileHandle := FOpen("update_file.csv")
   IF nFileHandle == -1
      ? "Failed to open update_file.csv"
      RETURN
   ENDIF

   WHILE ! FEOF(nFileHandle)
      cLine := AllTrim(FReadLine(nFileHandle))
      IF Empty(cLine)
         LOOP
      ENDIF
      // Parse CSV line
      LOCAL aFields := HB_ATokens(cLine, ",")
      AAdd(aCSV, { aFields[1], aFields[2], aFields[3] }) // cOld, cNew, cName

      // Create a hash table for quick lookup
      cCompositeKey := aFields[1] + "_" + aFields[3]
      hTable[cCompositeKey] := aFields[2] // Map composite key to cNew
   ENDDO

   FClose(nFileHandle)

   // Traverse the database
   GO TOP
   DO WHILE ! EOF()
      cCompositeKey := MyDB->ID + "_" + MyDB->Name
      IF HB_HHasKey(hTable, cCompositeKey)
         MyDB->ID := hTable[cCompositeKey]
         // Optional: Mark the record as updated if needed
         // REPLACE MyDB->Updated WITH .T.
      ENDIF
      SKIP
   ENDDO

   // Clean up and close
   CLOSE DATABASES
   RETURN
 

Key Improvements in This Approach
1. Hash Table Lookup
Instead of searching the .csv line by line, the hash table enables O(1) lookups for each database record, massively improving performance.

2. Single Pass Through Database
Only one loop is needed for the database, avoiding the costly nested loop.

3. Minimal Disk I/O
The .csv is read into memory entirely at the start, reducing repetitive file reads.