News, tips, advice, support for Windows, Office, PCs & more
Home icon Home icon Home icon Email icon RSS icon

We're community supported and proud of it!

  • how to prevent date formatting in csv files

    Home » Forums » AskWoody support » Productivity software by function » MS Excel and spreadsheet help » how to prevent date formatting in csv files

    Author
    Topic
    #2396431

    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?

    Viewing 3 reply threads
    Author
    Replies
    • #2396450

      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?

    • #2396483

      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.

    • #2396508

      Windows 10 Pro version 21H2 build 19044.1387 + Microsoft 365 (group ASAP)

    • #2396576

      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.

    Viewing 3 reply threads
    Reply To: how to prevent date formatting in csv files

    You can use BBCodes to format your content.
    Your account can't use Advanced BBCodes, they will be stripped before saving.