How do I import CSV data?

(Pat Maddox) #1

tbref says “file import of comma-separated CSV files is also supported”, but dragging and dropping a CSV just produces a note with $Text set to the CSV contents, and I don’t see an import command. How do I import CSV data?

(eastgate) #2
  1. Needs to be a text drag, or a text file with the extension .csv. Encoding should be ASCII or utf-8.

  2. Needs to be a rectangular table without missing or invalid values, with Name in the first column

  3. The first row contains the names of the attributes corresponding to each column.

  4. Invalid data is fairly common for CSV problems, not least because the format isn’t terrifically well defined. Most common issue is missing values, followed closely by unmatched quotation marks in data.

  5. Testing with a small, simple test file is your friend.

(Pat Maddox) #3

What ended up working for me was:

  1. Import CSV into DEVONthink as a sheet
  2. Rename one of the columns to Name
  3. Select the entire sheet contents, copy and paste into TB

(Mark Anderson) #4

Note to self to clarify in aTbRef that this is a drag action, with no explicit menu option. Odd for those few whose use import, less menu bloat for the many who don’t, I guess.

(Paul Walters) #5

Along these lines, a common failure point for me is data that contains commas in line so the import gets confused about where the text for one attribute ends and the the text for the next one begins. CSV is actually pretty fragile and if the app that produces the CSV has an option to use OPML instead, then I’ll use the OPML to get the data over to Tinderbox. Also fragile, but less fragile than CSV.

(Mark Anderson) #6

If it’s an option, e.g. if the source process allows, I generally try to go for Tab-delimited over CSV. In short, the Tab character turns up in text less often that comma or straight double quotes (which are used to enclose text that contains commas).

One thing I would query is the original requirement the the first column be the $Name data. This was true until v6.6.0 (ref) but in v7 as long as the header row includes one column named ‘Name’, Tinderbox should map that correctly.

CSV supports cells with line breaks but tab-delin does not (I’ve just re-tested in v7.0.3). More on tabular import here and here.

(Paul Walters) #7

As noted elsewhere, I’m finding that ranges of cells containing line breaks can be copied from Numbers (v4.1) and successfully pasted into Tinderbox 7, with the line-breaks preserved in $Text. This same action fails if the source is an Excel 2016 range.