Jump to content
Developer Wiki and Function Reference Links ×

Importing Tab-Delimted Text Files with blank spaces


Recommended Posts

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!

Link to comment

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;

 

 

 

Link to comment

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

Link to comment
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

Link to comment

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.

Link to comment
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 by SamIWas
Link to comment

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.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...