troubleshooting Question

Export Excel worksheet as CSV with pipe "|" character as delimiter

Avatar of Dale Fye
Dale FyeFlag for United States of America asked on
Microsoft AccessMicrosoft ExcelMicrosoft Office
20 Comments2 Solutions1613 ViewsLast Modified:
I cannot find the previous question I asked about this.  Someone provided a process by which I could change the delimeter for Excel "CSV" files from a comma to the pipe character.  I need this because some of the text fields contain embedded commas and I don't want to change those, but I need the data in a CSV format for uploading using the SQL Server Bulk Insert command.

At any rate, when I open the Excel spreadsheet manually and save the file as a CSV, (also manually), I get the pipe delimiter.  But if I open the spreadsheet via Access automation and save the worksheet:

    osht.SaveAs Replace(oWbk.FullName, ".xlsx", ".csv"), xlCSV

I get a comma separated file instead of pipe separated.  Can anyone provide any recommendations which will allow me to automate saving the selected sheet of the XL file as a pipe delimited file?

Dale
ASKER CERTIFIED SOLUTION
John Tsioumpris
IT Supervisor
Join our community to see this answer!
Unlock 2 Answers and 20 Comments.
Start Free Trial
Learn from the best

Network and collaborate with thousands of CTOs, CISOs, and IT Pros rooting for you and your success.

Andrew Hancock - VMware vExpert
See if this solution works for you by signing up for a 7 day free trial.
Unlock 2 Answers and 20 Comments.
Try for 7 days

”The time we save is the biggest benefit of E-E to our team. What could take multiple guys 2 hours or more each to find is accessed in around 15 minutes on Experts Exchange.

-Mike Kapnisakis, Warner Bros