How to open CSV file in Excel

When you have a CSV that is separated by semicolons (;) and your system/Excel default is commas (,), you can add a single line to tell Excel what delimiter to use when opening the file. Here are the steps you should follow:

1. Open your CSV using a text editor.
   1. Windows: NotePad (notepad.exe)
   2. Mac: TextEdit (textedit.app)
2. Skip a line at the top, and add sep=; if the separator used in the CSV is a semicolon (;), or sep=, if the separator is a comma (,). 
3. Save, and re-open in any Spreadsheet app.
   
   
   
   Note: This newly added line will not show up when opening the file in Excel. Once you've opened up the Excel file (and the formatting looks good), you can re-save the file (if your default separator is a comma) so the "sep=" line is no longer present, which will allow for imports. 

   After I do that, I already have the formula to text to column based on each record types fixed column width counts.

   Every ID will have RecordType1 and can have additional rows of RecordType2 and RecordType3. Pretend there are 500 Unique IDs (recordtype1), 900 Records total (400additional rows of data for RecordType2 & 3)

   Example:
   HeaderRecord DateDateHourDate L
   RecordType1 First Last ID01 TitleDate1Date2 zerofilldate3
   RecordType2 ID01 Date1Date2 YesNo Spacefill
   RecordType3 ID01 DataInfoDate1 ZerofillDate
   RecordType1 First Last ID02 TitleDate1Date2 zerofilldate3
   RecordType2 ID02 Date1Date2 YesNo Spacefill
   RecordType3 ID01 DataInfoDate1 ZerofillDate
   RecordType1 First Last ID03 TitleDate1Date2 zerofilldate3
   RecordType2 ID03 Date1Date2 YesNo Spacefill
   RecordType3 ID03 DataInfoDate1 ZerofillDate
   TrailerRecord Count

   Reply

   To open a comma-delimited (CSV) file properly, use Excel’s Data Import from Text feature to open the import wizard and set all columns as text.

   If you click the file and allow Excel to open it automatically, the columns might be formatted improperly.

   CSV, or comma-separated values, is a common format for storing and transmitting content, such as contacts, calendars, databases, and spreadsheets. CSV files are used to move data between programs that aren’t ordinarily able to exchange data.

   Excel is supposed to read CSV files, but in most cases, when you open CSV file in Excel, you see scrambled data that’s impossible to read. This article demonstrates how to convert CSV to excel.

   How to open CSV file in Excel:

   • Open CSV files in Excel 2016 and later
   • Open CSV files in Excel 2003

   BONUS: Find out how to export iPhone contacts to Excel.

   ---

   Open a CSV file in Excel

   Excel 2016 and later

   1. Create a new Excel file and click Data ➔ From Text/CSV.
   
   2. Find the CSV file on your PC and click Import.
   
   3. Preview the resulting table in the pop-up window and click Load.
   

   Change the delimiter and encoding type if needed.

   4. All done.
   

   Related articles:

   • How to export iPhone or iPad contacts to Excel
   • How to transfer Excel documents to iPhone or iPad

   ---

   Open CSV files in Excel

   Excel 2003

   1. Open a new Excel document and navigate to the Data tab.
   
   2. Click From Text.
   
   3. Navigate to the CSV file you wish to open and click Import.
   
   4. In the newly-opened window, choose Delimited. Then click Next.
   
   5. Check the box next to the type of delimiter: in most cases, this is either a semicolon or a comma. Then click Next.
   
   6. Click Finish.
   

   That’s it! you have just imported a CSV file to Excel!

   

   Also:

   • What is the difference between CSV and vCard contact formats
   • How to export iPhone contacts to an Outlook CSV file
   • How to open a CSV file in Google Sheets

   Why is my CSV file not opening in Excel?

   CSV (comma delimited) will not open correctly and the data within will be displayed incorrectly. This is due to regional Excel settings that have default list separator options where files will either be read with a comma separator or semicolon separator.

   How do I automatically open a CSV file in Excel with columns?

   To open a comma-delimited (CSV) file properly, use Excel's Data Import from Text feature to open the import wizard and set all columns as text.

   How do I open a CSV file in spreadsheets?

   You can also import CSVs into Google Sheets directly from the file menu. Open a new Google spreadsheet and click File -> Import. Then choose a CSV to upload. You can choose a CSV file stored on Google Drive or upload one from your device.

   How do I open a CSV file in Excel on a PC?

   Open a CSV file in Excel.

   Create a new Excel file and click Data ➔ From Text/CSV..

   Find the CSV file on your PC and click Import..

   Preview the resulting table in the pop-up window and click Load..

   All done..