Automated conversion of CSV to Tab Delimited

Topic

New Reply

Now that a project has come to the point of being put into production, one very large stumbling block came up – how to convert a file that is FTP’d from a Unix box to a Win NT box into a Tab delimited file without human intervention. Does someone have a method by which I can do the necessary conversion? The resultant file goes into a VB process what is looking for Tab delimited strings and just converting the commas in the CSV file to tabs will not work (some of the data fields contains commas ) Using Excel 97 and NT 4 SP6. TIA

The difference between Genius and Stupidity:
A Genius knows their limits.
- Albert Einstein

Reply | Quote

Replies

Can’t you modify the VB code to use comma delimited?

Other options:
I assume that the fields which contain commas are within fields with quotes? Are all the fields delimited by commas and within quotes you could then replace the comma between the quotes with a tab between quotes (that is [“,”] with a [“”])

Otherwise (if some fields have quotes and others don’t) you could open the text file in VBA and read it character by character and replace any commas, that are NOT within quoted fields with tabs and leave the others alone: set a “flag” at the first quote and ignore any commas until you hit the end quote to “reset” the flag. You would replace any commas only when the “flag” is reset.

Steve

Reply | Quote

Can’t you modify the VB code to use comma delimited?

Other options:
I assume that the fields which contain commas are within fields with quotes? Are all the fields delimited by commas and within quotes you could then replace the comma between the quotes with a tab between quotes (that is [“,”] with a [“”])

Otherwise (if some fields have quotes and others don’t) you could open the text file in VBA and read it character by character and replace any commas, that are NOT within quoted fields with tabs and leave the others alone: set a “flag” at the first quote and ignore any commas until you hit the end quote to “reset” the flag. You would replace any commas only when the “flag” is reset.

Steve

Reply | Quote