How to Upload Csv When Data Has Commas in It

In that location are ii ways to import information from a text file with Excel: you tin open up it in Excel, or y'all can import it as an external data range. To consign data from Excel to a text file, use the Save As control and modify the file blazon from the drop-downwards menu.

There are two ordinarily used text file formats:

• Delimited text files (.txt), in which the TAB graphic symbol (ASCII character code 009) typically separates each field of text.

• Comma separated values text files (.csv), in which the comma grapheme (,) typically separates each field of text.

You tin change the separator character that is used in both delimited and .csv text files. This may exist necessary to make sure that the import or export performance works the way that you desire it to.

Note:You can import or export up to one,048,576 rows and xvi,384 columns.

Import a text file by opening it in Excel

You lot tin can open a text file that you created in another program as an Excel workbook by using the Open up control. Opening a text file in Excel does non change the format of the file — you can meet this in the Excel title bar, where the proper name of the file retains the text file name extension (for example, .txt or .csv).

1. Get to File > Open and browse to the location that contains the text file.

2. Select Text Files in the file type dropdown list in the Open dialog box.

3. Locate and double-click the text file that you lot want to open.

   • If the file is a text file (.txt), Excel starts the Import Text Sorcerer. When you lot are done with the steps, click Finish to complete the import performance. Run across Text Import Wizard for more information virtually delimiters and advanced options.

   • If the file is a .csv file, Excel automatically opens the text file and displays the data in a new workbook.

     Note:When Excel opens a .csv file, it uses the current default data format settings to translate how to import each cavalcade of information. If yous desire more flexibility in converting columns to unlike data formats, you can use the Import Text Wizard. For case, the format of a information column in the .csv file may be MDY, but Excel'south default data format is YMD, or you desire to catechumen a column of numbers that contains leading zeros to text and then you can preserve the leading zeros. To strength Excel to run the Import Text Wizard, yous tin can change the file name extension from .csv to .txt before you lot open up it, or you can import a text file by connecting to it (for more than information, see the following department).

Import a text file by connecting to it (Ability Query)

You can import information from a text file into an existing worksheet.

1. On the Data tab, in the Get & Transform Information group, click From Text/CSV.

2. In the Import Data dialog box, locate and double-click the text file that yous want to import, and click Import.

3. In the preview dialog box, y'all take several options:

   • SelectLoad if you want to load the data directly to a new worksheet.

   • Alternatively, select Load to if yous want to load the information to a table, PivotTable/PivotChart, an existing/new Excel worksheet, or just create a connectedness. You as well accept the option of adding your data to the Data Model.

   • Select Transform Data if you want to load the information to Power Query, and edit it earlier bringing information technology to Excel.

If Excel doesn't convert a particular column of information to the format that you want, then you can convert the data after y'all import it. For more information, see Convert numbers stored as text to numbers and Convert dates stored equally text to dates.

Consign information to a text file past saving it

You tin can convert an Excel worksheet to a text file past using the Relieve As command.

1. Go to File > Save As.

2. Click Browse.

3. In the Save Every bit dialog box, under Save as type box, choose the text file format for the worksheet; for example, click Text (Tab delimited) or CSV (Comma delimited).

   Note:The unlike formats support dissimilar feature sets. For more information about the feature sets that are supported by the different text file formats, see File formats that are supported in Excel.

4. Browse to the location where you want to save the new text file, and and then click Relieve.

5. A dialog box appears, reminding you that only the current worksheet will be saved to the new file. If you are certain that the current worksheet is the ane that you want to salve equally a text file, click OK. Y'all tin save other worksheets as separate text files by repeating this procedure for each worksheet.

   You may also see an alert below the ribbon that some features might exist lost if yous save the workbook in a CSV format.

For more information about saving files in other formats, see Save a workbook in another file format.

Import a text file past connecting to it

You can import data from a text file into an existing worksheet.

1. Click the cell where you lot desire to put the data from the text file.

2. On the Data tab, in the Get External Data group, click From Text.

3. In the Import Data dialog box, locate and double-click the text file that you want to import, and click Import.

   Follow the instructions in the Text Import Wizard. Click Help on any page of the Text Import Sorcerer for more data about using the wizard. When you are done with the steps in the wizard, click Finish to complete the import functioning.

4. In the Import Data dialog box, do the following:

   1. Under Where do you desire to put the data?, practice one of the following:

      • To return the data to the location that you selected, click Existing worksheet.

      • To render the data to the upper-left corner of a new worksheet, click New worksheet.

   2. Optionally, click Backdrop to set refresh, formatting, and layout options for the imported data.

   3. Click OK.

      Excel puts the external information range in the location that y'all specify.

If Excel does non convert a column of information to the format that you want, you lot tin convert the data afterwards yous import information technology. For more information, run into Catechumen numbers stored as text to numbers and Convert dates stored every bit text to dates.

Export data to a text file by saving it

You can convert an Excel worksheet to a text file past using the Save Equally command.

1. Become to File > Salve Every bit.

2. The Save As dialog box appears.

3. In the Save every bit type box, cull the text file format for the worksheet.

   • For instance, click Text (Tab delimited) or CSV (Comma delimited).

   • Note:The dissimilar formats support dissimilar feature sets. For more data about the feature sets that are supported past the dissimilar text file formats, see File formats that are supported in Excel.

4. Browse to the location where you lot want to relieve the new text file, and then click Save.

5. A dialog box appears, reminding you that only the current worksheet will be saved to the new file. If you are sure that the current worksheet is the one that you lot desire to save as a text file, click OK. You tin save other worksheets every bit split up text files by repeating this process for each worksheet.

6. A second dialog box appears, reminding you that your worksheet may contain features that are not supported by text file formats. If you are interested only in saving the worksheet data into the new text file, click Yes. If you are unsure and would like to know more than virtually which Excel features are non supported by text file formats, click Assist for more data.

For more information well-nigh saving files in other formats, see Save a workbook in another file format.

The way you modify the delimiter when importing is different depending on how y'all import the text.

• If you use Get & Transform Information > From Text/CSV, after you lot cull the text file and click Import, choose a character to use from the list under Delimiter. You can meet the outcome of your new choice immediately in the data preview, so you can exist sure yous make the pick you desire before you proceed.

• If y'all employ the Text Import Magician to import a text file, yous tin can change the delimiter that is used for the import operation in Step 2 of the Text Import Magician. In this footstep, you lot tin also change the way that consecutive delimiters, such every bit sequent quotation marks, are handled.

  Encounter Text Import Wizard for more than data about delimiters and advanced options.

If yous desire to apply a semi-colon as the default list separator when you Relieve As .csv, just need to limit the alter to Excel, consider changing the default decimal separator to a comma - this forces Excel to use a semi-colon for the list separator. Plain, this volition also change the manner decimal numbers are displayed, so also consider changing the Thousands separator to limit any confusion.

1. ClearExcel Options > Advanced > Editing options > Use organisation separators.

2. Set Decimal separator to , (a comma).

3. Set Thousands separator to . (a period).

When you lot save a workbook as a .csv file, the default listing separator (delimiter) is a comma. Y'all tin modify this to another separator character using Windows Region settings.

Caution:Changing the Windows setting will cause a global change on your estimator, affecting all applications. To simply change the delimiter for Excel, see Change the default listing separator for saving files equally text (.csv) in Excel.

1. In Microsoft Windows 10, correct-click the Kickoff push button, and then click Settings.

2. Click Fourth dimension & Linguistic communication, and so click Region in the left panel.

3. In the main panel, under Regional settings, click Additional date, fourth dimension, and regional settings.

4. Under Region, click Alter date, time, or number formats.

5. In the Region dialog, on the Format tab, click Additional settings.

6. In the Customize Format dialog, on the Numbers tab, type a grapheme to use every bit the new separator in the Listing separator box.

7. Click OK twice.

1. In Microsoft Windows, click the Start push, and then click Command Console.

2. Under Clock, Language, and Region, click Change date, fourth dimension, or number formats.

3. In the Region dialog, on the Format tab, click Additional settings.

4. In the Customize Format dialog, on the Numbers tab, type a character to use as the new separator in the Listing separator box.

5. Click OK twice.

Note: Afterwards you alter the list separator character for your computer, all programs employ the new character as a list separator. You can change the graphic symbol dorsum to the default character by post-obit the same procedure.

Need more help?

You can e'er ask an skillful in the Excel Tech Community or get back up in the Answers community.

See As well

Import information from external data sources (Power Query)

harrisitardarand1976.blogspot.com

Source: https://support.microsoft.com/en-us/office/import-or-export-text-txt-or-csv-files-5250ac4c-663c-47ce-937b-339e391393ba

Share this post