How JustAnswer Works:
  • Ask an Expert
    Experts are full of valuable knowledge and are ready to help with any question. Credentials confirmed by a Fortune 500 verification firm.
  • Get a Professional Answer
    Via email, text message, or notification as you wait on our site.
    Ask follow up questions if you need to.
  • 100% Satisfaction Guarantee
    Rate the answer you receive.
Ask Dustin Your Own Question
Dustin
Dustin, Computer Support Specialist
Category: Software
Satisfied Customers: 12992
Experience:  I have vast experience with configuring & repairing and utilizing software.Also worked in The US Army as A Network Switching Systems Operator/Controller
19897260
Type Your Software Question Here...
Dustin is online now
A new question is answered every 9 seconds

I'm wondering if there is a way to remove the extra commas

Customer Question

I'm wondering if there is a way to remove the extra commas at the end of a csv export from Google Sheets?
Details:
I have a spreadsheet where the rows have a varying number of fields. When I save the spreadsheet as a .csv it pads the shorter fields with extra commas. Any way to remove these. Basically, I just can't have a row end in a comma.
Spreadsheet looks like this:
RED ORANGE
YELLOW GREEN BLUE PURPLE BROWN
WHITE
downloading as .csv gives this:
RED,ORANGE,,,
YELLOW,GREEN,BLUE,PURPLE,BROWN
WHITE,,,,
I need to trim the trailing commas off of the .csv file before it gets saved. Any ideas?
Submitted: 1 year ago.
Category: Software
Expert:  Dustin replied 1 year ago.
Welcome to Justanswer.com, my name is ***** ***** I will do my best to assist you. Before you export, you may have this showing in the formula fields for some of your columns. &"," That would show up at the end of the formula, and all that sybmol does is add a comma. If you see those in your spreadsheet before exporting.. We may need to clear those up and remove them. They should only appear at the end of the formulas so removing them shouldnt affect anything. If you see them, I would test by removing the &"," symbol from one column /formula, export.. See if that fixed the issue for that column, and if so then we can repeat the fix on other columns. If this doesnt help or doesnt fix the issue, let me know and maybe I can look at the sheet and try to figure out what the issue is. I strive to provide great service, and greatly appreciate a rating of excellent ( 5 stars ) selected and submitted below this answer or at the top right of the screen. I always answer follow up questions.Dustin S.Senior Technician and Network Switching Systems Operator / Maintainer.
Customer: replied 1 year ago.
No, there is nothing like that on the sheet. I think it requires a Google Apps script that saves to csv and then strips off the commas.
Expert:  Dustin replied 1 year ago.
To be clear, from the google sheets page your choosing file > download as > csv ? Or are you choosing another download setting?
Customer: replied 1 year ago.
You're correct, I'm going to File, Download as csv. It seems when downloading as .csv it wants to pad all the rows with empty fields so they all contain the same number of fields. I need it to not do that, and not have a comma at the end of the last (populated) field, but still contain commas between the fields (obviously). Thanks
Expert:  Dustin replied 1 year ago.
How about if we download as excel instead? Or open office format. ( if you don't have excel ) We could then export from excel as csv or open office, and it may not add the commas. What I can say is that this isn't normal behavior, something is causing that sheet to export with the added commas.
Customer: replied 1 year ago.
Excel will do the same thing actually. And if I'm going to have to save it as Excel, then open it again in excel, I might as well create it in Excel in the first place (which isn't what I want to do).
Expert:  Dustin replied 1 year ago.
Understood. Would it be possible for you to share or make the sheet public and provide a link so I can have a look at it and try to figure out whats causing the issue?
Customer: replied 1 year ago.
https://docs.google.com/spreadsheets/d/1TGwhbdAKQZiwQz5INoEytDRGTC3UgKYpNpWr8gdNWSY/edit?usp=sharing
Expert:  Dustin replied 1 year ago.
Ive not been able to repeat the problem. When I download as csv, and then open the file ( using excel or open office ) I dont see the commas showing. ( the only change I did was choose format, clear formatting) If you can get the commas to occur again, please tell me how you did so. Such as , download, save as csv. Then opened with " program"
Customer: replied 1 year ago.
Try opening with a text editor like notepad++, TextPad, etc. . I need it to stay in a .csv format for FTP'ing. I'm not trying to import it back into Excel.
Expert:  Dustin replied 1 year ago.
Understood. Those commas will show in basic text editors, each comma represents a cell with blank data. Or rather, the space in between commas represents a cell with blank data. If you take the csv file and open in ( not import, just open ) excel or open office or any other spreadsheet app, those commas disappear and wont show. But when you open it with a text edit program, it has to show the commas or else it has no way to represent an empty cell. For instance, if you put the number zero beneath brown we see this when we open it with notepad ++ BLUE,RED,,,ORANGE,YELLOW,BLACK,BROWN,WHITEPURPLE,,,0, Showing us that the zero is the third cell after purple.. Short version. There is no way to remove the commas when you export as csv, or else it would no longer function as a "comma separated values" formatted file.. If you remove the commas then you only have a text file with no way for other programs to recognize what data goes in what cells.. If you have further questions or concerns, let me know~ I strive to provide great service, and greatly appreciate a rating of excellent ( 5 stars ) selected and submitted below this answer or at the top right of the screen. I always answer follow up questions.Dustin S.Senior Technician and Network Switching Systems Operator / Maintainer.
Customer: replied 1 year ago.
I'm not asking to remove all the commas, just the trailing ones so a row never ends with a comma. It's adding a bunch of empty fields to the shorter rows, and that's throwing the program off that I need to load this into. I just need the number of fields in the csv to represent the number of populated fields on the spreadsheet. I don't think you understand the business case. Is there anyone else that might know Google Sheets or scripting, otherwise I'll just ask for a refund.
Expert:  Dustin replied 1 year ago.
I understand how the function works. Ive explained how the function works. This has always been the case with google sheets and many other spreadsheet programs. If you dont want commas, export in tsv instead of csv Other wise, your going to have to convert with powershell or use a different spreadsheet program that will do what you want.

Related Software Questions