Excel allows you to import data from text files in two different ways: either open the text file directly within Excel, or import it as an external data range.
To export data from Excel to a text file, use the SAVE AS command and select a different file type from the drop-down menu.
The separator character that is used in .csv and delimited text files can be modified.
The OPEN command can be used to open a text file created in another program as an Excel workbook.
Excel launches the Import Text Wizard if the file is a text document (.txt).
Excel opens the text file automatically and shows the data in a new workbook if the file is a .csv file.
The GET & TRANSFORM DATA group can be used to import data from a text file into an already-existing worksheet.
The GET EXTERNAL DATA group can be used to import data from a text file into an already-existing worksheet.
The SAVE AS command can be used to convert an Excel worksheet to a text file.
You can convert the data after you import it into Excel if it doesn’t automatically convert a specific column of data to the format you desire.
You can convert the data after you import it into Excel if it does not automatically convert a column of data to the format you require.
When importing a text file with the GET & TRANSFORM DATA feature, you can modify the delimiter.
When importing a text file with the Text Import Wizard, you have the option to modify the delimiter.
The list separator (delimiter) that is used by default when you save a workbook as a .csv file is a comma.
With Windows Region settings, you can modify the list separator that is used by default.
Opening a CSV File with Columns in Excel
Comma Separated Values, or CSV for short, indicates that commas are used to separate the data in the file.
Excel may not treat commas as delimiters when you open a CSV file; instead, it may treat them as regular characters.
When opening a CSV file in Excel, the Text Import Wizard is a useful tool that lets you choose the delimiter and other settings.
- Once Excel is open, select the Data tab.
- From the "Get & Transform Data" section, choose "From Text/CSV".
- Click "Next" after selecting "Delimited" in the Text Import Wizard.
Editing an Excel CSV File
Return to the Main Asset Essentials Menu
CSV files are supported for import and export by the Connector Tool.
Although you can work with CSV files in Microsoft Excel, Excel is not a CSV editor specifically.
Working with CSV files in Microsoft Excel can be complicated by auto formatting; we strongly advise avoiding this by following the instructions on this page.
Whenever you need to open your CSV file for editing purposes, we advise you to follow these instructions.
- Excel will attempt to auto-format the data in the CSV file if you double-click the file on your computer. This can lead to problems when importing your data back into Asset Essentials.
- When prompted, select BLANK WORKBOOK when you launch Microsoft Excel.
- Once you click the DATA tab, choose FROM TEXT.
- Locate the CSV file on your computer, then select OPEN.
- Make sure that DELIMITED is your file type selection in the Text Import Wizard window.
- Click on File origin and choose 65001: UNICODE (UTF-8).
- Verify the box labeled "My Data Has Headers."
- Then press the NEXT button.
- Verify that COMMA is the only checked box in the Delimiters section.
- Then press the NEXT button.
- To ensure that every column in the Data preview section is highlighted, scroll to the rightmost column and hold down the Shift key while choosing the last column.
- Choose TEXT under the Column data format section.
- Select "FINISH" from the menu.
- In the subsequent pop-up window, click the OK button to add the data to the spreadsheet.
- In Excel, click the FILE tab and choose SAVE.
- On your computer, select a location for the file to be stored.
- Make sure CSV (COMMA DELIMITED) is the save as type option that is selected.
- Press the SAVE icon.