Remove comma from csv file python
Try with Show
Optional convert to
Sample Frame Used:
I have a .csv that was created using input_xls.to_csv, and everything delimits correctly. However, within some of the string records are commas (e.g. a business name like 'Widgets, Inc.'). The result is incorrect delimiting when importing the .csv into a file geodatabase using arcpy.conversion.TableToTable - values end up shifted over into other columns. I want to replace the commas with a space. I cannot figure out how to use either something like .apply(lambda x: x.replace(',', ' ')) or [val if val else " " for val in row] + ([" "] * (len(first_row) - len(row))) (found here: https://community.esri.com/t5/python-questions/find-and-replace-blanks-in-csv-with-python/m-p/673632) Any help to nudge me in the right direction would be appreciated. Thanks.
6 Replies What you can do is read your excel file into an array, where when appending data into the array, you call the replace command to get rid of the comma.
And then use that array to create the csv.
My example is pulling data from a SDE database but I am pretty sure you can use something like openpyxl to read through an excel sheet. You might want to try bringing in the csv back into Excel, then try the Excel to Table tool to see if it handles the embedded commas better when bringing back into a file geodatabase What is your input_xls? Is it a pandas DataFrame? Yes, I'm using pandas .read_excel and .to_csv I was out Friday and will come back to this later today, but plan to look further at what Matt Kramer posted above. Thanks Personally I cannot recreate your probleme, but you have two options. 1. change regional settings of your machine to use different seperators (semicolon for example) 2. in the same folder as your data add file "schema.ini", where you specify how data is seperated, additionnaly you can change the name of columns and its types. I never followed-up with the solution I found using csv and pandas: with open(cama_csv_file, 'r') as f1: with open(cama_csv_file_final, 'w+') as f2: df = pd.read_csv(f1) df.replace(',',' ', regex=True, inplace=True) df.to_csv(f2, index=False, line_terminator='\r') f2.close() f1.close() Creating a new .csv is not an issue, because I delete all of them at the end of the process.
How do I remove commas from a CSV file?Remove comma from Text String. Select the dataset.. Click the Home tab.. In the Editing group, click on the Find & Replace option.. Click on Replace. This will open the Find and Replace dialog box.. In the 'Find what:' field, enter , (a comma). Leave the 'Replace with:' field empty. ... . Click on Replace All button.. How do I remove a comma from a dataset in Python?Using replace() function, we swap the commas in the python string with null elements.
How do I manage a CSV file in Python?CSV files can be handled in multiple ways in Python.. Import the csv library. import csv.. Open the CSV file. The . ... . Use the csv.reader object to read the CSV file. csvreader = csv.reader(file). Extract the field names. Create an empty list called header. ... . Extract the rows/records. ... . Close the file.. How do I change CSV separator in Python?How to change the delimiter in a CSV file. Create a new Python file in the location where your CSV file is saved. ... . Open up an Anaconda Prompt instance. ... . Type python change_delimiter.py (replacing change_delimiter.py with the name of your Python file) then press Enter.. |