Submit Hint Search The Forums LinksStatsPollsHeadlinesRSS
14,000 hints and counting!

Convert text files to CSV format for analysis System
Have you ever received a text file that contains a lot of data you'd like to analyze? Rather than copy-and-paste it, item by item, into your spreadsheet app, here's another solution. In my case, I wanted to look at Apple's downloaded application stats for iPhone Developers. Unfortunately, there are in an ugly pure text format. To make them more useful, open then in TextEdit and convert them to CSV files. This is relatively easy to do:
  1. Find a tab character in the file. This isn't too hard to do, because that's how info is separated. Make sure it's only one tab and not two tabs. Copy the character. (Alternatively you could open up any new document, type a tab character, and copy it.)
  2. Open Find and Replace (Command-F). For the Find value, paste the tab character. You must paste it, as typing Tab jumps to the next field. Set the Replace vale to a comma (,).
  3. Hit Replace All. You'll see plenty of places where there are now multiple commas (especially with Daily reports). Ignore this; everything will be fine.
  4. I use Save As mostly as a precaution, but Save will work. All you need to do is change the extension to .csv for Comma Separated Value. If no extension is showing, type it on you own. When prompted, say that you do want to use CSV as the format.
  5. Open the CSV file in Numbers or Excel, etc. Not only is the layout nice, but you can now run math functions on the data, if that's your thing.
Unfortunately, download stats files don't come like this, but it's only a few seconds per file. Also, you could potentially write an Automator/AppleScript solution to handle the work.

[robg adds: Excel is pretty good at parsing many pure text files, so you could also try asking Excel to simply open the downloaded file first, to see what you get.]
    •    
  • Currently 2.69 / 5
  You rated: 5 / 5 (13 votes cast)
 
[28,442 views]  

Convert text files to CSV format for analysis | 11 comments | Create New Account
Click here to return to the 'Convert text files to CSV format for analysis' hint
The following comments are owned by whoever posted them. This site is not responsible for what they say.
Convert text files to CSV format for analysis
Authored by: jcbeckman on Sep 22, '09 07:51:58AM

Most spreadsheet programs already understand tab-delimited files.



[ Reply to This | # ]
Convert text files to CSV format for analysis
Authored by: asmeurer on Sep 22, '09 08:00:56AM

If you're going to be doing serious find and replacing, I would recommend TextWrangler. If you are willing to take an hour or two to learn how to do regular expression (grep) pattern matching, it will make your life a whole lot easier. See the TextWrangler help for a good guide.



[ Reply to This | # ]
Convert text files to CSV format for analysis
Authored by: Bodoggy on Sep 22, '09 08:17:21AM

Hmm, to call this a hint is a stretch. Every spreadsheet program I have worked with understands tab-delimited files by default.

It's like writing a hint about how Word can open RTF files.



[ Reply to This | # ]
Convert text files to CSV format for analysis
Authored by: ctierney on Sep 22, '09 08:29:51AM

Watch out for preexisting commas, otherwise you may end up with extra columns. I usually stick with tabs for that reason.



[ Reply to This | # ]
Really??
Authored by: derPlau on Sep 22, '09 08:49:25AM
I mean, seriously: not only can most spreadsheets understand files saved as tab delimited text; most actually understand that if you simply paste a bunch of tab-delimited text, they should paste them in different cells, delimited by tabs.

[ Reply to This | # ]
Convert text files to CSV format for analysis
Authored by: mrmkirsch on Sep 22, '09 08:57:59AM

In CSV format, strings that contain embedded commas must be surrounded by double quotation marks. Failing to do that will cause embedded commas to be interpreted as extra fields.

As others have mentioned already, most spreadsheet programs are capable of importing tab-delimited files without the need for conversion to CSV format.



[ Reply to This | # ]
Convert text files to CSV format for analysis
Authored by: astrosmash on Sep 22, '09 12:22:00PM

In addition to what others have said, tab-delimited files have that advantage that you can copy-and-paste them directly into an existing spreadsheet (Numbers, OpenOffice, Excel) which is something you can't do with Comma-delimited files.



[ Reply to This | # ]
Press opt-tab to type a tab
Authored by: lullabud on Sep 22, '09 12:57:53PM

In the TextEdit find/replace window, you can simply type opt-tab to insert the tab character into the box.



[ Reply to This | # ]
Convert text files to CSV format for analysis
Authored by: Dr. T on Sep 22, '09 01:13:56PM

I agree with Bodoggy: This is one of the lamest hints I've seen here. Excel works well on tab delimited text. So do most database programs.

For parsing text with no obvious delimiters, I rely on BBEdit which can use grep and regular expressions to perform complex find and replace operations.



[ Reply to This | # ]
Convert text files to CSV format for analysis
Authored by: Skurfer on Sep 22, '09 01:35:59PM

There's no need to copy, bring up the Find dialogue, then paste.

Just select something and hit ⌘E and it will become the text you're searching for. Select a , and hit ⇧⌘E and it will become the replacement text. Now, when you hit ⌘F to find/replace, you'll see the text already there.



[ Reply to This | # ]
Convert text files to CSV format for analysis
Authored by: tedw on Sep 22, '09 02:47:04PM

well, if you just want to view the data in a nice table, open the tab-delimited file in Word, type command-A to select all, and then choose Convert/Convert text to table from the Table menu (I assume pages has an equivalent way of doing this).



[ Reply to This | # ]