malformed field in csv file

i have the following csv file, which i generated with Java's Writer class:

Writer writer = new BufferedWriter(new OutputStreamWriter(
                      new FileOutputStream(outputFilePath + ".csv"), "UTF-8"));

however, when i try to read it with a python script, i get this:

Exception: E10001: ('The provided timestamp 07/14/2015 1:30:14 PM is malformed. The supported formats are: [%Y-%m-%d %H:%M:%S.%f, %Y-%m-%d %H:%M:%S:%f, %Y-%m-%d %H:%M:%S, %Y-%m-%d %H:%M, %Y-%m-%d, %m/%d/%Y %H:%M, %m/%d/%y %H:%M, %Y-%m-%dT%H:%M:%S.%fZ, %Y-%m-%dT%H:%M:%SZ, %Y-%m-%dT%H:%M:%S]',): Traceback (most recent call last):

if i open the csv file in MS Excel, and immediately resave it, the the python script runs fine.  there must be something Excel is reformatting/removing that Java's Writer is NOT doing.  I am just not sure what it is.  Thanks.
stock-price-hourly.csv
LVL 1
mmingfeilamAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
It's not malformed, cause there is no definition how a CSV should store a date literal.

This is a mere python problem, which cannot read the date literal exported by Java.

The only problem I have with Java part of the code is the fact that it produces a date literal, which requires locale/region interpretation. So You should change the Jave code to export for example an ISO8601 date time literal.

But the important part In your Java snippet is missing: Where does the data comes from? And how do you actually write it?

btw, see also RFC4180.
0
mmingfeilamAuthor Commented:
the data come from yahoo finance which uses UTC time, which i saved as a txt file, but because the file's structure is not the same as what the python script is expecting, i had to parse the txt file and resave as a csv file.  

writerWithActual.append(localTimeStr);
				writerWithActual.append(',');
				writerWithActual.append(price[1]);
				writerWithActual.append(',');
				writerWithActual.append(price[1]);
				writerWithActual.append('\n');


	protected String getLocalTime(String utcTime) {
		String localTimeStr = null;

		long unixSeconds = Long.valueOf(utcTime).longValue();
		Date date = new Date(unixSeconds * 1000L); // *1000 is to convert
													// seconds to milliseconds

		// DATE_FORMAT = "%m/%d/%y %H:%M"
//		SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy h:mm:ss a"); 
//		 %m/%d/%Y %H:%M
		SimpleDateFormat sdf = new SimpleDateFormat("MM/dd/yyyy h:mm:ss a");

		
		sdf.setTimeZone(TimeZone.getTimeZone("UTC")); // give a timezone
														// reference for
														// formating (see
														// comment at the bottom
		localTimeStr = sdf.format(date);
		// System.out.println(formattedDate);

		return localTimeStr;
	}

Open in new window


thanks.
0
Walter RitzelSenior Software EngineerCommented:
Here is a piece of code written in python that have loaded perfectly the data. There was only one caveat: the first 3 lines should be skipped, because they are not dates (they are header). Observe the format string. That is probably making some difference from your code as well.
import csv
import time

f = open('stock-price-hourly.csv', 'r')
csvRows = csv.reader(f,delimiter=',')

next(csvRows)
next(csvRows)
next(csvRows)

for row in csvRows:
	print(row[0])
	dateS = time.strptime(row[0],'%m/%d/%Y %I:%M:%S %p')
	print(dateS)

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

mmingfeilamAuthor Commented:
i am using a python file from an existing library, and they only work if i use the format "MM/dd/yyyy h:mm:ss a".  what i don't understand is why does it work if i open the csv file in MS Excel and resave, but not otherwise.  thanks.
0
mmingfeilamAuthor Commented:
here a csv file that doesn't work.
stock-price-hourly.csv
0
Walter RitzelSenior Software EngineerCommented:
Which is the library? Can you please publish your code? As I hope you have tested, my code works perfectly to load the csv file, and get the format correct.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Python

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.