Importing CSV data into Paradox 4.5 DOS table

[M.Emmanuel]

I am trying to figure out how to import a CSV file into a legacy Paradox 4.5 for DOS table.

I see there is an Import option, but it replaces the entire table. I want to import the data as new rows into an existing table.

The CSV can be made as "friendly" as needed:

• No text fields enclosed in quotes

• Either ; or , as separator

• No separator character present within any fields

Online documentation for Paradox is scarce. I have a PAL reference for version 4.0 and the book Paradox 4.5 Made Easy. Otherwise, I will attempt to write a program myself.

An example showing how to:

• open a text file,

• read it line by line,

• parse three fields separated by ;,

• and assign them to specific table columns

would be greatly appreciated.

Even basic hints on which PAL commands to explore would be very helpful.

Thank you!

[Steven Green]

walk down the menu, just like you do interactively.. {Tools} {ExportImport} blah blah {AppendDelimited} to add it to an existing table

[Jostein Bakke]

Here's a code that (I used some years ago, and) have simplified, I have taken out everything except the txt-import-thing (and translated my Norwegian comments, or tried to…):
Jostein Bakke

method pushButton(var eventInfo Event)
var
vFbi FileBrowserInfo
tcssBasis, tcIMP, tcHSIF, tcBASIS, tcANM13  tCursor
vTxtFil, vTxtLinje  string
vTStr  textStream
vLtFT, vLTteller2, vLTteller1  longInt
vTxtArr Array[] String
vSmtSak, vSmtEN, vSmtFN, vSmtFDT, vSmtPNR, vSmtORG, vSmtROLLE, vSmtRegDSak, vSmtRegDAnm, vSmtALDER, vSmtSTB, vSmtFland, vSmt040dato, vSmt040avgj, vSmtOpklDato, vSmtOpklAvgj  smallInt
vJNStopp  logical
endVar

;Gets the txt-file:
vFbi.Title = "CHOOSE IMPORT-FILE"
vFbi.CustomFilter = "CSV-file|*.csv|TXT-fil|*.TXT|All files|*.*|"
fileBrowserEx(vTxtFil, vFbi)
if vTxtFil=blank()
then
return
endIf

setMouseShape(MouseWait) sleep()
;connects vTStr to txt-file
if not vTStr.open(vTxtFil, "r")
then errorShow("Could not open the file:")
setMouseShape(MouseArrow) return
endIf

if not tcANM13.open(":IMPORT:Anmeldte13.db")
then setMouseShape(MouseArrow) errorShow() return
   endIf

;Start READING:
tcANM13.edit()
tcANM13.lock("Full")
tcANM13.empty()

setMouseShape(MouseWait) sleep()

while not vTStr.eof()
vTStr.readLine(vTxtLinje)
vTxtLinje.breakApart(vTxtArr, ";")

;Last line:
if vTxtArr[2] = blank() and vTxtArr[3] = blank()
   then loop
   endIf


tcANM13.insertRecord()

tcANM13."Case no" = longInt(vTxtArr[1])
tcANM13."Rolle" = vTxtArr[2]
tcANM13."Last name" = vTxtArr[3]
tcANM13."Name" = vTxtArr[4]

endWhile

tcANM13.unLock("Full")
tcANM13.endEdit()
tcANM13.close()

tcIMP.close()

endMethod

[Steven Green]

that's pdoxwin, not pdoxdos

Steve at Oasis Trading Post

--
You received this message because you are subscribed to the Google Groups "TheDBCommunity" group.
To unsubscribe from this group and stop receiving emails from it, send an email to thedbcommunit...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/thedbcommunity/d75bb600-5512-4dcf-b627-4561b75a778bn%40googlegroups.com.

[M.Emmanuel]

Thanks for sharing, the Append option was obvious but I missed it.
I realized the code shared was for Paradox Windows, not DOS. But I appreciate the effort. Thanks for sharing.

While the import is the way to go, I managed to read the file with READFILE and then a combination of search and subtring. But I guess there is no direct method to actually ADD a record in PAL DOS so it would be a bit clumpsy.

I also noticed that I can not select the delimiter character, while in PARADOX 3.5 that seemed to be an option.

[Steven Green]

you can insert records to a table in a variety of ways, depending on context

the delimiter chars are part of SYSINFO in 4.5 

Steve at Oasis Trading Post

To view this discussion visit https://groups.google.com/d/msgid/thedbcommunity/16d10da9-b61d-4766-814a-7f5a23d1dfedn%40googlegroups.com.

[M.Emmanuel]

Thanks for the info. I only have the PAL reference manual for 4.0 and didn’t see any delimiter option under SYSINFO there — if someone has a 4.5 PAL reference guide out there and can share the specific keyword that would be welcomed.
I also noticed there is a rows and cols parameter set in SYSINFO, wondering if that might enable do forms larger than 80x25.

Regarding insert records, I’m looking for a simple way to programmatically append a record to a specific table.

I understand the form-based approach, but I was wondering if there is a way in PAL to assign values to fields and directly add a record without mimicking user input. 

Replicating the actions required on the UI to append a record in table view seem convoluted.

I was expecting that there would be a better/idiomatic way to do this operation in PAL.

[Steven Green]

just looked, you are correct about SYSINFO, the delimiters can't be changed that way in pdoxdos, only in pdoxwin

the columns and rows thing will make the workspace bigger, but not the forms, if I remember correctly

inserting, there are many ways to do this.. you can populate an array or dynarray and add it to a table, you can populate records in a temp table and add to another table, you can use an insert query, you can insert a record in a table directly

[Steven Green]

I was wrong, SYSINFO does work in PdoxDOS, just found what I was looking for.. bear in mind, there are a few things that are not in the manual, they just don't always come to mind in that context

sysinfo config to saveconfig ; save existing

dynarray newconfig[]

newconfig["SEPARATOR"] = chr(9) ; tab

sysinfo config from newconfig 

; just changed it to tab.. do the same with comma to change it back

[M.Emmanuel]

Thanks for sharing I will give it a try.

Regarding adding records, while it is a basic question and I understand the rationale of the different approaches, it would be good to have an actual example on how to do that. Any recommendation on literature to learn how to do that is welcomed since the online information on Paradox DOS is for obviusly very limited. So far I have managed to have a working application but I miss a bit more automation.

Another case I found is that I have a table for income, another table for expenses and another table for deductions. If you need to sum all incomes and subtract all expenses and deductions (so summary of a field in table A - summary of a field in table B - summary of a field in table C). It is convenient sometimes to have an unified report with calculations like that one, and I am still unclesr on how to do that. Maybe that is not the intended usage of Paradox.