Append row into a CSV file on SFTP

In this post I am going to show how we can append a row into CSV file using simple bash command.

To demonstrate this I am using Linux virtual machine, the OS is on Mint 18.3 64bit.  First me create a sample file on my Linux Machine as like below.

dev@dev-VirtualBox ~/Desktop/CSV $ pwd
/home/dev/Desktop/CSV
dev@dev-VirtualBox ~/Desktop/CSV $ ls
dev@dev-VirtualBox ~/Desktop/CSV $ cat <<EOF >sample.csv
> COL1,COL2,COL3
> 1,FIRST_LINE,18-JAN-2018
> EOF
dev@dev-VirtualBox ~/Desktop/CSV $ ls
sample.csv
dev@dev-VirtualBox ~/Desktop/CSV $

Once the file is created it will look something like below:

image

Now let us think that this file is on a SFTP server where we need to connect and append another line without renaming or changing the file format.

Login to the server


login as: dev
dev@10.0.0.203's password:
Welcome to Linux Mint 18.3 Sylvia (GNU/Linux 4.10.0-38-generic x86_64)

* Documentation:  <a href="https://www.linuxmint.com/">https://www.linuxmint.com</a>
Last login: Tue Jan 23 09:55:52 2018 from 10.0.0.203
dev@dev-VirtualBox ~ $ ls
Desktop  Documents  Downloads  Music  Pictures  Public  Templates  Videos
dev@dev-VirtualBox ~ $ cd Desktop/
dev@dev-VirtualBox ~/Desktop $ ls
CSV
dev@dev-VirtualBox ~/Desktop $ cd CSV/
dev@dev-VirtualBox ~/Desktop/CSV $ ls
sample.csv

So now we can login to the server.

Method 1: Now let us execute the below line to insert or append a new row to the existing CSV file i.e. sample.csv:


dev@dev-VirtualBox ~/Desktop/CSV $ sed -i.bak 1i"2,Second_Line,18-JAN-2018" sample.csv
dev@dev-VirtualBox ~/Desktop/CSV $

Once the above line is executed we will see the row is inserted but not at the end but at the beginning.

image

Now how we can append the line at the end .


dev@dev-VirtualBox ~/Desktop/CSV $ pwd
/home/dev/Desktop/CSV
dev@dev-VirtualBox ~/Desktop/CSV $ ls
sample.csv
dev@dev-VirtualBox ~/Desktop/CSV $ cat sample.csv
COL1,COL2,COL3
1,FIRST_LINE,18-JAN-2018
dev@dev-VirtualBox ~/Desktop/CSV $ sed -i -e "\$a2,Second_Line,18-JAN-2018" sample.csv
dev@dev-VirtualBox ~/Desktop/CSV $ cat sample.csv
COL1,COL2,COL3
1,FIRST_LINE,18-JAN-2018
2,Second_Line,18-JAN-2018
dev@dev-VirtualBox ~/Desktop/CSV $

 

The line used to insert the row/ append the row at the end is :

sed -i -e "\$a2,Second_Line,18-JAN-2018" sample.csv

Method 2: Now let us check how we can use echo to insert the line at the end.


dev@dev-VirtualBox ~/Desktop/CSV $ echo "3,Third_Line,18-JAN-2018" >> sample.csvdev@dev-VirtualBox ~/Desktop/CSV $ cat sample.csv
COL1,COL2,COL3
1,FIRST_LINE,18-JAN-2018
2,Second_Line,18-JAN-2018
3,Third_Line,18-JAN-2018
dev@dev-VirtualBox ~/Desktop/CSV $

Method 3: Append using a simple python programming


dev@dev-VirtualBox ~/Desktop/CSV $ python
Python 2.7.12 (default, Nov 19 2016, 06:48:10)
[GCC 5.4.0 20160609] on linux2
Type "help", "copyright", "credits" or "license" for more information.
>>> with open('sample.csv', 'a') as file:
...     file.write('4,Fourth_Line,18-JAN-2018')
... ;
   File "<stdin>", line 3
     ;
     ^
SyntaxError: invalid syntax
>>> with open('sample.csv', 'a') as file:
...     file.write('4,Fourth_Line,18-JAN-2018\n');
...
>>> exit()

Finally, the file will look like as below:


dev@dev-VirtualBox ~/Desktop/CSV $ cat sample.csv
COL1,COL2,COL3
1,FIRST_LINE,18-JAN-2018
2,Second_Line,18-JAN-2018
3,Third_Line,18-JAN-2018
4,Fourth_Line,18-JAN-2018

dev@dev-VirtualBox ~/Desktop/CSV $



 

image

 

To automate the process you can create a script and try to pass the row as a input to the file while calling the script.

 

Thank you for reading my blog , please feel free to leave me some feedback or to suggest any future topics.

Looking forward to hear from you – Swadhin Ray (Sloba) -( LinkedIn ) ( Twitter )

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s