Data File Format

[jwhittaker]

I have been using .csv files for almost all my data files over the last several years. I have seen some weird things happening with the data in these files lately. It's not just with Fusion desktop that I'm seeing it with either. What is everyone else using for the data format?

comma delimited?

tab delimited?

Some issues I'm seeing things disappear other than letters dates changing format.

If I open my csv in excel there will be a comma ($10,000) but if I open it in a text editor it's not there. I've have seen just the opposite as well.

I've seen the month and day swop places in dates looking at the same file in excel and a text editor.

Does anyone have any ideas or suggestions about a stable data format.

[Dan Korn]

Excel is not always your friend in terms of managing plain-text data files. If you insist on using Excel (the World's Most Popular Database Program™) to manage your files, I would at least use a tab-delimited file. The comma-delimited (CSV) format forces escapes for comma characters in the data using double-quotes, which in turn forces double-quotes in the data to be escaped, which results in a file full of stuff that's hard to interpret. FusionPro does a very good job of dealing with these idiosyncrasies of Excel, but there can still be ambiguities.

 

But regardless of whether you're using CSV or tab-delimited files, when you look at them in Excel, what you see is not always what you get. Excel likes to be clever and try to automatically format the data for display, so you're not always seeing the actual data file contents. So if you really want to see the raw data in the file, you need to open it in a plain-text editor, such as Notepad. On Mac, the default text editing program, TextEdit, also likes to play some tricks, such as changing a file's encoding, or even changing it to RTF, so I recommend using an editor such as TextWrangler or Xcode. Or you can use the handy "Edit Flat File Data" menu option in FusionPro.

[jwhittaker]

Thanks Dan.

When ever I see these problems show up the only way to fix them is to save them as a tab delimited text file. That has always seem to the most stable.

[ScottHillock]

The only safe way to open comma separated or tab delimited files in excel is to first remove the extension from the filename. With an extension Excel likes to assume the file format. Then open Excel and tell it to open the file. Here you can specify your delimiters, etc. The important screen is where you can define the "column data format". Select all of your columns and change the setting from General to Text. General will autoformat columns, drop leading zeroes, etc. Changing the format to Text will leave your data as is.