Risingline Web Design | Site Map
Search Risingline.com

Using Microsoft Excel to open csv files without reformatting data Email or bookmark this page

CSV (Comma Separated Value) is a common format that HTML based Web forms and scripts use to write data to flat file on a web server. A CSV flat file can record all your form submissions and is a convenient method to archive data on a Web server without using a MySQL database.

One caveat when using a CSV file to record your form's data: it's important not to allow commas in user entered form fields, since a comma will be interpreted as a column when the csv file is created.

Retrieving and formatting csv data

When you view a csv file on your Web server (ideally in a password protected and encrypted directory) it is not formatted and thus difficult to do anything with.

The csv data needs to be converted into an organized and editable spreadsheet format. Since Microsoft Excel is the most common spreadsheet application, this presents a challenge since Excel decides on its own how to format data when opening a csv file. This is not desirable since the data integrity can be compromised. For example Excel usually trucates numbers over 15 digits, and may convert entries with dashes or slashes to date or time formats.

The steps below are intended to ensure that when you open a csv file you get a spreadsheet of editable data exactly as entered by your form user.

Following is the procedure for insuring that Excel opens your csv file without changing your data

Note: this procedure is for Windows XP and Excel 2003. The process may vary slightly with other software.

1. Navigate to the csv file on your server. In Firefox right click anywhere on the page and choose "Save Page As".

2. To force Excel to let us specify the format, manually change the file extension from "csv" to "txt" in the Save As window. Then save the .txt file to your hard drive.

3. Now go to Excel, then open the .txt file from within Excel. (You will probably need to change "Files of type" manually to All Files in order to see the .txt file in Excel's open dialogue.) The .txt extension on the csv file forces Excel to initiate its Text Import Wizard.

4. In the Text Import Wizard Step 1 of 3 specify "Original data type" as "Delimited" and make sure that "Start import at row:" is "1" and "File origin:" is "437 : OEM United States" are selected.

5. In the Text Import Wizard Step 2, ensure only "Comma" is checked under "Delimiters".

6. In Step 3, in the "Data preview" make sure the first column is highlighted. Then scroll to the last column on the right and while holding down shift, click the column with your mouse pointer. All columns will then be highlighted. Under "Column data format" select "Text". Then click "Finish".

7. Now the file will open in Excel with the data exactly as entered by the form user.

Here are a couple other alternatives for displaying csv data in its original format:

Was this helpful?  Do you have any suggestions for improvement?  Please take a minute and let us know.

Instructional content on this page is copyrighted 2006-2008 Generose Corporation.

Next page: Risingline FAQs

© 2011 Risingline Web Design — Boise, Idaho | Legal | Terms of Service | Client Login   Visit Risingline's Facebook pageFollow Risingline on TwitterGoogle Plus