I have an odd problem with the import statement in 10.2. I need to import some fields from af CSV-file with semicolon as a delimiter. I cannot read it as a single line because some of the fields might contain newlines. At least a comment from the original developer says so.
Now I need a few fields to be either blank/empty, filled with text or with a - (minus character). The idea is that blank means ignore and minus means wipe the corresponding field in the database.
But the minus is read as an empty string (or a blank?). If i write -- (two minus characters) they are read correctly. The input fields are all character type.
Part of the code is as follows.
indfilnvn = "c:\data\buffer2\vare-god-b.csv".
input from value (indfilnvn) NO-CONVERT.
...
IMPORT DELIMITER ";" cn1 cn2 cn3 cn4 cn5 cn6 cn7 cn8 cn9 cn10 cn11 cn12 cn13 cn14 cn15 cn16
cn17 cn18 cn19 cn20 cn21 cn22 cn23 cn24 cn25 cn26 cn27 cn28 cn29 cn30
cn31 cn32 cn33 cn34 cn35 cn36 cn37 cn38 cn39 cn40 cn41 cn42 cn43 cn44
cn45 cn46 cn47 cn48 cn49 cn50 cn51 cn52 cn53 cn54 cn55 cn56 cn57 cn58.
...
message "cn25:" + cn25
view-as alert-box info buttons ok.
cn25 shows as an empty string.
I have tried various conversions in the input, that does not help. There are no "funny" characters in the file.
DVPGOD;Isop;Isopblade;stk;21;21;DVP;;;;;;;;;;;;;-;;N;AU;J;-;;;;;;;;;;;;;;N;;;;;;;;;;;;;;;;;;;X
You can pass the entire file through regex and look for this exact occurrence, then replace. Columns are skipped when there is a single unquoted hyphen.
Pass the file through sed or some other regex utility and replace ;-; with a special tag such as <!hyphen!> and then use import statement on the file with those changes.
Etienne.
A minus sign is a hyphen.
knowledgebase.progress.com/.../IMPORT-statement-ignores-hyphen
From the help file:
"If an input data line contains an unquoted hyphen in place of a data value, then the corresponding field is skipped, as it is in UPDATE. If you specify a hyphen (-) as the delimiter character, all hyphens are treated as delimiters. If you use the UNFORMATTED option, the hyphen is treated the same as any other character."
How big is the file you're trying to load. If it's just a few lines then use COPY-LOB to copy it into a LONGCHAR variable and then loop through it like you would any character variable. I do that quite a bit in 10.2. A lot easier than import in a lot of cases.
The length may vary. I do not know any maximum number of lines. Probably several thousand.
I will have to dig into the comment about newlines in some fields. How is that possible?
You can pass the entire file through regex and look for this exact occurrence, then replace. Columns are skipped when there is a single unquoted hyphen.
Pass the file through sed or some other regex utility and replace ;-; with a special tag such as <!hyphen!> and then use import statement on the file with those changes.
Etienne.
A minus sign is a hyphen.
knowledgebase.progress.com/.../IMPORT-statement-ignores-hyphen
From the help file:
"If an input data line contains an unquoted hyphen in place of a data value, then the corresponding field is skipped, as it is in UPDATE. If you specify a hyphen (-) as the delimiter character, all hyphens are treated as delimiters. If you use the UNFORMATTED option, the hyphen is treated the same as any other character."