SamIWas Posted July 18, 2017 Share Posted July 18, 2017 When importing a tab-delimited .txt data file from Filemaker, some fields may be empty. I've noticed that when a field is left black, Vectorworks ignores it and imports the next field in its place. So I have this test data: R1 1ABC 1DEF 1GHI R2 2DEF 2GHI R3 3ABC 3GHI R4 3ABC 4DEF R5 5DEF 5GHI And this simple script to test it: PROCEDURE ImportInfo; VAR filename,recnum,field1, field2, field3: STRING; BEGIN; Getfile(filename); Open(filename); WHILE NOT EOF(filename) DO BEGIN Read(recnum,field1, field2, field3); AlrtDialog(concat('Record: ',recnum,'. Field1: ',field1,'. Field2: ',field2,'. Field3: ',field3,'.')); END; {while} END; Run(ImportInfo); So, when I run that script, the first dialog says: Record: R1. Field1: 1ABC. Field 2: 1DEF. Field3: 1GHI as expected. But, the second run of it says: Record: R2. Field1: 2DEF. Field 2: 2GHI. Field3: R3 So, it has skipped the blank area in the first field of the second record. I have been dealing with this for ten years by putting a dash in empty fields. But, I really want to move past that, as that is error-prone. Since it seems to ignore the field, I don't know how to check for the field being empty. I can't move to comma delimited, and would really rather not move to colon-delimited. You guys are geniuses, so I'll throw this one at you! Quote Link to comment
Sam Jones Posted July 18, 2017 Share Posted July 18, 2017 I ended up having to use StdReadLn(); and then parsing the returned string. It would be nice if someone can come up with a cleaner way. Below has been cut out of a larger command. Hopefully we'll see a nicer implementation. Sam PROCEDURE GetFieldValues BEGIN Open(ImportFile); NumCols := 0; Allocate StrHolder[1..1000]; Allocate StrEraser[1..1000]; StrEraser := ''; {==============================================================} PROCEDURE ParseStr(delimitedStr : DYNARRAY[] of CHAR; VAR resultStr : String); {Returns the first tab delimited item and strips it off the delimited string} VAR tabPosition : Integer; itemName : String; dyStrHolder : DYNARRAY[] of CHAR; dyStrHolderResult : DYNARRAY[] of CHAR; dyStrResult : DYNARRAY[] of CHAR; dysStrSize : INTEGER; BEGIN Allocate dyStrHolder[1..1000]; Allocate dyStrHolderResult[1..1000]; dyStrHolderResult := StrEraser; dyStrHolder := delimitedStr; itemName := ''; tabPosition := POS(chr(9),dyStrHolder); IF (tabPosition > 0) THEN dyStrHolderResult := COPY(dyStrHolder,tabPosition+1,Len(delimitedStr)) ELSE dyStrHolderResult := delimitedStr; IF tabPosition > 0 THEN BEGIN IF tabPosition = 1 THEN itemName := '' ELSE itemName := COPY(dyStrHolder,1,tabPosition - 1); Delete(dyStrHolder, 1, tabPosition); StrHolder := dyStrHolderResult; END ELSE BEGIN itemName := delimitedStr; delimitedStr := ''; StrHolder := ''; END; resultStr := itemName; END; {PROCEDURE ParseStr} {==============================================================} BEGIN StdReadLn(StrHolder); {Read first line for field names} WHILE (LEN(StrHolder) > 0) DO BEGIN NumCols := NumCols + 1; {Count the Number of fields} ParseStr(StrHolder,FieldVal); ColNameArray[NumCols] := FieldVal; END; {WHILE (LEN(StrHolder) > 0)} WHILE (NOT EOF(ImportFile)) DO BEGIN StdReadLn(StrHolder); FOR Index := 1 TO NumCols DO ParseStr(StrHolder,FieldValArray[Index]); {other stuff was done here} END; {WHILE (NOT EOF) } Close(ImportFile); END; Quote Link to comment
JBenghiat Posted July 18, 2017 Share Posted July 18, 2017 This has been a problem for a while. As with Sam's script, you can manually parse each line and deal with repeating tabs properly. File handling is one of the big motivators on the adoption of Python, and VW highly recommends using py for file handling and data reading. There are many file parsing techniques built in to Python and they are fairly well documented. You can also mix py and native vs if you're not ready to make a total switch for this script. Calling one from the other is fairly easy, though passing data is a little trickier. -Josh Quote Link to comment
SamIWas Posted July 18, 2017 Author Share Posted July 18, 2017 Okay. Thanks all. I was hoping for something simple I was missing! Seems like that would be a bug, and one that could be fixed fairly easily. Quote Link to comment
Miguel Barrera Posted July 18, 2017 Share Posted July 18, 2017 I often import text files and found that comma delimited (.csv) formatted files are better to account for the empty fields. Most databases and spreadsheets have this option also for export/import so I am guessing that Filemaker would not be the exception. Quote Link to comment
JBenghiat Posted July 18, 2017 Share Posted July 18, 2017 30 minutes ago, SamIWas said: Seems like that would be a bug, and one that could be fixed fairly easily. It's been that way for years (10+?) -- it's actually a shortcoming of the PASCAL language on which vs is based. The "fix" is actually python support. -Josh Quote Link to comment
Sam Jones Posted July 19, 2017 Share Posted July 19, 2017 I used to think it was a bug that I had to work around, but it turns out that it is left over from the standard definition of Pascal. Once the data handling routine is written, it is pretty easy to incorporate it in all the file handling that needs it, but it is not a great way to pass stuff back and forth; good for collecting data. As I understand it Python has a lot of very cool file handling and processing techniques, but I'm an old dog and used to Pascal, in fact enjoy it. Josh has been very politely beating me over the head to make Python call for a while now, I just don't have the energy. The problem with comma delimited files is that a lot of data has commas in it; european numbers being one of the first to come to mind. Quote Link to comment
SamIWas Posted July 22, 2017 Author Share Posted July 22, 2017 (edited) On 7/19/2017 at 4:53 PM, Sam Jones said: I used to think it was a bug that I had to work around, but it turns out that it is left over from the standard definition of Pascal. Once the data handling routine is written, it is pretty easy to incorporate it in all the file handling that needs it, but it is not a great way to pass stuff back and forth; good for collecting data. As I understand it Python has a lot of very cool file handling and processing techniques, but I'm an old dog and used to Pascal, in fact enjoy it. Josh has been very politely beating me over the head to make Python call for a while now, I just don't have the energy. The problem with comma delimited files is that a lot of data has commas in it; european numbers being one of the first to come to mind. Yeah, that's a bummer. When I have some time, I guess I'll have to learn python scripting. I also can't use comma-delimited. Almost all of my data has commas in it. " Edison 12/3, 50' ". And I'd rather deal with working around it than changing all of the data to something uglier. Edited July 22, 2017 by SamIWas Quote Link to comment
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.