Save Tab-delimited as Semi-colon delimited

[Closed]
Report
Posts
21
Registration date
Thursday July 23, 2009
Status
Member
Last seen
April 6, 2012
-
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
-
I see multiple postings on this issue all over the web with no solutions other than to write/use a macro... I am looking to save an excel workbook with a semicolon as the delimiter rather than a Tab when saving as a Text Document.

A Tab delimiter causes field to be quoted which is undesired.

EX field:
123 45th St., Apt. 18

then when saving as a Text Document:

"123 45th St., Apt. 18"
(surrounded by quotes) This causes my database import process to error out.


I have tried saving as a pipe | delimited file, but a similar error still occurs. I have tried using find and replace statements ( replacing "|" with ";") but also no success.

For reasons beyond my control, the internal database load process will not be changed to accept CSV or other formats other than Text Documents.

Any help, links to advice, etc is greatly appreaciated.



2 replies

Posts
21
Registration date
Thursday July 23, 2009
Status
Member
Last seen
April 6, 2012

After weeks of on-again off-again searches I found what appears to be the only solution (solutions depending on the original file format).

1.ETL requires a .csv format
One process that worked for my needs was to change the file extension before opening. For example, if it was a .CSV I changed it to a .TXT. Then opened Excel and used the File Manager to open the TXT document. After the manipulation, I then saved the document as a .CSV and the format was accepted by my ETL


2. ETL requires a .txt format
The other process which worked.... if the file was already a .TXT format, I opened it through Excel. After performing my manipulation, I went into the Tool>Options and in the International tab I un-checked "Use system separators" and put a comma (',') in the "Decimal separator" field.
At this point, it is time to save the document as a .CSV. Once the saving was complete, I renamed the file extension to .TXT and the ELT process was able to load it.
When done, I made sure to set my Internation Settings for "separators" back to default, so opening other documents would function as expected.
Posts
4476
Registration date
Thursday January 28, 2010
Status
Contributor
Last seen
August 2, 2020
768
I thought when you save a file, there is one option to save as a comma delimited file. I am not sure about semi colon delimited file.