Sheets created in DEVONthink have by default a semicolon-separated-value file underneath.
Is there a way to say use comma-separated-value (CSV) format
I see tab-separated, quote-enclosed fields in a file with a “.tsv” extension when I create a Sheet…
However, I do see tabular data when I click on a .csv, which is a really nice touch!
Sean
2 Likes
Since S-CSV, CSV, and TSV are all simple text files, it is certainly possible to replace one separator by another. Though it might need some careful thinking to avoid replacing the separator inside strings.
Which is why the .tsv files have fields which are quote-enclosed.
I think .csv files are encoded so fields/cells which need it are enclosed, usually.
Maybe the Sheet .ttsv files are just blanket encoded that way to simplify coding, I’ve not explored either formats extensivly in DT4.
Sean
What makes you say that? Taking a look at the raw content of a TSV file gives me, for example, this:
"1. Spalte#real{format:currency}" "2. Spalte#string"
"10" "Preis fĂĽr 2. Produkt ""Blub"""
"500" "Preis fĂĽr 1. Produkt"
Not a single semicolon in view. It really is a tab separated file, with every field enclosed in double quotes (needlessly, imo), and quotes in a field themselfes duplicated. Are you perhaps looking at a C-SSV file that you imported into DT?
To your question whether you can convert that to a CSV: Yes, you can. Something like this should do. (Disclaimer: I stole some of the code shamelessly from a HI)
(() => {
const app = Application("DEVONthink");
const r = app.getRecordWithUuid('x-devonthink-item://AF4EA610-05BB-47DB-B57D-2512D28AB659');
const separator = ",";
/* Get the column headings and remove the formatting information from them */
const columnHeadings = r.columns().map(t => t.replace(/#.*$/,''));
/* csvData will contain the data rows separated by "separator", with the colum headings in the first row */
const csvData = [];
csvData.push(columnHeadings.join(separator));
/* Now loop over all rows */
r.cells().forEach(row => {
const csvRow = row.map(value => {
/* Loop over all cells in this row. If it contains a comma, quote or a line separator,
double all quotes in it and then enclose it in quotes. */
if(/[,"\r\n]/.test(value)) {
return `"${value.replace(/"/g, '""')}"`;
} else {
return value;
}
})
/* csvRow is an array of possibly quoted values. `join` creates a string from
it with `separator` between each value. `push` appends it to the `csvData` array */
csvData.push(csvRow.join(separator));
})
/* Create a string containg CSV data by joining all CSV rows with a newline */
const csvText = csvData.join('\n');
/* Do whatever you want with the CSV text here. You could write it to an external file or try to create a DT record from it. */
})()
I looked with a text editor at the underlying file of my sheet - and it is definitely a semicolon-separated-value file.
What “text editor” ? .tsv files are delimited by tabs.
TextEdit.app
My file has the ending .csv. However the content is *not comma-separated, but semicolon-separated.
When does DEVONthink create sheets as semicolon-separated “csv” files?
When you create them in DT. And then the extension is tsv.
If you import a,say, CSV into DT, it apparently keeps the original delimiters. DT never changes what you import.
That’s why I asked you if that where perhaps an imported file.
1 Like
I am very sure that I initially created the sheet in DEVONthink (and it is semicolon-separated) – but that’s a few DEVONthink versions ago…
It doesn’t natively. Sheets are always tab-delimited .tsv files.
2 Likes
If, however, you open a comma-separated file not created in DT as a Sheet and edit a column type (e.g. from Multi-line Text, which is the default type, to Single-line Text), it will convert the whole Sheet to semicolon-separated.
Before:
column 1,column 2
item 1,item 2
After:
"column 1#string";"column 2#text"
"item 1";"item 2"
1 Like