Question

Leading zeros - Forms data lookup from CSVs

Forms

Updated May 13, 2019

asked on May 5, 2019 • Show version history

Hi there,

I have a CSV file, created in Excel, in which one column has phone numbers that begin with a leading zero. The zero is retained when looking at the CSV file in notepad.

However, in my form when I get a data lookup to auto-fill a 'Single Line' field with the phone number, it drops the leading zero on population. I would expect that to happen for a Number field (which is why I'm not using it) but not a Single Line field.

All other lookups are performing as expected.

Is there anything I need to be doing to get it to retain the leading zero?

nb: I can't use SQL or XLSX due to licensing issues on the server.

Thanks.

0 0

Answer

SELECTED ANSWER

replied on May 13, 2019

Like I said, it's likely the driver is guessing for the data type. It might be worth trying the steps in this stackoverflow thread.

1 0

Replies

replied on May 6, 2019

If you're using a data source with the Excel driver, then you are still using Excel rules for parsing data. Excel decides the data type based on the information in the column, then formats it to that type or returns null values for anything that does not match. It looks like in this case, the column type is number, so the missing leading zeros are expected behavior.

1 0

replied on May 12, 2019 • Show version history

Hi Miruna,

I've tried now numerous options, including ensuring all XLSX fields are 'text' format before saving as CSV; copy/pasting CSV contents directly into a notepad file; hand-typing a new CSV in notepad; using quote marks around the mobile ph numbers; etc.

The data definitely preserves the leading '0' - i.e. viewed in notepad it's there - however it never shows in the 'Single Line' field when populated via data lookup. So it's still a mystery!

Thanks for the suggestion though.

Oh, and I should say - I'm using the ODBC driver with 'CSV/Text' (Microsoft Access Text Driver).

0 0

SELECTED ANSWER

replied on May 13, 2019

Like I said, it's likely the driver is guessing for the data type. It might be worth trying the steps in this stackoverflow thread.

1 0

replied on May 13, 2019 • Show version history

Hi Miruna, thanks again.

As you mentioned, it was guessing the column type was 'Float', and kept resetting back to that even when we specified it as 'Char' (hadn't noticed previously that it was resetting itself). Managed to force it to retain the column data type as 'Char' and now it's working.

Didn't need the 'schema.ini' file as suggested on stackoverflow though.

0 0

You are not allowed to follow up in this post.

Question

Question

Leading zeros - Forms data lookup from CSVs

Answer

Replies

Sign in to reply to this post.