Quick last-minute fix... remove trailing commas from CSV Topic is solved

[Nosyarg]

Last piece of the code, and I've been fighting with this for a week.

I need to be able to remove trailing commas from CSVs. One of the forms I've got writing, adds a whole LOT of extra blanks, always at the end of the CSV file, but I'm looking for a more elegant solution than opening the file in notepad and backspacing over the commas.

Any ideas that would start at the end of the file and remove commas until it finds a character OTHER than a comma?

Thanks!

[boiler]

See RTrim(). To remove trailing commas in each row, you would need to loop through them and apply it to each. You could use RegExReplace() to replace all trailing commas in all rows at once.

[sofista]

Sample code to remove trailing commas and blank spaces in each row that may get what you want:

Code: Select all

data =
(
John,Doe,120 washington st.,Riverside, NJ, 05057, ,,
Josh,McGintis,444 hobo Av.,Phila, PA,01991,,, ,,    ,,
"Karl ""Da Man""",Medici,531 Madison St.,Riverside, OK,08705, ,,,, 
Stephen,Charles,"7452 Terrace ""At the Plaza"" road",SomeTown,SD, 94321, , , , , ,    
,Blankman,,SomeCity, HD, 00765,,,, ,
"Joan ""the bone"", Mary",Jet,"7th, at Terrace plc",Desert City,FL,00789,,,,,,,
)

MsgBox, % RegExReplace(data, "`am)[, ]*$")

/* Output:

John,Doe,120 washington st.,Riverside, NJ, 05057
Josh,McGintis,444 hobo Av.,Phila, PA,01991
"Karl ""Da Man""",Medici,531 Madison St.,Riverside, OK,08705
Stephen,Charles,"7452 Terrace ""At the Plaza"" road",SomeTown,SD, 94321
,Blankman,,SomeCity, HD, 00765
"Joan ""the bone"", Mary",Jet,"7th, at Terrace plc",Desert City,FL,00789

 */

[Nosyarg]

thanks for the reply. will I need to import the entire file into ahk to run this replacement, or can I simply reference an external file? I'd prefer to keep the data in place as much as possible, without opening anything in a window that a user could accidentally interfere with or screw up. Transferring everything in and out of active macros scares me a bit, based on the amount of data flowing.

The contents of the external file, database.csv:

DATA1,DATA2,DATA3,DATA4,DATA5,DATA6,DATA7,DATA8,DATA9
A1,A2,A3,A4,A5,A6,A7,A8,
B1,B2,B3,B4,B5,B6,B7,B8,B9
C1,C2,C3,C4,C5,C6,C7,C8,
, ,,,,,,,
, ,,,,,,,
, ,,,,,,,
, ,,,,,,,
, ,,,,,,,

[sofista]

Surely, the second alternative is the preferred one. Try this:

Code: Select all

FileRead, data, C:\Users\[...]\Desktop\database.csv    ; path to the file
if not ErrorLevel {
	data := RegExReplace(RegExReplace(data, "`am)[, ]*$"), "\R*\Z")    ; edited
	FileDelete, C:\Users\[...]\Desktop\database.csv
	FileAppend, % data, C:\Users\[...]\Desktop\database.csv
	data := ""
}

I introduced an additional condition to the regular expression to delete all the empty lines—the same ones that had the commas removed—at the end of the file.

[Nosyarg]

Surely, the second alternative is the preferred one. Try this:

Code: Select all

FileRead, data, C:\Users\[...]\Desktop\database.csv    ; path to the file
if not ErrorLevel {
	data := RegExReplace(RegExReplace(data, "`am)[, ]*$"), "\R*\Z")    ; edited
	FileDelete, C:\Users\[...]\Desktop\database.csv
	FileAppend, % data, C:\Users\[...]\Desktop\database.csv
	data := ""
}

I introduced an additional condition to the regular expression to delete all the empty lines—the same ones that had the commas removed—at the end of the file.

HUGE THANK YOU!!!!!

[garry]

One of the forms I've got writing, adds a whole LOT of extra blanks, always at the end of the CSV file,

@Nosyarg but why this happens ? When read source-file , in a loop can use > if row empty than continue
maybe, can you show your script ?

[Nosyarg]

One of the forms I've got writing, adds a whole LOT of extra blanks, always at the end of the CSV file,

@Nosyarg but why this happens ? When read source-file , in a loop can use > if row empty than continue
maybe, can you show your script ?

Hey Garry.... Sorry for the sluggish reply. The extra commas were due to a second AHK script that I have writing another piece of the data from pre-loaded DDLs. My code isn't smart enough not to re-write all the possible back every time, so telling it to go in and eliminate the excess after-the-fact is the best solution.

What I'm working on currently has 9 functional scripts, each reading from and writing to the same CSV. Quite a bit of the code is filled with proprietary and confidential data, which my company would disapprove of my copying here. I'm not trying to be obviously cryptic, but unfortunately it's the case.

The issue is resolved, thanks to you and others on the forum. I really appreciate everyone's assistance in helping me finish off this project. Now to the tweaking!!!!