Appending all csv files in a folder

Questions & Suggestions
1

I have a folder with 100 csv files. These need to be merge to one file.

Each files contents will be like this:

Header1,Header2,Header3
data1,data2,data3
data4,data5,data6
data7,data8,data9

So, the headers are always the same but the data varies.

I need to append all these files so they become one big csv file! I understand that Terminal has a Cat command or something like that? I am a bit worry about using it as if I use a * command and get it wrong, I may ruin my Mac if the folder is wrongly addressed! But also, that leaves the issue of getting rid of the duplicate headers.

So, given the above, how would you cycle through each file, appending one to the other but removing the duplicate headers?

Thanks,

Jon

2

Hey Jon,

Install the Satimage.osax, and I can probably do that in 3-5 lines of AppleScript.

-Chris

3

Hey Jon,

Providing for the possibility of some formatting errors took 13 lines.

------------------------------------------------------------
# AppleScript
# REQUIRES the Satimage.osax
------------------------------------------------------------
set cvsFolder to alias ((path to home folder as text) & "test_directory:Jon_Test_Folder:")
set fileList to glob "*.cvs" from cvsFolder as alias
set cvsText to {}
repeat with i in fileList
  set end of cvsText to readtext i
end repeat
set cvsText to join cvsText using linefeed
set cvsText to change "^[[:blank:]]*$[\\n\\r]*" into "" in cvsText with regexp
set cvsText to change "\\A\\s+|\\s+\\Z" into "" in cvsText with regexp
set headerText to paragraph 1 of cvsText
set headerEndPosition to (matchLen of (find text "\\A" & headerText in cvsText with regexp)) + 1
set cvsText to change headerText & "[\\r\\n]+" into "" in cvsText starting at headerEndPosition with regexp without case sensitive
writetext cvsText to file ((cvsFolder as text) & "ConsolidatedCvsData.cvs")
------------------------------------------------------------

The Satimage.osax will deal with any UTF8 characters better than the shell, but here’s a working shell script:

#!/usr/bin/env bash
cvsDir=~/'test_directory/Jon_Test_Folder/';
cd "$cvsDir";
cvsText=$(cat *);
headerText=$(sed -n '1p' <<< "$cvsText");
cvsText="$headerText\n"$(grep -v "$headerText" <<< "$cvsText");
echo -e "$cvsText" > consolidatedCvsText.cvs;

-Chris

1 Like
4

Oh wow Chris, thanks for that! I have to confess I am unsure which is which. Is the first bit of code Satimage and the second stuff I would do in Terminal?

I can see what looks like RegExp in there, although I haven’t gotten my head around to understanding it. It can be nested and complicated. The method I was thinking was to just rip out the first line of each text file. Maybe that is what the code does anyway?

5

Hey Jon,

I've edited that last post to make it more clear.

The AppleScript does as follows:

  1. Sets a variable to your cvs file folder.
  • Gets all the files.
  • Reads all of them into a variable.
  • Removes any blank lines.
  • Removes any vertical leading or trailing whitespace.
  • Get's the header text.
  • Removes all header texts but the first one.
  • Writes the new file.

The Bash (shell) script does as follows:

  1. Sets a variable to your cvs file directory.
  • Sets the current working directory to that directory.
  • Reads all the files into a variable.
  • Grabs the first header-text line into a variable.
  • Removes all header-text from the text body and then concatenates header-text with non-header-text.
  • Writes the new file.

-Chris

6

cat myfiles*.csv >~/big.csv
bbedit ~/big.csv
Select a header line, Command-E, Search & Replace and remove them all.

Unless you have to do this process more than once, using a macro is overkill.

7

Should work with TextWrangler also, which is the free, less-powerful, version of BBEdit.

8

Hey guys, some great additional tips there. Actually, I prefer the Search and Replace method only because a) I am not familiar with RegEx and b) it was what I was thinking in the first place. I have TextWrangler installed too. I think with the combination of Chris’s code and the Search and Replace, I would get something that works and that I fully understand too.

Or maybe not. I can’t decide! I will play with Chris’s code first to see what happens!

As a side note, I really didn’t like the Mac before. But having used it more and more recently through KM, I am starting to see how powerful some aspects of it are. e.g. Applescript, Terminal, KM, etc. I’m feeling more love for it!

9

Just tried Chris’s shell code and it runs beautifully! I have my script doing some useful stuff now. :smile:

I don’t think I am that far off getting my whole thing working now. Step by step, making progress.

1 Like