Is it possible to import data that’s tab-delimited instead of comma-delimited - especially from the clipboard?
If so, how do I do that?
Thanks!
You can use the Text Import Wizard to import data from the clipboard. Open the wizard, then choose Import Data>From Clipboard from the menu bar. This is covered in the documentation.
The wizard will work with tab or comma delimited data, it automatically detects which format is provided.
Thanks!
I actually had already tried that, and the automatic detection saw it as comma-delimited - I had not wrapped my fields in quotes, but I was hoping the presence of a tab would make the importer see it as tsv instead of csv.
Is there any way to override the auto-detect and specify that the clipboard data is tab-delimited?
Thanks!
Edited to add:
although oddly, the import dialog didn’t seem to think there were more than 1 column in the clipboard. It only showed 1 column in the wizard, but when I actually performed the import, it put anything after a comma into the next field over, and then told me there weren’t enough columns for my data (there were two columns in the clipboard data, and about 20 in the database I’m appending to).
… just had a thought - I only had the tab in 1 row. If I made sure to have a tab in the first row, would that have fixed it?
This isn’t properly formatted TSV or CSV data. All rows should be consistent.
IIRC the importer checks the first line. If tab is there it presumes TSV, if not CSV, then imports all lines that way. But if your lines aren’t consistent your results will be odd, and rarely what you want.
Thank you both - those are helpful.
I’ll make sure all lines have the same format on my next attempt … although I may need to set a bunch of values on import, so I may end up writing a procedure to do this.
Thank you for the help.
Long ago, whatever was first encountered, a Tab or a Comma, that was used as the field delimiter. So if you wanted it to be Tab, you could create and extra (empty) first line with a Tab character in it (setting the delimiter) then delete that line as part of the post-import processing procedure.
“Text Import” is a long document, but I didn’t see anything about the field delimiter detection. I’d imagine that if your first field contained a comma, but your fields were separated by tabs, that might create a problem.
If you appended a Tab at the beginning - so it was the first thing (i.e. not a comma) the import algorithm saw, that would set the delimiter to Tab - but would throw your columns off. That’s why I suggested that the first tab be in a separate (extra) record that’s deleted later.
If you use the importtext command you can specify the field separator. Thus to import specifically tab-separated data from the clipboard:
importtext clipboard(),"separator",tab()
(plus maybe further option/value pairs if they are relevant).