insert data into specific lines of a CSV


 
Thread Tools Search this Thread
Top Forums Shell Programming and Scripting insert data into specific lines of a CSV
# 1  
Old 08-26-2010
insert data into specific lines of a CSV

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:
Code:
//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

Last edited by tlarkin; 08-26-2010 at 01:18 PM..
# 2  
Old 08-26-2010
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  
Old 08-26-2010
Quote:
Originally Posted by Corona688
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?
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.

Last edited by tlarkin; 08-26-2010 at 01:26 PM..
# 4  
Old 08-26-2010
I'm guessing the file is tab-separated? This should replace the fields you wanted all with the same serial, part, and tech...
Code:
#!/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

This User Gave Thanks to Corona688 For This Post:
# 5  
Old 08-26-2010
I will give this a try thank you very much for your assistance.
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. UNIX for Beginners Questions & Answers

Possible to insert a few lines of code into a file at a specific point?

Hi Folks - How would I go about inserting a chunk of lines (3) into a specific portion of a file? The background is I have a script (non shell) that it executed daily, however on Sundays, I uncomment a section of code so that piece can be run as well. So I was hoping to write a piece of... (9 Replies)
Discussion started by: SIMMS7400
9 Replies

2. Shell Programming and Scripting

Script to ingest a csv, validate data and insert into Oracle

Hi all i would appreciate your help... I am looking for a set of unix commands which i can use to 1) ingest a csv file with a known format 2) validate the filename 3) validate the data/datatypes 4) Insert into an oracle db Can you help get me started? yogz888 (1 Reply)
Discussion started by: yogz888
1 Replies

3. Shell Programming and Scripting

Insert charactera in 1st position of specific lines using vi editor or sed command

Dear all, i am having text file like below surya rama ranga laxman rajesh reddy i want add string (OK) before a text from line 3 to 5 the result will be surya rama OK ranga OK laxman OK rajesh reddy (1 Reply)
Discussion started by: suryanarayana
1 Replies

4. Shell Programming and Scripting

Insert text line to specific location CSV

In Perl. ***edited question below*** Hey all, I am teaching myself some simple CSV file manipulation and have become a little stuck. Say I have the following layout in the CSV file: age,name,locationIs it possible to INSERT data into the CSV into the correct age order. For example, if I had... (1 Reply)
Discussion started by: whyte_rhyno
1 Replies

5. Shell Programming and Scripting

CSV to SQL insert: Awk for strings with multiple lines in csv

Hi Fellows, I have been struggling to fix an issue in csv records to compose sql statements and have been really losing sleep over it. Here is the problem: I have csv files in the following pipe-delimited format: Column1|Column2|Column3|Column4|NEWLINE Address Type|some descriptive... (4 Replies)
Discussion started by: khayal
4 Replies

6. Shell Programming and Scripting

Extracting specific lines of data from a file and related lines of data based on a grep value range?

Hi, I have one file, say file 1, that has data like below where 19900107 is the date, 19900107 12 144 129 0.7380047 19900108 12 168 129 0.3149017 19900109 12 192 129 3.2766666E-02 ... (3 Replies)
Discussion started by: Wynner
3 Replies

7. Shell Programming and Scripting

Can sed be used to insert data at specific column?

I'm trying to use sed to insert data at a specific column, let's say my data looks like this: 0553 1828 0552 1829 0550 1829 0549 1830 0548 1831 what I want is this: timein 0553 timeout 1828 timein 0552 timeout 1829 timein 0550 timeout 1829 timein 0549 timeout 1830 timein 0548... (5 Replies)
Discussion started by: mswartz
5 Replies

8. Homework & Coursework Questions

Displaying specific lines from a CSV file

1. The problem statement, all variables and given/known data: Display from a csv file, birthdays that occur today. If there are no birthdays today, the next one in the year. 2. Relevant commands, code, scripts, algorithms: The csv file is ordered from older to younger (ie. the most recent... (8 Replies)
Discussion started by: Adzi
8 Replies

9. Shell Programming and Scripting

Insert 2 lines in a file at a specific location

Hi, I need to insert two new lines in a file: The file: "..... ...... ULIMIT_MAX_FILES="ulimit -S -n `ulimit -H -n`" .... .... " I need to add the lines: LD_LIBRARY_PATH='$LD_LIBRARY_PATH:$APACHE_HOME/modules' DOWNLOADMODULE_CONF_PATHNAME='$APACHE_HOME/conf/DWLModule.cfg' right... (2 Replies)
Discussion started by: potro
2 Replies

10. Shell Programming and Scripting

Insert lines at specific location in file

Hi There I have this file that I would like to add entries to, however, there is a "}" as the last line that I need to keep. Basically i would like to know how I can write a script that will add new lines at the second to last line position (ie always add new line above the close bracket) ... (17 Replies)
Discussion started by: hcclnoodles
17 Replies
Login or Register to Ask a Question