Hello

I have an issue wherein tables are exported to a CSV, then each is imported into a table with an identical name and structure, at a remote location. The table has a ClientID field: "ClientID" INTEGER GENERATED ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL.

When the CSV is created, the data is not sorted in the order in which it is displayed in the table. The >50,000 record table starts at ClaimID 50014 - ClaimID #1 is at line 50015. Also, the first ~50k records are written in descending order.

Thats Ok, but when the CSV is imported, the guy from 50014 is imported as ClaimID 1. The entire CSV is loaded in the same order in which it is written to the CSV, and therefore unusable.

I am using Unicode 2.14 build 3, in XE3. The code for the export is:
MAKE_CSV = 'EXPORT TABLE "%s" TO "%s.csv" IN STORE "CSVStore" '+
           ' DELIMITER CHAR #8 QUOTE CHAR ''"'' DATE FORMAT ''yyyy-mm-dd'' '+
           ' TIME FORMAT ''hh:mm:ss n'' AM LITERAL ''AM'' PM LITERAL ''PM'' '+
           ' DECIMAL CHAR ''.'' BOOLEAN TRUE LITERAL ''True'' FALSE LITERAL ''False'' '+
           ' INCLUDE HEADERS MAX ROWS -1';
...and the import is...
IMP_SQL = 'IMPORT TABLE "%s" FROM "%s" IN STORE "CSVStore"'+
          ' DELIMITER CHAR #8 QUOTE CHAR ''"'' DATE FORMAT ''yyyy-mm-dd'' '+
          ' TIME FORMAT ''hh:mm:ss n'' AM LITERAL ''AM'' PM LITERAL ''PM'' '+
          ' DECIMAL CHAR ''.'' BOOLEAN TRUE LITERAL ''True'' FALSE LITERAL ''False'' '+
          ' USE HEADERS MAX ROWS -1';

How should I approach this? I have dozens of tables that are exported\imported in this manner, so I have to find a strategy that works. If I have to export from a ClientDataSet I will.

Regards and TIA

Peter

Peter


Two suggestions:

1. Alter the table to "ClientID" INTEGER GENERATED BY DEFAULT AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL,

2. Look at using Tim's built in features for publishing the data.

Roy Lambert

Roy

Spot on! The 'BY DEFAULT' change ensured that the rows were not re-ordered. I guess that I can run a script after the import has run, and alter all the 'BY DEFAULT' columns back to 'ALWAYS'. I have an app that runs after the Inno installer has put the files on a client's system, and it does the importing from the CSVs, so it can alter the tables.

BTW the EDBManager export of the tables produced exactly the same CSV as my SQL did, which isn't surprising since I copied the SQL from the source

It is a pity that we can't have a switch to ensure that the CSV isn't written out in the order of the primary key. I think Tim has alluded to that a few times.

Thanks for your help Roy

Peter

Peter,

<< I have an issue wherein tables are exported to a CSV, then each is
imported into a table with an identical name and structure, at a remote
location. The table has a ClientID field: "ClientID" INTEGER GENERATED
ALWAYS AS IDENTITY (START WITH 0, INCREMENT BY 1) NOT NULL.

When the CSV is created, the data is not sorted in the order in which it is
displayed in the table. The >50,000 record table starts at ClaimID 50014 -
ClaimID #1 is at line 50015. Also, the first ~50k records are written in
descending order. >>

EDB currently does a "raw" export of the table rows, but I'm doing a new
build here shortly and I can easily change this to have it perform the
export according to the primary key.

Tim Young
Elevate Software
www.elevatesoft.com