Login ProductsSalesSupportDownloadsAbout |
Home » Technical Support » ElevateDB Technical Support » Support Forums » ElevateDB General » View Thread |
Messages 1 to 4 of 4 total |
Order of records in exported CSV |
Thu, Oct 31 2013 3:44 AM | Permanent Link |
Peter | 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 |
Thu, Oct 31 2013 3:56 AM | Permanent Link |
Roy Lambert NLH Associates Team Elevate | 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 |
Thu, Oct 31 2013 6:36 AM | Permanent Link |
Peter | 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 |
Tue, Nov 5 2013 2:31 PM | Permanent Link |
Tim Young [Elevate Software] Elevate Software, Inc. timyoung@elevatesoft.com | 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 |
This web page was last updated on Sunday, May 19, 2024 at 08:46 AM | Privacy PolicySite Map © 2024 Elevate Software, Inc. All Rights Reserved Questions or comments ? E-mail us at info@elevatesoft.com |