Solved How to use Command Prompt to Edit CSV File

September 30, 2019 at 14:49:40
Specs: Windows 10
I would like to

1) Delete the first three lines of a CSV file
2) Replace the new 1st header row (was the 4th row) with new headers on each of the columns.
3) Delete Columns 1, 5, 6, 7, 8, and 25.

I've tried some commands on own, including:

cat C14bOrigHdrs.csv | sed “1 d” > remove-row1.csv

cat remove-row1.csv | sed “1 d” > remove-row2.csv

cat remove-row2.csv | sed “1 d” > remove-row3.csv

sed -i '1s/.*/MRN & Pt Name,DOB,Pt Name,Txn Date,Txn Time,Item Code,Item Description,Order/RX#,User,Dept,AttendDocName,Description,Txn Type,NotesFromIssue,Issue Dose,Waste,UOM,Machine,Bin,Cost/Charge,Item Description - Total per Item per Patient (TPIPP),Net Disp (TPIPP),Net Waste (TPIPP),UOM,Net Charge (TPIPP),Delete,Delete,Delete,Delete,Delete,Delete,Delete,Delete/' newheaders.csv

cut -d, -f 1–7,13,15-17,20,22-25 newheaders.csv > NeedToRemoveDuplicates.csv

Unfortuantely, I get an error message right away saying "'cat' is not recognized as an internal or external command, operable program or batch file."

Any advice on what I'm doing wrong?

Thanks!


See More: How to use Command Prompt to Edit CSV File

Reply ↓  Report •

✔ Best Answer
October 8, 2019 at 01:00:25
Ok, here's a stab:
:: ====begin prototype: output is in curr.dir as "testout.csv". Input obviously "test.csv"
@echo off & setlocal
>testout.csv echo PT#,DOB,PT Name,Txn Date,Txn Time,Drug Name,Quantity
for /f "skip=4 tokens=1-5,7,9 delims=," %%a in (test.csv) do echo %%a,%%b,%%c,%%d,%%e,%%f,%%g>>testout.csv
::-------- end script

:: here is output from your sample:
PT#,DOB,PT Name,Txn Date,Txn Time,Drug Name,Quantity
"Pt: 111",01/01/1950,John Doe,10/01/2019,01:00:00 AM,DRUGX,-2
"Pt: 111",01/01/1950,John Doe,10/01/2019,02:00:00 AM,DRUGX,-1
"Pt: 111",01/01/1950,John Doe,10/01/2019,03:00:00 AM,DRUGY,-4
"Pt: 222",02/02/1960,Jane Doe,10/01/2019,04:00:00 AM,DRUGZ,-5



#1
September 30, 2019 at 19:32:33
Well, 'cat' is unix/xenix platform, not windows, (unless sourceforge has developed a windows-based sim-utility). Same goes for 'sed' and 'cut'. There may be win-sims for all of these - (sourceforge ROCKS). But that requires installing those sims, or a unix "box" running under windows. If you cast your lot with windows (god forbid!), then come back with a dummy csv to illustrate your moves. My unix is very rusty and you're probly much better advised by unix specialists. Another option is to use powershell, which I'm sure can also solve the problem with less overhead than batch.

message edited by nbrane


Reply ↓  Report •

#2
September 30, 2019 at 20:51:39
I'm sorry. I guess I'm not that well versed to know that the commands I was trying were not Windows commands.

Do you have any suggestions for how I can go about using Windows Command Prompt (if it is even capable) to edit a CSV file? Or better yet, how to create a batch file for that purpose?

Also, any suggestions as to where I could go to learn about some of the available Windows command prompt commands related to CSV editing?

message edited by bassmaster_1000


Reply ↓  Report •

#3
September 30, 2019 at 21:49:45
No problem. Post some "dummy" csv material for us to practice on, observing consistent format of the csv source. There are no specific commands for CSV editing in windows, just batch or powershell. Each approach requires some "learning curve" and the return is worth the investment. CSV, which is text, can be manipulated using batchscript according to standard text handling as long as certain characters are handled "with gloves": & > < | ^
Usually this is not a problem, but all depends on your source-file content. and what you want to do with it.
Post sample material "before" and "after".

Reply ↓  Report •

Related Solutions

#4
October 7, 2019 at 09:55:20
Textbox1,Textbox2,Textbox3,Textbox9,Textbox11,Textbox13
Organization Name,10/07/2019,09:04:27 AM,10/1/2019 - 10/7/2019, - , -

Textbox196,Textbox200,Textbox202,MyDate,MyTime,Code,Description1,ProfileID,Qnty
"Pt: 111",01/01/1950,John Doe,10/01/2019,01:00:00 AM,CBX1,DRUGX,OVR,-2
"Pt: 111",01/01/1950,John Doe,10/01/2019,02:00:00 AM,CBX1,DRUGX,OVR,-1
"Pt: 111",01/01/1950,John Doe,10/01/2019,03:00:00 AM,CBX2,DRUGY,55555555,-4
"Pt: 222",02/02/1960,Jane Doe,10/01/2019,04:00:00 AM,CBX3,DRUGZ,OVR,-5

That would be a mock example of the first 8 lines of my CSV.
I want to write a batch file that will:
- Delete the first 3 rows, then
- Delete column 6 (Code) and 8 (ProfileID), then
- Replace the (now) first row with the following column headers
- PT#,DOB,PT Name,Txn Date,Txn Time,Drug Name,Quantity
- Generate a new CSV with a new file name.

As a reminder, I'm running an out of the box Windows 10 PC. I would like to try to do these edits with basic, from Windows tools, like a Batch File or something, if possible. That being said, if I NEED to to install something else to make this happen, please let me know.

Hope that makes sense. Let me know if anybody has any questions.

Thanks!!!


message edited by bassmaster_1000


Reply ↓  Report •

#5
October 8, 2019 at 01:00:25
✔ Best Answer
Ok, here's a stab:
:: ====begin prototype: output is in curr.dir as "testout.csv". Input obviously "test.csv"
@echo off & setlocal
>testout.csv echo PT#,DOB,PT Name,Txn Date,Txn Time,Drug Name,Quantity
for /f "skip=4 tokens=1-5,7,9 delims=," %%a in (test.csv) do echo %%a,%%b,%%c,%%d,%%e,%%f,%%g>>testout.csv
::-------- end script

:: here is output from your sample:
PT#,DOB,PT Name,Txn Date,Txn Time,Drug Name,Quantity
"Pt: 111",01/01/1950,John Doe,10/01/2019,01:00:00 AM,DRUGX,-2
"Pt: 111",01/01/1950,John Doe,10/01/2019,02:00:00 AM,DRUGX,-1
"Pt: 111",01/01/1950,John Doe,10/01/2019,03:00:00 AM,DRUGY,-4
"Pt: 222",02/02/1960,Jane Doe,10/01/2019,04:00:00 AM,DRUGZ,-5


Reply ↓  Report •

#6
October 14, 2019 at 13:37:04
Is there a way I can get the operations to all show up on the Command Line as it is processing? When I have run batches in the Command Prompt before (i.e. robocopy), the window has always shown a list of all the files that were copied from one folder to another.

Also, is there a way that after performing the operations on all rows of the CSV, and showing those operations, that the screen will tell me when the batch file is done running?


Reply ↓  Report •

#7
October 14, 2019 at 18:05:40
Well, the cop-out solution would be to not set echo off, but the result can be very confusing because both the "command" and the command result are displayed. Useful for debugging, but not much for logging.
Here's the above with basically a "tee", echoing file-output to console as well.

:: ====begin prototype: output is in curr.dir as "testout.csv". Input obviously "test.csv"
@echo off & setlocal
>testout.csv echo PT#,DOB,PT Name,Txn Date,Txn Time,Drug Name,Quantity
echo PT#,DOB,PT Name,Txn Date,Txn Time,Drug Name,Quantity
for /f "skip=4 tokens=1-5,7,9 delims=," %%a in (test.csv) do (
echo %%a,%%b,%%c,%%d,%%e,%%f,%%g>>testout.csv
echo %%a,%%b,%%c,%%d,%%e,%%f,%%g
)
echo Done
:: end script

If there's more to the story, you'll need to post your current code and some sample of desired logging-output.

ps: added "pre", not tested:
for /f "skip=4 tokens=*" %%z in (test.csv) do (
echo pre: %%z
for /f "tokens=1-5,7,9 delims=," %%a in ("%%z") do (
echo %%a,%%b,%%c,%%d,%%e,%%f,%%g>>testout.csv
echo post: %%a,%%b,%%c,%%d,%%e,%%f,%%g
)
)
echo Done: test.csv TO testout.csv

message edited by nbrane


Reply ↓  Report •

#8
October 14, 2019 at 19:04:03
That looks great. The only other thing is this...

Is there a way to use a batch file to remove all duplicate rows from a file for which column 1 and column 7 are the same in multiple rows? Like in the example below, where Pt 111 and Drug X are on two rows. The last column of each of those two rows is auto calculated by a canned report to be the Net quantity per drug per patient. If I wanted the report to remove all subsequent duplicate rows, i.e. Remove row 2 of 4 because it is the first line that is a duplicate containing Pt 111 and Drug X... Is that possible?


Textbox1,Textbox2,Textbox3,Textbox9,Textbox11,Textbox13,Textbox15
Organization Name,10/07/2019,09:04:27 AM,10/1/2019 - 10/7/2019, - , -
Textbox196,Textbox200,Textbox202,MyDate,MyTime,Code,Description1,ProfileID,Qnty,NetQntyPerDrug
"Pt: 111",01/01/1950,John Doe,10/01/2019,01:00:00 AM,CBX1,DRUGX,OVR,-2,-3
"Pt: 111",01/01/1950,John Doe,10/01/2019,02:00:00 AM,CBX1,DRUGX,OVR,-1,-3
"Pt: 111",01/01/1950,John Doe,10/01/2019,03:00:00 AM,CBX2,DRUGY,55555555,-4,-4
"Pt: 222",02/02/1960,Jane Doe,10/01/2019,04:00:00 AM,CBX3,DRUGZ,OVR,-5,-4


Reply ↓  Report •

#9
October 14, 2019 at 21:19:43
Maybe:
@echo off & setlocal enabledelayedexpansion
>testout.csv echo PT#,DOB,PT Name,Txn Date,Txn Time,Drug Name,Quantity
set prev=_
for /f "skip=4 tokens=1-5,7,9 delims=," %%a in (test.csv) do (
if %%a%%f neq !prev! (
>>testout.csv echo %%a,%%b,%%c,%%d,%%e,%%f,%%g
echo %%a,%%b,%%c,%%d,%%e,%%f,%%g
set prev=%%a%%f
) else (
echo skipping: %%a,%%b,%%c,%%d,%%e,%%f,%%g
)
)

Reply ↓  Report •

Ask Question