Create a CSV from other CSVs

I want a copy of the header (first row) of about 50 .csv files to identify some of the non-compliant files.

Ideally, it would be great to automatically develop a .csv or an excel sheet that lists the filename in the first column, followed by the 26 header columns names of the .csv files. This way, I can determine the years the creators of the tables modified the structure. Doing this without automation will be pure drudgery.

Any thoughts on how to do this from within Devonthink?

I would use an Applescript, creating the .csv file
Not clear on the format for the “26 header columns names”; samples might help

What non-compliant files ?

Yes, please clarify what you’re actually after and include an example file, if possible.

I have 30 CSV (Comma Separated Values) files. Each table of values in each file represents a year’s worth of information. The first line of each file is a header containing the column names (not numerical values).

Reading the first line of each file and comparing all 30 of them would allow me to compare the column names and find the years where someone might have added, removed, or renamed a column.

Is there a quick way to create a csv that takes the first line of each of these files and puts them in its own table?

The output table would look like this:

“Year 01”, “R Id”,“Empl Id”,“Instructor”,“Course Identifier”,“Section Code”,“Course Title”
“Year 02”, “R Id”,“Empl Id”,“Instructor”,“Course Identifier”,“Section Code”,“Course Title”
“Year 03”, “R Id”,“Empl Id”,“Instructor”,“Course Identifier”,“Section Code”,“Course Space”
"Year …

The table would identify that a change occurred between Year 02 and Year 03

Thanks

I perhaps am a little biased. i can think of no easy way to do this DEVONthink. But Python has great ways to handle this sort of complexity including reading and writing files. The analysis part will be as complicated as you need.

I’d say a short script could do that. But there might be another way I’m not aware of.

1 Like

Try this…

tell application id "DNtp"
	if (count (selected records)) = 0 then return
	set tempText to {}
	repeat with theRecord in (selected records)
		if (type of theRecord) = sheet then
			copy (name of theRecord & linefeed & "	" & (columns of theRecord) & linefeed & linefeed) to end of tempText
		end if
	end repeat
	create record with {name:"Headers", type:rtf, content:tempText as string} in current group
end tell

Will this work for .csv files?
I did noticed they display as sheets
edited: but with default ABCDEF headers

It’s less complicated than my processing
I was literally copying the first line from each .csv file

Yes, it should as .CSV files are considered sheets.

Thanks, BLUEFROG!

The output lists two headers from the two files I selected, which is promising.

I tried changing the output type to csv by changing the type in this line:

create record with {name:“Headers”, type:csv, content :tempText as string } in current group

It compiles, but I’m not getting an output file anymore.

Thanks again,

There is no .CSV type.
Run it as-is. What is is the resulting rtf?

My solution is to write the data to a .csv file on my desktop
instead of a record in Devonthink

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

I’m having fun with “considered sheets”

All three of my test files show with default ABCDEF headers

When my script retrieves the file contents via plain text
. an ABCDEF row has been added
. the comma delimiters have been converted to tab delimiters

What is the type property of these files? It it’s sheet, using plainText is not sensible. There are other properties for sheets containing the headers and cells.

These are text files, extension .csv imported into Devonthink
They contain comma delimited data we need to access
Devonthink shows the kind as CSV Document

I am seeing no issue with the script I offered and CSV files imported into DEVONthink. Regardless of the type shown, they are treated and displayed as sheets.

I have a CSV file like this:

"1979","Another Room","Reason"
"The Year","The Room","The Reason"

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.

Also, I can’t change the headers, contrary to what the manual seems to think:

You will just need to provide starting column headings, which you can certainly add or take away from later.

OTOH, I do not really know what “add or take away from header” is supposed to mean.

1 Like

I have CSV files from my financial institution and other sources and none of the values are quoted.
They not only come through as a sheet with proper headers but I can also edit the columns as I wish.

@cgrunenberg would have to assess this.

Where are you creating the CSV file?

In my text editor. Afaik, quoted strings are ok in CSV files. They are, for example, used to quote strings containing commas

Yes, they should be.
I’m not able to reproduce the behavior yet.
I’m making mine in BBEdit, UTF-8 with LF endings. Then dragging and dropping into DEVONthink yields a sheet with the headers intact.