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

[Phillip Powell]

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

[Carey Brown]

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

[Phillip Powell]

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

The cell says

5/5/2024 08:15:00 AM

[Carey Brown]

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.

[Carey Brown]

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

[Phillip Powell]

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;
                     }
             }
        }
}

[Carey Brown]

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() ) );
		}
	}
	
}

[Carey Brown]

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 );
		}
	}
}

[Phillip Powell]

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

[Carey Brown]

Let me know if you have any questions.

[kevin Abel]

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 );
        }
    }
}

[kevin Abel]

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

Thanks,

Kevin

[Carey Brown]

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.

[Carey Brown]

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.

[Carey Brown]

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

[Campbell Ritchie]

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 */

[kevin Abel]

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