.DB export to CSV

[Joseph Kim]

Hello, this is Joseph 

 I wanted to ask how to export a .DB table to CSV that has 40,000+ records. 

 The reason being that I have tried exporting the table as a CSV but I only get the headers and Paradox closes once I do the export. 

 When I try to export the .DB to xls I only get about 16,000+. Is this a limitation of Paradox or is there a way around this? I am currently using Paradox 9.

[Steven Green]

the xls limit is because the engine inside is Excel 5 from 1997.. 16384 is the limit.. csv, no limit, you might want to run the repair tool on the table.. could have damage past that 16k point

[TheDBCommunity]

If nothing else, you can use a TextStream to export.  Might take longer to write the script, but once done, you can run it whenever needed, and if you write it correctly, it should have no trouble with larger data sets.

[Kevin Zawicki]

> 

 I wanted to ask how to export a .DB table to CSV that has 40,000+ records. 

<

I have exported MUCH larger tables, based on record count.

>

 The reason being that I have tried exporting the table as a CSV but I only get the headers and Paradox closes once I do the export. 

<

That sounds like table damage maybe. Are you using interactive Paradox to export from the menu, check all the settings.

It is possible you have a comma in the data or a memo field, that might cause an error, but only getting headers is not something i have seen.

>

 When I try to export the .DB to xls I only get about 16,000+. Is this a limitation of Paradox or is there a way around this? I am currently using Paradox 9.

<

Paradox can only natively export to older version of excel which comes with other issues, but often upward compatible. Newer versions of excel will open the export, but flag the file and be readonly, then you have to save as latter version.

If you only need CSV or other text only output look at using a tcursor scan and text stream to make your own.

Many advantages.

In code (a script) you would open a tcursor on the table or query result, scan the tcusror and write out to the file row by row.

You can control each column, alter data, etc.

I have many of these both export and import, many variations.

[Joseph Kim]

A lot the data in some fields contains commas and " marks. 

In addition, I do believe that text stream is a way to extract the information using code. What should I use?

The .DB file is downloaded on my local computer. Should that matter?

Also, is there a way for me to delete columns from the .DB file? I would like to get rid of the memo fields because they are not really necessary. 

[Steven Green]

Joseph.. the exports ignore the memo fields automatically, you have no choice

[Michael Kennedy]

Joseph,

If you write your own code to create the CSV file, then you have full control on which fields to include.

The embedded commas and embedded "-marks will cause no problems - provided you follow the 'standard' approach within your code, as follows:
  - Assume the agreed field-separator is Comma, and the agreed alphanumeric field-delimiter is double-quote.
  - If any Alphanumeric fields contain embedded commas, just leave them unchanged in the exported fields.
  - If any Alphanumeric fields contain embedded double-quotes, then, for each of them, put TWO consecutive double-quotes in the exported field.

The CSV importer should handle those pairs of double-quotes correctly - most do.

  - Mike

[Jure Zorko]

Joseph, if you know just a little bit od programming, then use this script (and modify to your needs):

In paradox: 

File - New Script

method run(var eventInfo Event)
var nameDB, nameCSV String
 ARfields Array[] String
    typ, name string
    i, j smallint
    TS TextStream
    TC TCursor
    line string
    separator string
    tmp string
    DA DynArray[] AnyType
    ch string
endVar
nameDB = "c:\\temp\\table.db"    ; input table
   nameCSV= "c:\\temp\\file.csv" ; output file
separator = ","   ; separator between columns in CSV.   ; or ,

   ; which fields to export to CSV and how to format them
   ARfields.AddLast("Order ID,String")
   ARfields.AddLast("Qty,Number")
   ARfields.AddLast("Item,String")
   ARfields.AddLast("Price,Number")
   ARfields.AddLast("Delivery,Date")

   if not TC.Open(nameDB) then errorshow() return endIf
   if not TS.Create(nameCSV) then errorshow() return endIf

   ; create first line in CSV
   line = ""
   for i from 1 to ARfields.Size()
tmp = ARfields[i]
      if not tmp.match("..,..",name, typ) then
      MsgStop("Err","Incorrectly define field\n"+tmp)
         return
      endIf
line = line + name
      if i < ARfields.Size() then line = line + separator endIf ; there is no separator after last field
   endFor
   TS.WriteLine(line)

   ; now data lines
   scan TC :
line = ""
      TC.CopyToArray(DA)
      for i from 1 to ARfields.Size()
         tmp = ARfields[i]
         tmp.match("..,..",name, typ)
         if not DA.Contains(name) then MsgStop("Err","There is no field "+name+" in table") return endIf
switch
case lower(typ) = "string"   : ; oh those double quotes
  tmp = DA[name]
                                            line = line+"\""   ; starting quote
                                            for j from 1 to tmp.size()
                                            ch = tmp.SubStr(j,1)
                                                line = line+ch
                                                if ch = "\"" then line = line+"\"" endIf  ; double inline quote
                                            endFor
                                            line = line+"\""   ; ending quote
case lower(typ) = "number"   : line = line + format("W.2,EZ,EDW",DA[name])    ; european style numbers
case lower(typ) = "logical" : line = line + string(DA[name])
case lower(typ) = "date"     : line = line + format("DD2M2Y3O(%D.%M.%Y)",DA[name]) ; european style date
            otherwise     : MsgStop("err","Unknown type of field "+name+":"+typ)
         endSwitch
         if i < ARfields.Size() then line = line + separator endIf ; there is no separator after last field
      endFor
      TS.WriteLine(line)
   endScan
   TC.Close()
   TS.Close()

endMethod

Just change name of files and fields.

Jure