the problem I have found with csv is that different software creates different formats. sometimes, everything is quoted, sometimes only certain values are quoted, and similar to the problem of a tab delimited text file, if the text contains a equation mark(s) everything gets screwed.
In my Txtutl component, (https://www.dropbox.com/s/wj3w55p3gvzp5h5/Txtutl_Component_v1.0.0.zip?dl=0) there is an option export records to text, and a corresponding import, where returns, line feeds, and tabs which maybe embedded in the data are replaced with the following actual characters: <Cr>, <Lf>, and <Tab> this eliminates the issue of embedded field or line markers. There is another way, slightly more generic way to resolve the issue of embedded delimiter characters, replace the default delimiters! I did a bit of google-fu, and came across these ideas for saving excel files as cdv with custom delimiters: https://www.howtogeek.com/howto/21456/export-or-save-excel-files-with-pipe-or-other-delimiters-instead-of-commas/ TLDR; go to control panel/system preference change the 'List separator' character. I imagine it would not be hard to write an applescript, or batch file or VBA script (embedded in the excel file(s)) to commands to do this change and then switch it back to default. https://social.technet.microsoft.com/Forums/scriptcenter/en-US/e1a75bbc-82ab-4afb-a082-731dacb04a3e/export-to-csv-but-with-a-custom-delimiter?forum=ITCG TLDR; Run the script given in the thread Chip > Pat, > As I understand it, in a cvs file, all string values should be > enclosed inside “. > This means that a CR is simply part of the cell’s string. > If you open the exported file with a text editor, you should see the > CR enclosed inside “. > If it isn’t, the Excel export is not working correctly. > If it is enclosed, I would be looking at the code doing the import - > it is not working correctly. > Cheers, Keith > >> Keith - if you export from Excel to csv, you still have the problem with >> embedded Return characters. > >>> Sometimes an Excel csv export doesn’t comply completely with the cvs >>> rules, but mostly it does. > > ********************************************************************** > 4D Internet Users Group (4D iNUG) > FAQ: http://lists.4d.com/faqnug.html > Archive: http://lists.4d.com/archives.html > Options: https://lists.4d.com/mailman/options/4d_tech > Unsub: mailto:[email protected] > ********************************************************************** ------------ Hell is other people Jean-Paul Sartre ********************************************************************** 4D Internet Users Group (4D iNUG) FAQ: http://lists.4d.com/faqnug.html Archive: http://lists.4d.com/archives.html Options: https://lists.4d.com/mailman/options/4d_tech Unsub: mailto:[email protected] **********************************************************************
