Java doesn't pick up seconds from a date string from a CSV in Excel

public static final SimpleDateFormat MDY_HMS_SQL_FULL_SDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"); public static final SimpleDateFormat MDY_HMS_MIL_SDF = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss");                                System.out.println("str = " + str);                                if (StringUtilities.isEmpty(str)) { this.setTemporaryBanExpireDate(null); } else if (DateUtilities.HAS_TIME_PATTERN.matcher(str).find()) { // If edited and saved by Notepad this.setTemporaryBanExpireDate(new Timestamp(DateUtilities.MDY_HMS_SQL_SDF.parse(str.trim()).getTime())); } else if (StringUtilities.isDate(str)) { // If edited and saved by Excel this.setTemporaryBanExpireDate(new Timestamp(DateUtilities.MDY_HMS_MIL_SDF.parse(str.trim()).getTime())); }

In my CSV file, I have a column whereby I enter

2024-05-05 08:15:00

If I enter that into the CSV file in Notepad, the  value of str is

2024-05-05 08:15:00

, and the second condition is met:

// If edited and saved by Notepad this.setTemporaryBanExpireDate(new Timestamp(DateUtilities.MDY_HMS_SQL_SDF.parse(str.trim()).getTime()));

However, if I enter

2024-05-05 08:15:00

into the CSV file in Excel, the value of str is

5/5/2024 8:15

, and the thrid condition an't be met, because the seconds are lobbed off by the time it gets from the Excel CSV to Java and throws a java.text.ParseException:

If edited and saved by Excel: 5/5/2024 8:15
java.text.ParseException: Unparseable date: "5/5/2024 8:15"
at java.base/java.text.DateFormat.parse(DateFormat.java:396)
at com.dandylabs.FeedbackBannedIPBeanPopulator.populateBean(FeedbackBannedIPBeanPopulator.java:104)
at com.dandylabs.FeedbackBannedIPUpdater.readCSV(FeedbackBannedIPUpdater.java:282)
at com.dandylabs.FeedbackBannedIPUpdater.perform(FeedbackBannedIPUpdater.java:264)
at com.dandylabs.FeedbackBannedIPUpdater.main(FeedbackBannedIPUpdater.java:307)

I don't know how to fix this, considering users will be editing their CSV files by either Excel or Notepad, depending on who they are, etc.  I need for the expire date to be consistently formatted in such a way as to retain seconds every single time, but if they edit in Excel, the seconds are lost by the time Java gets a hold of it.

Any ideas? I am open (in this particular project) to external libraries if need be, e.g. Apache POI, etc.

Thanks

In Excel, what is the FORMAT of the date/time cell?

Carey Brown wrote:In Excel, what is the FORMAT of the date/time cell?

The cell says

5/5/2024 08:15:00 AM

Phillip Powell wrote: public static final SimpleDateFormat MDY_HMS_SQL_FULL_SDF = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss.SSS"); public static final SimpleDateFormat MDY_HMS_MIL_SDF = new SimpleDateFormat("MM/dd/yyyy HH:mm:ss"); System.out.println("str = " + str);    if (StringUtilities.isEmpty(str)) {    this.setTemporaryBanExpireDate(null); } else if (DateUtilities.HAS_TIME_PATTERN.matcher(str).find()) {    // If edited and saved by Notepad    this.setTemporaryBanExpireDate(new Timestamp(DateUtilities.MDY_HMS_SQL_SDF.parse(str.trim()).getTime())); } else if (StringUtilities.isDate(str)) {    // If edited and saved by Excel    this.setTemporaryBanExpireDate(new Timestamp(DateUtilities.MDY_HMS_MIL_SDF.parse(str.trim()).getTime())); }

Your code snippet is incomplete. You are not using MDY_HMS_SQL_FULL_SDF for instance. We don't know what's in StringUtilities or DateUtilities.

In order for people to help you quickly it's best to provide an SSCCE (Simple Self Contained Compilable Example) that we can download and execute ourselves. An SSCCE is boiled down to only those lines of code necessary to demonstrate the issue and the code should not depend on external code that has not been provided. If possible, external code should be expanded in place.

You can set a custom format in Excel to "m/d/yyyy h:mm:ss" that will not truncate seconds.

Carey Brown wrote:You can set a custom format in Excel to "m/d/yyyy h:mm:ss" that will not truncate seconds.

I wound up with a workaround due to a requirement change: Seconds not needed, so never mind about lobbing off seconds.

I was able to get it to successfully parse in both Notepad and Excel:

public class DateUtilities {        public static final Pattern HAS_TIME_PATTERN_HHMM = Pattern.compile("[0-9]{1,2}:[0-9]{2}"); public static final SimpleDateFormat MDY_HMS_MIL_EXCEL_HMM_SDF = new SimpleDateFormat("M/d/yyyy H:mm"); public static final SimpleDateFormat MDY_HMS_SQL_HHMM_SDF = new SimpleDateFormat("yyyy-MM-dd HH:mm"); } public class FeedbackBannedIPBeanPopulator extends FeedbackBannedIPBean implements Serializable, BeanPopulator { @Override public void populateBean(String[] elements) throws Exception {             for (int i = 0; i < elements.length; i++) { if (elements[i] != null) { str = StringUtilities.cleanXSS(StringUtilities.stripHTML(elements[i])); } else { str = null; }                       switch (i) {                          case 0:     // BANNED DATE (REQUIRED)                                if (StringUtilities.isEmpty(str)) { throw new FeedbackException("Banned Date not found"); } if (str.contains("/")) { // EXCEL this.setBannedDate(new Timestamp(DateUtilities.MDY_HMS_MIL_EXCEL_HMM_SDF.parse(str.trim()).getTime())); } else if (DateUtilities.HAS_TIME_PATTERN_HHMM.matcher(str.trim()).find()) { // Notepad this.setBannedDate(new Timestamp(DateUtilities.MDY_HMS_SQL_HHMM_SDF.parse(str.trim()).getTime())); } else { // ?? throw new FeedbackException("Date \"" + str + "\" is in an unrecognized format"); }                          break;                         case 1:    // TEMPORARY BAN EXPIRE DATE (OPTIONAL)        if (StringUtilities.isEmpty(str)) { this.setTemporaryBanExpireDate(null); } else if (str.contains("/")) { this.setTemporaryBanExpireDate( new Timestamp(DateUtilities.MDY_HMS_MIL_EXCEL_HMM_SDF.parse(str.trim()).getTime())); } else if (DateUtilities.HAS_TIME_PATTERN_HHMM.matcher(str.trim()).find()) { this.setTemporaryBanExpireDate(new Timestamp(DateUtilities.MDY_HMS_SQL_HHMM_SDF.parse(str.trim()).getTime())); } else { throw new FeedbackException("Date \"" + str + "\" is in an unrecognized format"); }                         break;                     }             }        } }

Something like this:
import java.sql.Timestamp; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.regex.Pattern; public class Tmp4 { static final String[] tests = { "2024-05-05 08:15:00", // Notepad "5/5/2024 8:15:00", // Excel with custom format }; static final Pattern NOTEPAD_PAT = Pattern.compile( "\\d{4}-\\d\\d-\\d\\d \\d\\d:\\d\\d:\\d\\d" ); static final Pattern EXCEL_PAT = Pattern.compile( "\\d{1,2}/\\d{1,2}/\\d{4} \\d{1,2}:\\d\\d:\\d\\d" ); public static final SimpleDateFormat NOTEPAD_SDF = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" ); public static final SimpleDateFormat EXCEL_SDF = new SimpleDateFormat( "M/d/yyyy H:mm:ss" ); Timestamp timestamp; public static void main( String[] args ) throws ParseException { Tmp4 obj = new Tmp4(); for( String s : tests ) { obj.method( s ); System.out.println( obj.timestamp ); } } void setTimestamp( Timestamp timestamp ) { this.timestamp = timestamp; } void method( String str ) throws ParseException { System.out.println( "str = " + str ); if( str.isEmpty() ) { this.setTimestamp( null ); } else if( NOTEPAD_PAT.matcher( str ).matches() ) { this.setTimestamp( new Timestamp( NOTEPAD_SDF.parse( str.trim() ).getTime() ) ); } else if( EXCEL_PAT.matcher( str ).matches() ) { this.setTimestamp( new Timestamp( EXCEL_SDF.parse( str.trim() ).getTime() ) ); } } }

Some tweaks I had added.
import java.sql.Timestamp; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.regex.Pattern; public class Tmp4 { static final String[] tests = { "", "xyz", "2024-05-05 08:15:00", // Notepad "5/5/2024 8:15:00", // Excel with custom format }; static final Pattern NOTEPAD_PAT = Pattern.compile( "\\d{4}-\\d\\d-\\d\\d \\d\\d:\\d\\d:\\d\\d" ); static final Pattern EXCEL_PAT = Pattern.compile( "\\d{1,2}/\\d{1,2}/\\d{4} \\d{1,2}:\\d\\d:\\d\\d" ); static final SimpleDateFormat NOTEPAD_SDF = new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" ); static final SimpleDateFormat EXCEL_SDF = new SimpleDateFormat( "M/d/yyyy H:mm:ss" ); Timestamp timestamp; public static void main( String[] args ) { Tmp4 obj = new Tmp4(); for( String s : tests ) { System.out.println( "str = " + s ); try { obj.setTimestamp( s ); System.out.println( obj.timestamp ); } catch( Exception e ) { System.out.println( e ); } System.out.println(); } } void setTimestamp( Timestamp timestamp ) { this.timestamp = timestamp; } void setTimestamp( SimpleDateFormat sdf, String s ) throws ParseException { setTimestamp( new Timestamp( sdf.parse( s ).getTime() ) ); } void setTimestamp( String str ) throws Exception { str = str.trim(); if( str.isEmpty() ) { setTimestamp( (Timestamp)null ); } else if( NOTEPAD_PAT.matcher( str ).matches() ) { setTimestamp( NOTEPAD_SDF, str ); } else if( EXCEL_PAT.matcher( str ).matches() ) { setTimestamp( EXCEL_SDF, str ); } else { throw new Exception( "Invalid date/time format: " + str ); } } }

Due to my personal mental limitations, this will take an extremely long time to digest, but thanks for helping!

Let me know if you have any questions.

Phillip and Carey,

Great question and answers.  I don't understand the entire solution.  I"m learning a lot from reading it.   I'm guessing at what I think its doing.  I added comments to the code.  

import java.sql.Timestamp; import java.text.ParseException; import java.text.SimpleDateFormat; import java.util.regex.Pattern; public class Tmp4 {    static final String[] tests =  //this makes a four element array named tests.  Its used as a short cut so we don't have to import from different file types.  I don't know what the "" blank one is for.    {        "",        "xyz",        "2024-05-05 08:15:00",  // Notepad        "5/5/2024 8:15:00",     // Excel with custom format    };    static final Pattern NOTEPAD_PAT = Pattern.compile(           //These are constant variables.  I'd have to read about the format rules.  i can't tell if its grep or a java convention.            "\\d{4}-\\d\\d-\\d\\d \\d\\d:\\d\\d:\\d\\d" );        static final Pattern EXCEL_PAT = Pattern.compile(            "\\d{1,2}/\\d{1,2}/\\d{4} \\d{1,2}:\\d\\d:\\d\\d" );        static final SimpleDateFormat NOTEPAD_SDF =            new SimpleDateFormat( "yyyy-MM-dd HH:mm:ss" );    static final SimpleDateFormat EXCEL_SDF =            new SimpleDateFormat( "M/d/yyyy H:mm:ss" );        Timestamp           timestamp;        public static void main( String[] args )    {        Tmp4            obj = new Tmp4();        for( String s : tests )    //this will loop for each element in the tests array.        {            System.out.println( "str = " + s );            try            {                obj.setTimestamp( s );                System.out.println( obj.timestamp );            }            catch( Exception e )            {                System.out.println( e );            }            System.out.println();        }    }        void setTimestamp( Timestamp timestamp )  // These might be overloaded methods.   It choses the correct one to use when called by  obj.setTimestamp( s ); above.  Some of the overload choices have more //than one parameter but I only see one parameter named s being passed in.  I don't see how this works.    {        this.timestamp = timestamp;    }        void setTimestamp( SimpleDateFormat sdf, String s ) throws ParseException    {        setTimestamp( new Timestamp( sdf.parse( s ).getTime() ) );    }        void setTimestamp( String str ) throws Exception    {        str = str.trim();        if( str.isEmpty() )        {            setTimestamp( (Timestamp)null );        }        else if( NOTEPAD_PAT.matcher( str ).matches() )        {            setTimestamp( NOTEPAD_SDF, str );        }        else if( EXCEL_PAT.matcher( str ).matches() )        {            setTimestamp( EXCEL_SDF, str );        }        else        {            throw new Exception( "Invalid date/time format: " + str );        }    } }

I hope it was OK that I I jumped into the discussion.  I hope I didn't hi jack anything.

Thanks,

Kevin

Yes, setTimestamp( ... ) is overloaded, there are three of them and the compiler knows which one to choose based on the arguments being passed in. They all result in the field "timestamp" being set.

There is a chain in the way the setTimestamp(...) methods are written.

setTimestamp( String )
calls setTimestamp( SimpleDateFormat, String )
which calls setTimestamp( Timestamp )

Calling any one of them results in the last one being called which does the final setting of the field.

The first one was not originally named setTimestamp(..) but seeing as how that is what it does but using a String argument instead, it makes sense to name it the same. The second one is called by setTimestamp(String) in two places so it eliminates some code redundancy.

@Kevin - please keep line lengths short so that they don't get truncated  when being displayed. Take as many lines as you need.

Carey Brown wrote:. . . please keep line lengths short . . .

. . . and please reserve // comments for things very short. Your long comments should be broken into multi‑line /* comments */

Carey and Campbell,

I didn't know my comments would be truncated.  Now I"m aware of it.

I'll use the /*           */    comment style          

Thank you,

Kevin