Remove trailing commas from CSV file during exporting.

SWAY14

New Member
Joined
Jul 27, 2022
Messages
15
Office Version
  1. 2019
  2. 2016
  3. 2013
Platform
  1. Windows
  2. MacOS
Hello,
I have been at this for a few days now and nothing. does anyone know how to remove the trailing commas from a csv file while exporting excel sheet. I have a macro that works fine but when I open the csv file with notepad or textedit I can see the 3 commas at the end of the last cell since the header row goes to CQ and my information will always stop at Cn I only need 1 of these 3 commas after the 2. any help will be grateful!

VBA code
Sub Thistheone()
'
' Macro3 Macro
'

'
Range("A1").Select
Range(Selection, Selection.End(xlToRight)).Select
Range(Selection, Selection.End(xlDown)).Select
Selection.Copy
Workbooks.Add
ActiveSheet.Paste
Application.CutCopyMode = False
ActiveWorkbook.SaveAs FileName:= _
"/Users/sway/Desktop/cleint uploads /Testing2.csv", FileFormat:=xlCSVUTF8, _
CreateBackup:=False
Application.DisplayAlerts = False
ActiveWindow.Close True

End Sub
Output file
name,number,individual_first_name,individual_middle_initial,individual_last_name,site_id,individual_gender_id,individual_date_of_birth,individual_title_id,individual_language_id,individual_record_drop_down_id,individual_notes,home_is_aware,partner_is_aware,individual_is_safety_concern,individual_safety_concern_text,individual_is_financial_concern,individual_financial_concern_text,individual_speaks_english,tax_schedule_id,individual_user_defined_country_id,individual_text_1,individual_text_2,individual_text_3,individual_text_4,individual_text_5,individual_text_6,individual_text_7,individual_text_8,individual_drop_down_1_id,individual_drop_down_2_id,individual_drop_down_3_id,individual_drop_down_4_id,individual_drop_down_5_id,individual_drop_down_6_id,individual_drop_down_7_id,individual_drop_down_8_id,individual_drop_down_9_id,individual_drop_down_10_id,individual_drop_down_11_id,individual_drop_down_12_id,individual_checkbox_1,individual_checkbox_2,individual_checkbox_3,individual_checkbox_4,individual_checkbox_5,individual_date_1,individual_date_2,individual_date_3,individual_date_4,individual_date_5,individual_memo_1,individual_memo_2,main_contact_type_id,email_contact_type_id,general_communication_method,email_detail_contact_type_id,email_contact,email_extension,email_is_use_contact,email_comm_instructions,email_in_main_contact,home_contact_type_id,home_contact,home_extension,home_is_use_contact,home_comm_instructions,home_in_main_contact,mobile_contact_type_id,mobile_contact,mobile_extension,mobile_is_use_contact,mobile_comm_instructions,mobile_in_main_contact,work_contact_type_id,work_contact,work_extension,work_is_use_contact,work_comm_instructions,work_in_main_contact,individual_address_line_1,individual_address_line_2,individual_address_line_3,individual_address_drop_down_id,individual_address_county,individual_address_country_id,individual_address_p_code,preenrollment_reason_id,service_id,case_service_group_id,informal_series_id,worker_id,preenrollment_drop_down_1_id,preenrollment_priority_id,preenrollment_notes
individual_pre_enrollment,1,Testing,,Testing,1,2,2007-05-23,,1,,,,,FALSE,,,,,,,4.30E+10,,,testing testing,,4testing,Private Residence,,14,,7,4,4,,,,15,,,,,,,,,,,2022-07-19,,,,Na,,,,3,testing@gmail.com,TRUE,,,,,,,,,2,testing5,,TRUE,,,,,,,,,testing,testing,33,testing,196,21221,68,1065,,,,,2,,,
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
Hi Sway,
One option would be to take out the last 3 columns from your data, if you're not using the last 3 columns.
If you're ever going to use the last 3 columns then you want to keep them.
If you want to just do it as a bit of a hack, you could specify the column range in your code, or use the xlspecialcells(xllastcell).column -3 to set the column if you're going to have variable numbers of columns but always want to drop the last 3.
I like to calculate the row and column values as variables then use that in the select statement. Allows for a little more customisation
 
Upvote 0
Hi Sway,
One option would be to take out the last 3 columns from your data, if you're not using the last 3 columns.
If you're ever going to use the last 3 columns then you want to keep them.
If you want to just do it as a bit of a hack, you could specify the column range in your code, or use the xlspecialcells(xllastcell).column -3 to set the column if you're going to have variable numbers of columns but always want to drop the last 3.
I like to calculate the row and column values as variables then use that in the select statement. Allows for a little more customisation
Thank you very much for your help I was able to goofier it out. must first save the file as mdos file manually once its saved then I can run the macro to export just fine. thank you!
 
Upvote 0

Forum statistics

Threads
1,223,228
Messages
6,170,871
Members
452,363
Latest member
merico17

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top