1

One of my jobs is to upload product data to the company store. The data is initially pulled from the company database by my supervisor, and she puts it into an Excel spreadsheet and exports as CSV. I then convert the CSV file into the format the store software desires and upload it. I generally use a combination of short Java programs I've written and OpenOffice Calc to make the adjustments.

Somewhere in the process, product numbers sometimes get converted to dates. I've noticed OO Calc sometimes does this, and I imagine Excel has a similar annoying feature. I've Googled until my fingers bleed trying to find a way to turn it off in OO, and I can't find a solution. Even if I did, my supervisor's use of Excel could be the culprit. Often, these conversion problems aren't noticed until the products are already listed on the e-store, and I haven't had the opportunity to trace the process and find the definite source of the problem

What is the best way to handle CSV data in such a way that none of the initial values are changed when it's passed around to different programs?

Improve this question

4 Answers 4

Reset to default
1

If you could bring your supervisor to not use Excel, export directly to CSV.

Improve this answer
1
  • Agreed, using Excel in this process has no purpose, extract from the DB as a CSV.
    – Hydaral
    Mar 2, 2011 at 12:28
0

Try enclosing the numbers in quotes as you process it through your java program. OO/Excel will be more likely to treat it as text and at least not mangle it into some bad date format.

Improve this answer
1
  • Java tends to be the last part of the process, though. If the damage is done by Excel or OO, it's already done by the time Java touches it.
    – kallenboone
    Oct 16, 2010 at 23:23
0

Well the best way would probably (IMHO) to stop using excel and openoffice and do the whole thing in java. The next best thing would be to stop using openoffice and do that bit in java.

But if you really want to continue to use excel and/or openoffice you might get around your problem by temporarily change the product numbers to something that definitely doesn't look like a date. For example you could surround the product numbers with some special string (that never occur in the product number naturally) that you then remove before doing the final export to csv.

Improve this answer
5
  • I would love to use this solution, but I'm not sure it's possible. My supervisor is not an IT person; she's marketing. She gets the data from a database. Some of the company databases are accessed with Access. Others are interfaced with a VB program created by the IT department. I'm pretty sure (but not 100% certain) that she gets the product data from the VB program, but I'm not sure what format it's in when she gets it. It may be that she's required to use Excel to make it into a CSV. I'm going to ask her to explain the process and see what I can do from there. Thank you for the reply!
    – kallenboone
    Oct 16, 2010 at 23:34
  • @Mongooseman - Perhaps you can get at the data before she imports it into excel? Or perhaps you can skip excel altogether and get the raw data from the database. You might even save her some work.
    – Nifle
    Oct 16, 2010 at 23:38
  • That would be ideal, but there are a few problems in the way. The biggest is that I often work remotely. The company's server--where the database is stored--is only accessible through the company network, which cannot be accessed remotely. I'm sure there's a way to make it available via the Internet, but I doubt the IT department would set it up just for me. It should be perfectly doable during the summer and Christmas breaks when I'm working in-house, though.
    – kallenboone
    Oct 17, 2010 at 0:02
  • @Mongooseman - Could you create an excel template where you specify each columns datataype and import the data using that? (not an excel user myself)
    – Nifle
    Oct 17, 2010 at 0:06
  • I don't have Excel, and I don't know a lot about using it. I could probably figure it out and do it with the computers in the CS department's computer lab, though. I need to find out exactly how my supervisor gets the data from the database to me first, to make sure it's necessary. I'm waiting on her to email me back.
    – kallenboone
    Oct 17, 2010 at 0:26
0

It seems to me that the solution is to be sure that the data fields are set up to interpret the data properly. I have selected a column, for example and set it's "type" and then added the data. Or, change the format AFTER the data is imported.

EDIT: I re-read your question. I think the answer to your question is to be sure the data spreadsheet itself is correctly formated first.

Regards,

Improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .