Converting text column to number after import

Tap Forms – Organizer Database App for Mac, iPhone, and iPad Forums Using Tap Forms Converting text column to number after import

Tagged: 

Viewing 1 reply thread
  • Author
    Posts
  • December 6, 2018 at 9:40 AM #32444

    Tom Loredo
    Participant

    Hi TapFormers-

    I’m a new user, moving to Tap Forms from a much less capable app called Records. One thing I did like about Records is that it had some extra data types. In particular, one of my databases uses its percentage and currency types. In such types, you enter (say) 10, and it displays as 10%, for example.

    I’ve imported that database to Tap Forms. When Records exports percent or currency values to CSV, it unfortunately exports them with the associated symbol, i.e., as “10%” or “$5.00”. If I try importing these as numbers, Tap Forms just silently ignores the data and leaves the cells blank (I think the user should get a warning whenever input data is ignored, but that’s another issue!). So I told Tap Forms to import as text.

    Now I have these “10%” and “$10.00” text columns. Is there a way to convert that data to numbers within Tap Forms—either converting the existing columns, or creating new columns that have the converted data? As it stands, Tap Forms doesn’t realize the values are numbers, and I have to manually enter “%” and “$” with new data.

    Thanks,
    Tom

    December 6, 2018 at 9:52 PM #32462

    Brendan
    Keymaster

    Hi Tom,

    Thanks for choosing Tap Forms!

    What you could do is pre-process your CSV file and remove the % and $ symbols from your file before you import it into Tap Forms.

    Or you can use Tap Forms’ Advanced Find & Replace (under the Records menu) so you can search a specific field for the % or $ characters and replace them with nothing. That should remove them.

    Then you can switch the field types to Number. You can then enable the Currency and Percent formats on them.

    However, when Tap Forms exports a value with a percentage, the value is actually a decimal fraction. For example 10% is stored as 0.10. If you have Excel or Numbers, you could pre-process the file and divide the values by 100 to convert them to a decimal value between 0 and 1. 0 is 0% and 1 is 100%. Then that would allow you to import the files so that Tap Forms can interpret the data properly.

    Thanks!

    Brendan

Viewing 1 reply thread

You must be logged in to reply to this topic.