Extract information from csv to multiple files

Question

I need some help to extract and to separate strings of information from a CSV file, and subsequently printing it multiple new files.

I have huge files with a ton of information I need separated. The code needs to group all the info from say Alabama into a new file, and the information from Oklahoma in a separate file. Is this possible in a single step with Bash (.sh)?

1 Sample; Region; Name; Result;
2 "99"; "Alabama", "Joe"; "Ill"
3 "100"; "Alabama", "Mary", "Ill"
4 "101", "Oklahoma", "Joseph", "Ill"

In this case, it would write 2 output files, one containing all the lines for Alabama and the other for Oklahoma. Would it be further possible to send these files to be encrypted with 7zip?

Welcome to the site. Please ensure that the example content is accurate. Currently you seem to have a mix of `;` and `,` as field separators (and are the spaces behind the separators actually there?). Also, I assume that the leading line numbers are for illustration purposes only, and not present in the actual input. In addition, please add the following: Desired output (with or without header, with or without quotes), and what you already tried and where you ran into problems. — AdminBee, Aug 30 '21 at 11:42
See [Extract data from a file and place in different files based on one column value](https://unix.stackexchange.com/questions/114061/extract-data-from-a-file-and-place-in-different-files-based-on-one-column-value) — steeldriver, Aug 30 '21 at 12:12
Does this answer your question? [Extract data from a file and place in different files based on one column value](https://unix.stackexchange.com/questions/114061/extract-data-from-a-file-and-place-in-different-files-based-on-one-column-value) — AdminBee, Aug 30 '21 at 12:31
Your example has only two different files with fixed names. For an answer, it would be good to know, whether real world files could have any possible name there, because no one would use that first `awk` answer, if you'd need to code 50 state names by hand. — Philippos, Aug 30 '21 at 12:50

sseLtaH · Accepted Answer · 2021-08-31T06:40:29.093

Something like this should work.

You can use awk to seperate and create the files and then p7zip for encryption after.

for f in file; do
    awk ' /Alabama/ { print > "Alamaba.txt" } /Oklahoma/ { print > "Oklahoma.txt" }' $f
    7z a -p -mx=9 -mhe -t7z states.7z Alabama.txt Oklahoma.txt
    #7z a -p -mx=9 -mhe -t7z Alabama.7z Alabama.txt
    #7z a -p -mx=9 -mhe -t7z Oklahoma.7z Oklahoma.txt
done

awk will match and print directly to the output file.

p7zip will create an encrypted 7z file in this case called states.7z

-a: Add files to archive

-p: Prompt for a password

-mx=9: Compression level

-mhe: Encrypt file names

-t7z: Generate a 7z archive

To restore the data, use:

7za e states.7z

Output

cat Alamaba.txt
2 "99"; "Alabama", "Joe"; "Ill"
3 "100"; "Alabama", "Mary", "Ill"

cat Oklahoma.txt
4 "101", "Oklahoma", "Joseph", "Ill"

Thank you very much, this works perfectly for our data. Is it possible to modify this script in order to get Alabama and Oklahoma in 2 separate 7zip files? — nermze, Aug 31 '21 at 06:30
@artur Sure. Please check the edit, I have added it as comments in the code. — sseLtaH, Aug 31 '21 at 06:41

score 0 · Answer 2 · answered Aug 30 '21 at 12:21

Well, assuming all the fields are separated with a single space you can try this:

while read SAMPLE REGION REST; do OUT=$(sed s/'[",;]'//g <<<$REGION);echo "$SAMPLE $REGION $REST" >>$OUT.csv; done < all.csv

If there is no space between the fields, you can change the internal field separator by using IFS= as folowing example:

while IFS="," read SAMPLE REGION REST; do OUT=$(sed s/'[",;]'//g <<<$REGION);echo "$SAMPLE $REGION $REST" >>$OUT.csv; done < all.csv

The head line, which is falls into a separated file you must copy yourself.

Extract information from csv to multiple files

2 Answers2