CODEWITHSUNDEEP
excelvbacsv
rohrl77
rohrl77

Reputation: 3337

Opening CSV in Excel using VBA causes data to appear in two columns instead of one

I created VBA code in Excel 2007/2010 to import data from a CSV file. Unfortunately, when I open the file programmatically, the data is split into two columns (A and B) for certain rows of data.

When I open the CSV File manually, everything displays fine!

Generally the CSV data looks like this (example header row):

TBWAKT;"TBWAKO";"TBSAIS";"TBSKU9 ";"TBSMOD";"TBLETT";"TBKBNR ";"TBBEZ2 ";"TBFAR2
";"TBSUGC";"TBSOGC";"TBEINK ";"TBKBGR ";"TBKBGF ";"TBVKPE ";"TBVKPR ";"TBEKPE
";"TBAUAN";"TBFAAN";"TBREAN";"TBSTAN";"TBRUAN";"TBKPAG";"TBERDT ";"TBDATV ";"TBDATB "

The data that causes problems includes a comma in the text. Here is an example:

JEAN 5 POCHES EXTENSIBLE+1,60M

Here is the code:

Private Sub OpenCSV(x As Integer, wkbDashboard As String, wkbCsvImport As String, wksDestination As Worksheet)
' Opens CSV and copies data to current workbook
Dim wkbCsvImportName As String
Dim r As Range

Workbooks(wkbDashboard).Activate

' Open and read CSV
Workbooks.Open Filename:=wkbCsvImport, Format:=xlDelimited, Delimiter:=";"
wkbCsvImportName = ActiveWorkbook.Name

Screenshot of the problem. The stuff in red is in column B after opening the file.
enter image description here

Upvotes: 4

Views: 9667

Answers (6)

Charlie Lo
Charlie Lo

Reputation: 1

Change the cell format to text. e.g. Cells(1,1).NumberFormat = "@"

Upvotes: 0

Jozef
Jozef

Reputation: 39

I know two possible workarounds:

1) Change the extension from .csv to for example .xxx and open it like this:

Workbooks.OpenText fileName:="file.xxx", _
Origin:=xlMSDOS, StartRow:=1, DataType:=xlDelimited, TextQualifier:=1, _
ConsecutiveDelimiter:=False, Tab:=False, Semicolon:=True, _
Comma:=False, Space:=False, Other:=False, OtherChar:="", _
TrailingMinusNumbers:=True, Local:=True

If you use .csv or .xls, then the excel overrides the settings by it's default values from the OS.

2) In Windows 10, change your locale setting from English - United States to English - United Kingdom. It's strange that it helps, it doesn't matter what the delimiter setting in advanced date/time is. In Windows 7 I think the delimiter setting worked.

Upvotes: 1

Gido
Gido

Reputation: 61

Add Local:=True as argument in Workbooks.Open
Hope this might help!

Upvotes: 6

PowerUser
PowerUser

Reputation: 11791

I still suspect it's because the extension is CSV. What happens if you rename the file as a .txt?

Upvotes: 1

Joseph
Joseph

Reputation: 5160

I think when you do it manually Excel is reading the delimiter as ";" and not just ;.

Try this:

Workbooks.Open Filename:=wkbCsvImport, Format:=xlDelimited, Delimiter:=""";"""

EDIT:

the only way I can get this to work is by changing the file extension from csv to txt and then run this code:

Workbooks.OpenText Filename:=wkbCsvImport, _
                    DataType:=xlDelimited, semicolon:=True

Upvotes: 1

Romain
Romain

Reputation: 1302

In order to import data with a separator that is not a comma, you should set the Format attribute to 6 in order to be able to define your delimiter, as described here. It should also work if you directly set Format to 4

Upvotes: 1

Related Questions