2 Replies - 764 Views - Last Post: 23 December 2015 - 09:34 AM

#1 ge∅  Icon User is online

  • D.I.C Lover

Reputation: 175
  • View blog
  • Posts: 1,117
  • Joined: 21-November 13

All-text csv files

Posted 23 December 2015 - 12:28 AM

Hello folks,

I don't know where to post this frankly because it's more a software-related issue, but I guess it's as good a place as any.

I work A LOT with CSV files. My clients send me XLS databases, generally not well formed, I need to do some cleaning up and fiddling around with a programming language to extract and format the data the way I want, and the CSV format is both easy to parse and (at least it should be) easy to display as a table in a software like OpenOffice or Excel (because, yes, sometimes it's just faster to simply do stuff in a software)

The issue I'm having all the time is that Excel or OpenOffice detect data types when they should really mind their own business : some data are misinterpreted as a date, a price or a number and are converted to something else when they should really be left the way they are, product references are interpreted as text when they contain a letter and as numbers when they don't, etc. it's very annoying and error-prone.

I would rather want my software to interpret everything as text and deal with data types in my programming language when I parse the CSV file



So do you handle XLS or CSV files a lot, how do you deal with this, is there a way to tell Excell or OpenOffice "just show me my data and stop trying to be clever because you are not!", or do you think I should do it differently?

Thanks a lot in advance for your ideas !

This post has been edited by ge∅: 23 December 2015 - 12:29 AM


Is This A Good Question/Topic? 0
  • +

Replies To: All-text csv files

#2 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6343
  • View blog
  • Posts: 25,601
  • Joined: 12-December 12

Re: All-text csv files

Posted 23 December 2015 - 03:51 AM

In Excel from the Data tab, From Text, and the subsequent Text Import Wizard you can specify that every column is Text, rather than General which will attempt to interpret numbers. You could record the process as a Macro.

I would also consider opening the (text) file first in something like Sublime Text.
Was This Post Helpful? 1
  • +
  • -

#3 ge∅  Icon User is online

  • D.I.C Lover

Reputation: 175
  • View blog
  • Posts: 1,117
  • Joined: 21-November 13

Re: All-text csv files

Posted 23 December 2015 - 09:34 AM

Cool! Thanks andrewsw.

It's true that the process is a bit tedious, I'll check how to do Macros.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1