insert data into specific lines of a CSV

Shell Programming and Scripting
1

So I work in a 1 to 1 laptop deployment and sometimes we need to mass order parts. The vendor will send us a text file and we have to manually input serial numbers. Well I have a full blown web based inventory system which I can pull serial number reports from.

I then have to input the part number, CompTIA code, and so forth. I was thinking to myself there has got to be a better way of automating this process.

here is an example of the text:

//RECORD NO.	RECORD TYPE	REPAIR TYPE	SERIAL NO	UNIT RECEIVED DATE	SHIP TO	DIAGNOSED BY(TECH ID)	SYMPTOM	DIAGNOSIS	NOTES	COMPONENT	REQUEST REVIEW BY APPLE(Y/N)	CUSTOMER LAST NAME	CUSTOMER FIRST NAME	CUSTOMER ADDRESS LINE 1	CUSTOMER ADDRESS LINE 2	CUSTOMER ADDRESS LINE 3	COMPANY	COUNTY/SUBURB	CITY	STATE	EMAIL	PRIMARY PHONE	SECONDARY PHONE	COUNTRY CODE	ZIP CODE	ALTERNATE CONTACT	PO NUMBER	CUSTOMER TYPE	CUSTOMER PRIMARY PHONE	COMPTIA CODE	COMPTIA MODIFIER	SP REFERENCE	OSVERSION	RAM	HARDDRIVE	PASSWORD	UPLOADED PROOF OF PURCHASE	DATA TRANSFER REQUESTED(Y/N)	ADDRESS COSMETIC DAMAGE(Y/N)	BOX SHIPMENT	SHIP UNIT TO CUSTOMER	SERVICE PROVIDER CONTACT	SERVICE PROVIDER CONTACT PHONE NUMBER	FAXED PROOF OF PURCHASE	UNIT ABUSED FLAG	LEG 2 CARRIER CODE	LEG 2 TRACKING NO.	IS ACCIDENTAL DAMAGE (Y/N)	Check If OOW COVERAGE(Y/1/X/N)	IS NON RPLENISHMENT	DIAGNOSTIC CODE	OVER RIDE DIAGNOSTIC CODE CHECK	DISPATCH ID	ERROR MESSAGE
//RECORD NO.	RECORD TYPE	ITEM NO	PART NO	ABUSED	COMPTIA	COMPTIA MOD	Repairable Damage	DIAGNOSTIC CODE																																														
1	H	CA	W8738625Z5V	8/17/10	47660	 USA075WB	case splinters bezel cracked	case splinters bezel cracked				Mark 	Hughes	625 Minnesota Ave			USD500 KCKPS		Kansas City	KS	mahughe@kckps.org	913.449.7791		US	66101		7122010																											
1	D		922-9592	N	Z07	A	N																																															
2	H	CA	W87384YYZ5V	8/17/10	47660	 USA075WB	case splinters bezel cracked	case splinters bezel cracked				Mark 	Hughes	625 Minnesota Ave			USD500 KCKPS		Kansas City	KS	mahughe@kckps.org	913.449.7791		US	66101		7122010																											
2	D		922-9592	N	Z07	A	N																																															
3	H	CA	W87382D5Z5V	8/17/10	47660	 USA075WB	case splinters bezel cracked	case splinters bezel cracked				Mark 	Hughes	625 Minnesota Ave			USD500 KCKPS		Kansas City	KS	mahughe@kckps.org	913.449.7791		US	66101		7122010																											
3	D		922-9592	N	Z07	A	N																																															
4	H	CA	W87393D8Z5V	8/17/10	47660	 USA075WB	case splinters bezel cracked	case splinters bezel cracked				Mark 	Hughes	625 Minnesota Ave			USD500 KCKPS		Kansas City	KS	mahughe@kckps.org	913.449.7791		US	66101		7122010																											
4	D		922-9592	N	Z07	A	N																																															
5	H	CA	W8737B6TZ5V	8/17/10	47660	 USA075WB	case splinters bezel cracked	case splinters bezel cracked				Mark 	Hughes	625 Minnesota Ave			USD500 KCKPS		Kansas City	KS	mahughe@kckps.org	913.449.7791		US	66101		7122010																											
5	D		922-9592	N	Z07	A	N																																															
6	H	CA	W87393U9Z5V	8/17/10	47660	 USA075WB	case splinters bezel cracked	case splinters bezel cracked				Mark 	Hughes	625 Minnesota Ave			USD500 KCKPS		Kansas City	KS	mahughe@kckps.org	913.449.7791		US	66101		7122010																											
6	D		922-9592	N	Z07	A	N																				
7	H	CA	W8739B7ZZ5V	8/17/10	47660	 USA075WB	case splinters bezel cracked	case splinters bezel cracked				Mark 	Hughes	625 Minnesota Ave			USD500 KCKPS		Kansas City	KS	mahughe@kckps.org	913.449.7791		US	66101		7122010
7	D		922-9592	N	Z07	A	N																				
8	H	CA	W87379M3Z5V	8/17/10	47660	 USA075WB	case splinters bezel cracked	case splinters bezel cracked				Mark 	Hughes	625 Minnesota Ave			USD500 KCKPS		Kansas City	KS	mahughe@kckps.org	913.449.7791		US	66101		7122010

If I can get all the proper data/strings in a list, how would I got about inserting the data in the proper area? I figure I could hard code a script where I can just plug in variables. Like part number, list of serials, failure code, etc and then let it generate the long report for me.

To give you an idea we ordered 1200 of the same part over the summer and due to lack of time of trying to figure this out we just did it manually.

Any ideas?

Thanks

2

This is not a CSV file incidentally, CSV is "comma-seperated". But what are you trying to substitute, where? What should it look like in the end, based on what data?

3

sorry it is plain text....i just open it up in excel to modify it so I had the mindset it was a CSV file...

These are the data strings I need to insert

W8738625Z5V is the serial number

922-9592 would be the part number

USA075WB would be the tech ID of the person creating the order

Then the rest is just info about contacting us That stuff can just be mass copied and pasted. However, I would like to plug in some variables and strings to populate the serial numbers. I can run a report of serial numbers form our back end inventory management software, and I can just copy/paste or source them as plain text. It should look just like the above example. Then I upload that text file to the vendor's web based parts ordering system and it either parses the data and OK's it, or rejects it.

4

I'm guessing the file is tab-separated? This should replace the fields you wanted all with the same serial, part, and tech...

#!/bin/bash

# Assume input for 'read' is tab-seperated
IFS=$'\t'

while read -a ARRAY
do
        # Print and skip comment lines
        if [[ "${ARRAY[0]:0:2}" == "//" ]]
        then
                echo "${ARRAY[*]}"
                continue
        fi

        # Modify different fields based on the type given
        if [[ "${ARRAY[1]}" == "H" ]]
        then
                ARRAY[7]="USA075WB"
                ARRAY[3]="W8738625Z5V"
        elif [[ "${ARRAY[1]}" == "D" ]]
        then
                ARRAY[3]="922-9592"
        fi

        # Write the entire array back out, still tab-seperated
        echo "${ARRAY[*]}"
done < file.in > file.out
1 Like
5

I will give this a try thank you very much for your assistance.