how to prevent date formatting in csv files

Topic

New Reply

When asking customers to enter their address details online, the data is downloaded to our server as a csv file.  Some customers enter their address details in a way that turns the output in the csv file to a date ,  e.g   2/6 which should be reflecting unit 2, 6 (High St) is turned into Feb 6.  Any suggestions on how to deal with this via coding at our end as opposed to hoping customers enter the details correctly?

Reply | Quote

Replies

My first thought is that it would be most efficient to try to enforce your data-entry requirements on the front end, i.e., the form the customers fill out. Is that a possibility?

Reply | Quote

Excel is so bad at dealing with CSV files and has been for years, it’s infuriating. LibreOffice knows how to deal with them!

I’ve given this some thought and I have three thoughts:

1) Add an optional Unit input box to the form. This is the best solution that I can think of as it maintains exactly what the customer wants and keeps the pieces of data separate. If you want to make it more readable for the company then, when processing the form data, concatenate 2 into “Unit 2” IF the input box contains data.

2) If 1 is not possible, a simple hack would be to force 2/6 into a string by concatenating 2/6 into ” 2/6″ when processing the form data. Note the space before 2/6. I don’t have access to Excel right now, but this should keep it as a string I think?

3) You may want to be more thorough than 2 by using a regular expression search and replace to convert 2/6 into “Unit 2, 6”. This would be less reliable, however, since there might be variations on 2/6 entered.

Overall, I would recommend 1 if at all possible.

Hope this helps.

Reply | Quote

Could one of these three techniques be useful?

Stop Excel from Converting Text to Number or Date format when Opening a CSV file

Perhaps the third option would be appropriate?

Option 3: Formatting the CSV file so that Excel doesn’t convert the data format

Reply | Quote

thank you – I will have to look closely at some of the suggestions but just to clarify,

the processing is being done in csv – I do not need or want to convert the file to excel – in fact once the file has been amended with respect to any incorrect address details, the next step of processing, uploading the file into another program has to be in csv format. I guess my misunderstanding is that I do open the file via excel and make changes that way, though I save back as csv.

Using input boxes would be an idea but I am wary that there are too many options/variations with respect to addresses:

5 High St = normal residential address

3-5 High St = normal residential address

3/5 High St – Unit/Apartment (proper way to enter it)

Unit 3, 5 High St – Unit and how some customers will enter it

Villa 3, 5 High St (Retirement Village)

PO Box 53 ( other option)

plus a number of other variations.

I am somewhat realistic in not expecting to get a perfect file unless subscribing to a full address validation program, so I probably will have to continue having some cleanup work each day before uploading the amended file, just want to reduce the work and avoid the most common/obvious issues like dates.

Reply | Quote