When I export sheets created in DT Pro Office, they come out as TSV files. Unfortunately, neither Excel nor Numbers agrees to open them. Is there a way to export them as CSVs or to make Excel or Numbers open them?
1 Like
Maybe you have to use a different file extension but both applications should be able to open tab-delimited files.
Neither one agrees to open it and in fact, Open Office and Word open it as a text document.
What extension should be used and how do I get DT Pro to use that automatically? Iād hate to change the extension by hand every timeā¦
To be fully clear, this is a sheet generated in DT Pro with a few records containing both textual and numerical data. It is then exported through File -> Export -> Files and Folders. What I get are two files, one TSV file with the same name as the sheet and one property list called DEVONtech_storage. Dragging it out on to the Desktop produces the same TSV file, but no property list.
Unfortunately I donāt know as I donāt use Excel or Numbers but every good spreadsheet application should be able to import tab-delimited data in one way or the other.
Yes, yes, other programs should do all kinds of things. But how do I get DT Pro to function and output something readable?
None of the spreadsheets that I have (Excel, Numbers & Open Office) agrees to open the sheets. Both MS Word and Open Office Writer think the sheets are text documents. And this same behavior results whether the extension is tsv or tab.
Sampas, as Christian said, Excel can import data exported by a Sheet from DT Pro. The result in Excel will be columns and rows corresponding to the columns and rows in the DT Pro Sheet.
Iāve sent files back and forth between Excel and DT Pro in the past, so I know that it works. Iāve got MS Office for Mac but I donāt have it on my laptop (my most-used computer) and donāt plan to install it on this computer.
So your issue is not that DT Pro canāt export Sheets as text files that can be imported into Excel ā it can. Your issue is to find out how to get the text file into Excel as a worksheet with columns, rows and cells corresponding to the original DT Pro Sheet, at the Excel end. And that can be done. How? I donāt remember, but I could rediscover it quickly from the Excel side.
As an Excel user, you will likely come across various text file extensions among files that you wish to capture as spreadsheets. At the Excel end, you will read Excel help files and perhaps experiment with changing the extensions of text files before importing them into Excel ā perhaps trying some Excel options.
I say that because I donāt remember how to capture DT Pro Sheet text file exports into Excel as structured worksheets, but I had originally discovered how to in a few minutes of experimenting. Note: if you export a worksheet from Excel as a tab-delimited text file, what is the extension of that file? Any options for export as text files? And what are the extensions of text tiles that Excel can import and will read as delimited so as to create structure in the worksheet? As I remember, it was easy.
Sorry for the indirect response, but there are a number of applications that read delimiters in text files to create structure in spreadsheets or databases, and there can be differences in the text file extensions and import options that are characteristic of each such application.
DT Pro doesnāt attempt to guess which application one might be using to capture information from an export of a Sheet. Many Mac applications that use a delimiter to structure data from a delimited text file can read and structure exports from Sheets, but the technique can vary from one application to the next.
Sampsa,
If still necessary I can check for you this weekend. I have done this several times and it is indeed a matter of doing the right thing in Excel, nothing wron with DTPO.
Gerben
Excel has two mechanisms to get data from text files, File -> Open and Data -> Get External Data -> Import Text File. Neither of which agrees to open a TSV-file, but both of which are fine with tab-delimited files as long as they have TXT-extension. Same thing for Numbers. NeoOffice Calc agrees to open a TSV-file through Insert -> Sheet From File. If there are additional ways to get data into Excel or Numbers, let me know.
Unless I change the extension by hand to txt looks like neither Excel nor Numbers will import the file that DT Pro exports. Is there a way to change the extension that DT Pro exports or to change the format to comma-separated? A script maybe?
I just followed these steps to import a DTPO sheet into Excel:
- export sheet in DTPO as āfiles and foldersā: this puts a .tsv file on the desktop
- open Excel and use open command to open the .tsv file (you maye have to tell Excel to enable āall documentsā instead of āall readable documentsā in the menu that appears)
- when you do this Excel starts a Text Import Wizard: keep settings as they are and sheet is imported. You may have to change to column headers.
This should do it.
Gerben
Thanks, Gerben.
Sampsa, there are text editors that will allow one to batch process large numbers of files at a time for tasks such as changing from one delimiter character to another and/or for changing the file extension.
Thank you!
What programs allow for batch processing?
(free) TextWrangler barebones.com/products/textwrangler/
has a Multi-File Search&Replace facility; text only
(commercial) TextSpresso taylor-design.com/textspresso/overview.htm
is a powerfull tool for batch processing files (can process binary files)
(free) FileWrangler software.christopherdrum.com/filewrangler
can batch process files (renaming, changing extension, ā¦): I weekly use it to batch rename the awfull names my Ricohās camera attaches to my pics
(commercial) aBetterFinderRename publicspace.net/ABetterFinderRename/
One step ahead from FileWrangler
Or do the following in Terminal:
type pico -w ./batch.sh
IFS='
';
for i in `ls -1`
do
ls "$i"
done
and type chmod +x ./batch.sh
and type ./batch.sh to run the program.
What this will do currently is list the contents of all directories in the current directory. In other words, it lists every item in the directory (āls -1ā), turns that into a variable (āfor i inā¦ā), then repeats an instruction (āls ā$iāā) for each one of those items. The IFS variable (set to a carriage return) is the internal field separator, a variable that defines what characters count as whitespace. Donāt worry about it.
If you want to rename a lot of files from .tsv files to .whatever, then just make the script look like this:
IFS='
';
ext2='txt'
for i in `ls -1 *.tsv`
do
name=${i##*/}
name0="${name%.*}"
base="${name0:-$name}"
mv $name $base.$ext2
done
No program installation, no shareware, none of that crapā¦ and you learn something too 
Iām very new to DTP - just trying it out - and I have a couple of comments that Iād like to share. They are at least somewhat prompted by the above message thread.
Some context: I have a bit of a research background and am now working in knowledge management for a biotech company. Iām very new to knowledge management. Until about 2 yrs ago, and for about 20+ years prior to that, I was a PC owner/user; and I used UNIX machines in the research lab, but I didnāt administer them. I am now a total convert to Mac. One of the reasons is because they seem to have a very well thought-out interface to a machine that works really well. That is, I can sit at my MacBook or iMac and do what it is that Iād like to do. I can use the software rather than first needing to finish programming it.
My experience in the PC world was full of tinkering. For the most part, I donāt want tinkering anymore; Iād like to find programs that are well thought-out and designed - as is the computer and operating system. I think that is a characteristic of the Mac user community, though. I certainly appreciate the idea of extensibility, but there needs to be a lot of easy-to-access basic functionality there first.
For a very long time, PC and Mac users have had access to one very versatile and easy to use spreadsheet program or another. Offering Mac users something like what Sheets so far appears to be, is a huge step backward. For it to be attractive to current Mac users, I would think it needs to be much more than it is now. It doesnāt need to be Excel, but it needs to have a lot more basic functionality. And, the transfer of information to Excel needs to be much-much-much simpler.
I see a lot of potential for DTP, and I would love to have it be able to do the kinds of things Iād like to do - and to help me learn how to manage information better. I will keep testing it out for at least as long as the trial period lasts, but it will need to provide me with the same class of functionality and ease of use as the Mac and the Mac software that Iāve used thus far do. Sheets hasnāt done that.
How difficult would it be for DTP to allow comma-separated-values as an optional output format?
Thanks.
Iām with KeithKendrick, but further suggest that in the export dialog you have a check-box for which separator you want to use, that way you could select at export time.
Bill.
I have created a simple sheet in DEVONthink, here is how it looks like:
And here it is the same file opened in Excel:
As you can see the header gets modified automatically with some junk data that is most likely related to the data types used when building the sheet in DEVONthink!
But this is not very productive if we need to use the file in another OS.
Is there any way we can create csv files within DEVONthink and use them as sheets within the product?
Itās the definition of the types of the columns but required.
You could write them out with a script. That should be relatively easy.
Or just throw away everything after the ā#ā in the first row. Not too hard either, Iād say.
Yes, it is. But I hope you agree that the file in the outside system does not present in a very user friendly way as I would need to edit all the headers.
Is there any way we can create csv files within DEVONthink and use them as sheets within the product?
No. CSV files would use the same headers.