# Create a CSV from other CSVs
**URL:** https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636
**Category:** Automation
**Created:** [September 3, 2022, 6:17pm UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636 "2022-09-03T18:17:22Z")
**Posts on this page:** 20
**Page:** 2
### Author:  [@rctill](https://discourse.devontechnologies.com/u/rctill)
#### Post date: [September 6, 2022, 1:27am UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636/21 "2022-09-06T01:27:30Z")
Thanks Bluefrog,
Could you please post the complete script you propose that includes this code? I’m confused as to how it will work.
I will run the script on my desktop instead, as you suggest.
Thanks for your time,
---
### Author:  [@DTLow](https://discourse.devontechnologies.com/u/DTLow)
#### Post date: [September 6, 2022, 1:41am UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636/22 "2022-09-06T01:41:22Z")
> [@rctill](#):
>
> Could you please post the complete script you propose that includes this code?
I’m working with the multiple .csv files imported into Devonthink
Select the files to be processed and run the script
For each file,
the contents are retrieved, by reading the raw .csv file in the database
and the first line is added to the new .csv file text
After all the files are processed, the new .csv file is created
```auto
set theNewcsvFileText to ""
tell application id "DNtp"
set theSelectedcsvFiles to get selection
repeat with theSelectedcsvFile in theSelectedcsvFiles
set theSelectedcsvName to "\"" & name of the theSelectedcsvFile & "\""
set theSelectedText to paragraphs of (read (path of theSelectedcsvFile as string) as «class utf8»)
set theNewcsvFileText to theNewcsvFileText & theSelectedcsvName & "," & item 1 of theSelectedText & linefeed
end repeat
end tell
set targetFile to (path to desktop as text) & "theNewFile.csv"
set openFile to open for access file targetFile with write permission
write theNewcsvFileText to openFile starting at eof as text
close access file targetFile
```
---
### Author:  [@cgrunenberg](https://discourse.devontechnologies.com/u/cgrunenberg)
#### Post date: [September 6, 2022, 6:38am UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636/23 "2022-09-06T06:38:42Z")
> [@chrillek](#):
>
> After importing it in DT, I get a “Sheet” record (which is fine) with header columns “A”, “B”, and “C”. I would have expected headers “1979”, “Another Room”, and “Reason”. That is exactly what @DTLow reported with their imported CSVs.
On import DEVONthink tries to automatically detect whether there’s a header or not as not all CSV/TSV files have one. However, this might fail and therefore example files would be appreciated, thanks!
---
### Author:  [@chrillek](https://discourse.devontechnologies.com/u/chrillek)
#### Post date: [September 6, 2022, 6:58am UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636/24 "2022-09-06T06:58:29Z")
The sample file was contained verbatim in my post, it contains only two rows.
---
### Author:  [@cgrunenberg](https://discourse.devontechnologies.com/u/cgrunenberg)
#### Post date: [September 6, 2022, 7:04am UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636/25 "2022-09-06T07:04:52Z")
Without any explicit explanation even I wouldn’t know whether this CSV file is supposed to have a header or not 🙂
---
### Author:  [@rmschne](https://discourse.devontechnologies.com/u/rmschne)
#### Post date: [September 6, 2022, 7:38am UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636/26 "2022-09-06T07:38:29Z")
To itch my curiosity, I wrote the small Python script to do as described by the OP @rctill. The basis is that the 30 files are in an indexed folder, run the python script in a macOS Terminal window with the current directory that indexed folder. Output is displayed to the terminal window, and an output.csv file created (but with no CSV headers per the OP)–using headers would make this little script even easier and more standard as a CSV file. For simplicity for me, uses Python pandas which may need to be installed for anyone using this.
Python available standard on all Macs. However, I use Python in a virtual environment which to explain is well beyond scope of this post. See the “interweb” for that.
```auto
#!/usr/bin/env python
# coding: utf-8
import glob
import pandas as pd
output_csv="output.csv"
file_list = glob.glob("*.csv") # get list of all the csv files in the current directory
file_list.sort() # sort in ascending order
with open(output_csv,'w') as f:
for fn in file_list:
if fn != output_csv: # skip the possibly pre-existing output csv file
df = pd.read_csv(fn) # create a dataframe with the file contents
list_of_column_names = list(df.columns) # creating a list of column names by calling the .columns
combine = '"Year'+fn+'",'+','.join(list_of_column_names)
print(combine)
f.write(combine+'\n')
f.close()
```
Attached is the test folder with 30 dummy input files.
[Archive.zip](https://discourse.devontechnologies.com/uploads/short-url/mwYsrx4evAhkMjyxRI8SEo8HJ5r.zip) (21.2 KB)
Note: archive.zip updated.
---
### Author:  [@chrillek](https://discourse.devontechnologies.com/u/chrillek)
#### Post date: [September 6, 2022, 7:58am UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636/27 "2022-09-06T07:58:16Z")
Ok. I was simply assuming that the _first_ row would become the header automagically, since I’m not aware of any special “header markup” for CSV.
What’s the heuristics in DT to figure that out?
---
### Author:  [@chrillek](https://discourse.devontechnologies.com/u/chrillek)
#### Post date: [September 6, 2022, 8:10am UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636/28 "2022-09-06T08:10:12Z")
Cool. Alternatively, on the command line:
```nohighlight
head -1 *.csv | sed -e 's/^==>.*$//' -e '/^$/D' > newCSV.csv
```
---
### Author:  [@cgrunenberg](https://discourse.devontechnologies.com/u/cgrunenberg)
#### Post date: [September 6, 2022, 8:11am UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636/29 "2022-09-06T08:11:52Z")
> [@chrillek](#):
>
> What’s the heuristics in DT to figure that out?
Basically looking for certain keywords or headers created by DEVONthink or whether the first row uses only uppercase as there’s no official marker.
---
### Author:  [@chrillek](https://discourse.devontechnologies.com/u/chrillek)
#### Post date: [September 6, 2022, 8:16am UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636/30 "2022-09-06T08:16:26Z")
I see. That explains a lot, since the OP also (as I did) has all kinds of string in their header - numbers, text in mixed case etc.
- Would it be possible to _modify_ the header from inside DT? I haven’t found a way, but possibly overlooked something.
- Would it be possible to have a (hidden) preference for “always interpret first line as header”?
I guess modifiable headers would be preferable, though.
---
### Author:  [@rmschne](https://discourse.devontechnologies.com/u/rmschne)
#### Post date: [September 6, 2022, 8:18am UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636/31 "2022-09-06T08:18:51Z")
Yes, cool. Brain freeze here, though.
---
### Author:  [@cgrunenberg](https://discourse.devontechnologies.com/u/cgrunenberg)
#### Post date: [September 6, 2022, 8:19am UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636/32 "2022-09-06T08:19:10Z")
> [@chrillek](#):
>
> Would it be possible to _modify_ the header from inside DT?
Isn’t that identical to editing the columns? Or do you have a different “modification” in mind?
---
### Author:  [@chrillek](https://discourse.devontechnologies.com/u/chrillek)
#### Post date: [September 6, 2022, 8:23am UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636/33 "2022-09-06T08:23:30Z")
What I see is this:
When changing to “form view”, I get three rows with editable text fields, labelled “A”, “B”, “C”. Clicking into the column labels in the table view sorts by the corresponding column, and in the form view, I can’t click on the labels.
So, I don’t see how I could change, for example, the header “A” to “1979”. Not seeing the forest for the trees?
---
### Author:  [@cgrunenberg](https://discourse.devontechnologies.com/u/cgrunenberg)
#### Post date: [September 6, 2022, 8:25am UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636/34 "2022-09-06T08:25:47Z")
See _Edit Columns…_ in _Tools \> Document \> Sheets_ or in contextual menu.
---
### Author:  [@chrillek](https://discourse.devontechnologies.com/u/chrillek)
#### Post date: [September 6, 2022, 8:26am UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636/35 "2022-09-06T08:26:28Z")
Well, the forest was called context menu. Thanks.
It’s not overly convenient, but possible. Which makes me think that maybe a (hidden) preference or a “use first row as headers in the next CSV import” might be helpful. Though the latter would, of course, break the usual import process that doesn’t require any interaction.
---
### Author:  [@DTLow](https://discourse.devontechnologies.com/u/DTLow)
#### Post date: [September 6, 2022, 12:08pm UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636/36 "2022-09-06T12:08:46Z")
> [@rmschne](#):
>
> Attached is the test folder with 30 dummy input files.
Thank you for the 30 dummy input files
Sadly Devonthink says none of the files have a header row ☹
There’s no columns function in AppleScript, so I just retrieved the first line of the .csv file
> sort in ascending order
Nice addition
---
### Author:  [@rmschne](https://discourse.devontechnologies.com/u/rmschne)
#### Post date: [September 6, 2022, 12:40pm UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636/37 "2022-09-06T12:40:19Z")
> [@DTLow](#):
>
> Sadly Devonthink says none of the files have a header row
Sadly, IMHO DEVONthink is wrong 😉 if that is what it’s saying to us. I didn’t think to actually import into DEVONthink as it never occurred to me to have to test that. I just imported now, and they are displayed as tables, which is what I would have expected. Beyond that I did not pursue to learn how DEVONthink tells me what the columns are as described in first row.
There is nothing special about a header row. It’s simply declared. By convention, the first line of a file delimited by something (comma, tab, … whatever) is the header row. It doesn’t have to be the first line for when using methods of Pandas, R, and native Python where one is able to declare which row is the header. I’m sure other tools have same capabilities, but I only use these and in my work, I’m reading CSV files and munging into structures to facilitate analysis.
Sorting in descending order also easily to accomplish but I chose not to do that.
---
### Author:  [@cgrunenberg](https://discourse.devontechnologies.com/u/cgrunenberg)
#### Post date: [September 6, 2022, 12:47pm UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636/38 "2022-09-06T12:47:15Z")
> [@rmschne](#):
>
> Attached is the test folder with 30 dummy input files.
> [Archive.zip](https://discourse.devontechnologies.com/uploads/short-url/rSXoLOSxJNm15B2VWUb275yL1jk.zip) (20.9 KB)
Are these mixed quotes actually intented? There are single, double & curly quotes. Sometimes even mixed: `'RID01"`
---
### Author:  [@rmschne](https://discourse.devontechnologies.com/u/rmschne)
#### Post date: [September 6, 2022, 1:09pm UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636/39 "2022-09-06T13:09:23Z")
Typed by hand. Then copy/paste. Did not notice. My input routine wasn’t bothered, so I did not notice. Nor was I reading past the header. I did give some thought to write an algorithm for the computer to inform me of problems like the OP was looking for, but I stopped at that point and went on to other things.
I’ll fix these and edit my upload.
---
### Author:  [@cgrunenberg](https://discourse.devontechnologies.com/u/cgrunenberg)
#### Post date: [September 6, 2022, 1:28pm UTC](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636/40 "2022-09-06T13:28:05Z")
Thanks! Unfortunately the header still uses curly quotes, currently only single/double quotes are supported.
[Previous page](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636.md?page=1)
[Next page](https://discourse.devontechnologies.com/t/create-a-csv-from-other-csvs/72636.md?page=3)