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

Dale Fye
Dale Fye used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2014

Commented:
Does the process automatically add quote marks around any field that contains a comma character?
NorieAnalyst Assistant

Commented:
Dale

What settings/options/steps are you using when you manually save the file from Excel to end up with a pipe-delimited file?

Have you tried recording a macro when you follow that process?
Ryan ChongSoftware Team Lead

Commented:
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?

probably we can change the system settings for List Separator under Regional settings.



see if that work after made the changes?
Should you be charging more for IT Services?

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

John TsioumprisSoftware & Systems Engineer

Commented:
I think with a little tweak to this solution you should be able to insert a custom delimiter
Most Valuable Expert 2015
Distinguished Expert 2018

Commented:
When you export the table to Text, in the wizard select Advanced.
Change the delimiter to |
Then finish.

See attached example, please.

/gustav
FormsTable.txt
Software & Systems Engineer
Commented:
Well i did a very quick and dirty method
Const ForAppend = 8

Open in new window


Private Sub ExportExcelSheetToPipeDelimitedText()
Dim mySheet As Object
Dim xlApp As Object
Dim strName As String
Dim LastCol As Long
Dim rowRange As Object
Dim strLine As String
Dim delimiter As String
Dim strPath As String
Dim fso As Object
Dim oFile As Object
strPath = "C:\Temp\DelimitedPipe.txt"
Set fso = CreateObject("Scripting.FileSystemObject")
    If fso.FileExists(strPath) Then
        Set oFile = fso.OpenTextFile(strPath, ForAppend)
    Else
        Set oFile = fso.CreateTextFile(strPath)
    End If
delimiter = "|"

i = 1
    Dim LastRow As Long
    strName = "C:\Temp\LookupExample.xlsx"
    Set xlApp = CreateObject("Excel.Application")
    Set mySheet = xlApp.Workbooks.Open(strName).Sheets(1)
    
     LastRow = mySheet.Cells(mySheet.Rows.Count, "A").End(-4162).Row
Set rowRange = mySheet.Range("A1:A" & LastRow)
    
   For Each rrow In rowRange
   strLine = vbNullString
        
        LastCol = mySheet.Cells(i, mySheet.Columns.Count).End(-4159).Column
        Set colRange = mySheet.Range(mySheet.Cells(i, 1), mySheet.Cells(i, LastCol))
        For Each cell In colRange
            strLine = strLine & cell.Value & delimiter
        Next cell
        strLine = Left(strLine, Len(strLine) - 1)
        oFile.writeline (strLine)
        i = i + 1
    Next rrow
    
    Set oFile = Nothing
    Set fso = Nothing
    Set mySheet = Nothing
    Set xlApp = Nothing
End Sub

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
It was a recommendation similar to Ryan's and changing the list separator in the Regional settings which is allowing the export to work properly when I perform this task manually:
1.  Open file in Excel
2.  Click File => SaveAs
3.  Select the CSV format from the save as dialog
4.  Click OK.

But for some reason, when I open the file with VBA automation, and perform a sheet.SaveAs operation, it does not use that list separator format, with the pipe.

@Aikimark, when performing a true CSV export (with comma delimiters), it does.  But this does not help, as the SQL Server Bulk Insert process does not recognize these quotes and still separates the fields based on the embedded commas.  That is why I want to use the pipe as the separator, because there are no fields in the data that contain a pipe character.

@John,  Yes, I could use that technique, but the files that this procedure will act upon may contain as many as 250,000 records, and I was hoping that I could get the sheet.SaveAs method to work properly with this delimiter.

@Gustav,  the manual process works now, it is the automated process which is not working properly.
John TsioumprisSoftware & Systems Engineer

Commented:
Personally i wouldn't tamper with system settings to get the desired functionality ...you can always forget something and when everything just stops to work then......if the fso crashes with many records its a different case...i will try to check...
John TsioumprisSoftware & Systems Engineer

Commented:
My poor old G620 had a hard time handling 260,000 records but it pulled it off just fine...so fso works ...for 250K+ records
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
how long did that take, John?  And how many columns did you use in your test?
John TsioumprisSoftware & Systems Engineer

Commented:
20 columns 259400 rows...time .... a lot...it was not a quick operation.....probably would be faster if it was running from inside Excel...
Sham HaqueSenior SAP CRM Consultant

Commented:
To echo John's sentiment regarding change of system settings - yes it works, but...it also replaces the delimiter when composing formulae, so instead of writing
IF(A2="Y","Hello","Bye")

Open in new window

, you need this
IF(A2="Y"|"Hello"|"Bye")

Open in new window


Feels wrong!
Top Expert 2014

Commented:
Which SQL Bulk Loader?
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
@Sham, Interesting, That is a big deal.  But since the user will be uploading up to a dozen of these files at a time, long running loops like John suggested will lock up their computer, potentially for hours.  Need something that works better.  Maybe there is a way to change that system setting briefly, while the code runs, and then set it back, after it completes.  More testing needed.

@aikimark, Bulk Insert
declare @SQL nvarchar(max)
set @SQL = 'Bulk Insert #AL  FROM ''' + @FilePath + @Filename + ''' ' +
'with (FirstRow = 2, Datafiletype = ''char'', Fieldterminator = ''|'', Rowterminator = ''\n'')'
exec @SQL

Open in new window

WHERE #AL is defined as a temp table with all the fields are defined as nvarchar( ).  After importing into this temp table, the SP performs an Append query that performs data type translations between the nvarchar( ) in the temp table and the actual field types in the reporting database table.

Unfortunately, the Bulk Insert process of SQL Server identifies the fieldterminator (whether embedded inside quotes or not) as a field terminator, so it would break:
a, "abc,def", 1
into 4 columns
a, abc, def, 1
when performing the bulk insert.
John TsioumprisSoftware & Systems Engineer

Commented:
if speed is the concern then you need to code the insert directly from Excel avoiding the in between..
Top Expert 2014

Commented:
If you use the CSV file exported from Excel, what happens if you perform the bulk import and add the following parameters:
FORMAT = 'CSV'
FIELDQUOTE = '"'

Open in new window

Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
aikimark,

Have not had a chance to play with that yet.  Will test sometime over the weekend.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012
Commented:
Dale, ...
(Just stopping by...)
;-)

I saw this and I rigged up something that may be of value???
If you "Import" the CSV file into Access you can run code like this to create a text string with Pipes as delimiters.

Its a long shot, but it seemed to work for me (presuming I am understanding your situation).
Meaning:
I created a simple file in Excel (With a "fullname" field containing names in this format: "Coachman, Jeffrey", ...to test)
...and saved it as a csv file while in Excel.
I then imported this Excel CSV file into access and ran code like this:
Dim rst As DAO.Recordset
Dim str As String
Dim fld As DAO.Field
'Your Excel CSV file that you import into Access as a table named "Change2Pipe"
Set rst = CurrentDb.OpenRecordset("Change2Pipe")

    'Get the Field Names
    For Each fld In rst.Fields
        'Add a | between each one
        str = str & fld.Name & "|"
    Next fld

    'Trim the trailing "|"
    str = Left(str, Len(str) - 1) & vbCrLf

    'Loop all the records
    Do Until rst.EOF
        'Loop all the fields
        For Each fld In rst.Fields
            'Add each field value and concatenate a "|"
            str = str & fld.Value & "|"
        Next fld
        'Trim the trailing "|"
        str = Left(str, Len(str) - 1)
        rst.MoveNext
        'make new line
        str = str & vbCrLf
    Loop
        'Trim the trailing "CRLF"
        str = Left(str, Len(str) - 2)

rst.Close
Set rst = Nothing

MsgBox str

'Create the new Pipe Delimited text file
Open "c:\YourFolder\Change2Pipe.txt" For Output As #1
    'Insert the string variable into it
    Print #1, str
'Close/save this file.
Close

Open in new window


As a test, I then took this newly created "PSV" text file and was able to successfully import it (back) into Excel, and it opened ok and the data presented well (Correctly defined fields and the comma in the Full Name field was preserved)
I then was able to, (double-check), also import this PSV text file back into Access (and again, ... it opened and the data presented well)

I'm on the run, ...but if you want the sample files, ...I can get them to you tomorrow...
;-)

Jeff
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
aikimark,

re: your comment

SQL Server 2008 R2 will not accept those settings in the Bulk Insert command

FORMAT = 'CSV'
FIELDQUOTE = '"'

I'm going to have to go back to the drawing board, and see whether the client can get the original file, exported from their accounting software in a CSV file, and determine whether there is a way for them to strip commas from the fields during the export process.  If not, I'm probably going to have to read the CSV files and parse out the commas embedded within text fields, and then remove the embedded quotes.  Don't know how long this will take, but will give it a test later this week.

I also found that Excel was converting several text fields that look like numbers to numbers, and then reformatting them as scientific notation, which corrupted the data as well.
Dale FyeOwner, Dev-Soln LLC
Most Valuable Expert 2014
Top Expert 2010

Author

Commented:
Thanks for the suggestions, I ended up with a solution similar to each of these.

I opened the file as a text file using VBA, read each row of the text file as a string, then parsed each string on the commas, then reconstructed the string, with some logic that accounted for string segments that started or ended with a quote.

"ABC,DEF"

would be broken into array elements "ABC and DEF", so if the array element started with a quote it accounted for that and instead stripping the leading quote and inserting a comma , it simply stripped the leading quote.  Then as the next element was read, it would see that it ends with a " and would strip that quote and append that array element to the previous, followed by a comma.

But then I found some records were a field contained "ABC, DEF, GHI", so I had to add logic that would account for multiple commas embedded within a single field and return ABC DEF GHI.  

I'll be writing an article on this process it shortly.

By replacing the embedded commas with spaces and then stripping double spaces I was able to accomplish what I was looking for.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial