0

I have a scenario where db2 SQL query should fetch the data in to a CSV file and the csv file's data should be separated with a pipe delimiter, I have done that by using regional settings change in control panel by changing the List Separator to | (pipe) from , (Comma) ...after this step the flow in my program is, there is a VBA code in a xlsm file which opens this pipe delimited CSV file and copies its contents to it but here the default delimiter is changed to , (comma) automatically and not considering the pipe delimiter. I have debugged the VBA and the issue is with

workbooks.open("Path\file.csv")

here the csv file is opened with comma as a default delimiter and copied to the xlsm file.

ex: The pipe delimited csv file data is like the below one which was correctly separated in to multiple columns by the pipe delimiter

1234567|jane,smith|canada|False...

But the .xlsm file is opening the file which is as shown below and copying the data to it into just two columns by considering the comma as a separator, the name field is fetched as last name and first name with a comma in between which is acting as a delimiter and separating the multiple columns to two columns.

1234567|jane    smith|canada|False..

How to make this xlsm file to consider the delimiter as pipe but not comma..any code should be written or any settings change?

Jay
  • 1
  • 1
  • What is the setting in Excel? File->Options->Advanced: Decimal separator? –  Mar 15 '19 at 13:44
  • @peakpeak..Sorry for Late reply Decimal Separator is .(dot) and Thousands Separator is , (Comma) – Jay Mar 18 '19 at 09:21

1 Answers1

0

CSV = Comma separated value. Since it's not a CSV file try naming it .txt and specify that the delimiter is a pipe on import otherwise it might default to tab.

This works:

Sub OpenCSV()

Dim wkbTemp As Workbook
Dim sPath As String, sName As String

sName = "TestFile.txt"
Set wkbTemp = Workbooks.Open(Filename:=sName, Format:=6, Delimiter:="|")

End Sub

This doesn't

Sub OpenCSV()

Dim wkbTemp As Workbook
Dim sPath As String, sName As String

sName = "TestFile.csv"
Set wkbTemp = Workbooks.Open(Filename:=sName, Format:=6, Delimiter:="|")

End Sub

Excel expects a file with the extension .CSV to be formatted correctly with comma separator and double quoted strings. Pipe delimited text files are .txt, period.

Jim Castro
  • 864
  • 5
  • 10
  • The requirement is to actually import to csv and then work with VBA code in .xlsm and then save back in .csv..But I tried testing with.txt as well..in this case the pipe delimiter is not at all considered not even for first column like in the earlier case all the data is fetched in a single column like below, – Jay Mar 18 '19 at 09:30
  • Current Scenario after changing to .txt: Column1 | Column2 | Column3 Row 1 1234567|jane,smith|canada|False... Previous case: Column1 | Column2 | Column3 Row 1 1234567|jane smith|canada|False.. Actual CSV file data is partitioned with each field in each column as delimiter is pipe Column1| Column2 | Column3 | Column4 Row 1 1234567 jane, smith canada False – Jay Mar 18 '19 at 09:36