Modify CSV File using batch file

[ITGURU]

I'm running an automated task to export a csv report from SIMS.net. I then need to add another column with a heading, and then populate all other rows with the word student.

I've created a script which adds the word student to all rows, but it is also doing it for the header row.

Can anyone please assist how i get a header row and then the word student on all other rows?

 

Thanks

script is below:

 

This is adding 2 columns to the csv file with examples, newsecond and newfith

 

 

for /f "tokens=1-4,* delims=," %%a in (c:\Timetable.csv) do ( >>c:\newcsv.csv echo %%a,NewSecond,%%b,%%c,%%d,NewFifth,%%e)

[Arthur]

I think this might do what you need, but it's PowerShell.

 

Import-Csv .\Timetable.csv | 
Select-Object *,@{Name='NewSecond';Expression={'Student'}} | 
Export-Csv .\NewTimetable.csv -NoTypeInformation

[ITGURU]

Perfect thanks, that'll do nicely!

 

is there a way to reorder the columns, say put the student column as column 3?

 

Thanks

 

I think this might do what you need, but it's PowerShell.

 

Import-Csv .\Timetable.csv | 
Select-Object *,@{Name='NewSecond';Expression={'Student'}} | 
Export-Csv .\NewTimetable.csv -NoTypeInformation

[Arthur]

is there a way to reorder the columns, say put the student column as column 3?

Replace the asterisk in Select-Object with the actual names of your columns. This allows you to reorder them however you want.

 

Import-Csv .\Timetable.csv | 
Select-Object [color="#FF0000"]Column1[/color], [color="#FF0000"]Column2[/color], @{Name='NewSecond';Expression={'Student'}}, [color="#FF0000"]Column4[/color], [color="#FF0000"]Column5[/color] | 
Export-Csv .\NewTimetable.csv -NoTypeInformation

Edited May 7, 2019 by Arthur

[ITGURU]

I've noticed as well that when i run command reporter from powershell it's stripping the 00 from students admission numbers when it creates the csv export from the report.

 

 

Replace the asterisk in Select-Object with the actual names of your columns. This allows you to reorder them however you want.

 

Import-Csv .\Timetable.csv | 
Select-Object [color=#FF0000]Column1[/color], [color=#FF0000]Column2[/color], @{Name='NewSecond';Expression={'Student'}}, [color=#FF0000]Column4[/color], [color=#FF0000]Column5[/color] | 
Export-Csv .\NewTimetable.csv -NoTypeInformation

[Knil92]

That will be to do with the excel template that it uses, you will need to format the cells so that it doesnt get rid of the 00 in front of the admission numbers

[ITGURU]

If i preview the report within SIMS or run the report from within SIMS i get the 00 in front of the admissions number. if i run the report via commandreporter the 00's are removed!

 

That will be to do with the excel template that it uses, you will need to format the cells so that it doesnt get rid of the 00 in front of the admission numbers

[Knil92]

If i preview the report within SIMS or run the report from within SIMS i get the 00 in front of the admissions number. if i run the report via commandreporter the 00's are removed!

 

Are you opening the csv file in excel? I've just tried making a report that exports to a csv file, and when I open it in excel it takes the 00 away, but if i right click and open with notepad its got the 00 in there, so it will be an issue with excel.

[ITGURU]

Are you opening the csv file in excel? I've just tried making a report that exports to a csv file, and when I open it in excel it takes the 00 away, but if i right click and open with notepad its got the 00 in there, so it will be an issue with excel.

 

So it does! weird that! When i ran command reporter from cmd prompt and open in excel the 00's appear!

[Knil92]

I think because excel opens it and tries its best to format the data the way it thinks it should be, so it ends up taking the 00 out, kind of like if you open excel and put a phone number in with the area code starting with a 0 it will take it out when you press enter.