4

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

Community
  • 1
  • 1
rohrl77
  • 3,277
  • 11
  • 47
  • 73
  • I'm guessing here. Perhaps because it is a CSV (*Comma* Separated Value), but the delimiter is actually a semicolon? Maybe Excel is trying to use both commas and semicolons as delimiters. – PowerUser Dec 06 '12 at 15:11
  • I'm guessing your `Workbooks.Open` command has extra delimitters listed - can you post up more code so we can see how this is happening? – John Bustos Dec 06 '12 at 15:12
  • @ John Bustos: I updated the above to show more of the code. Does this help? How would I test if my `Workbooks.Open` command has extra delimiters? – rohrl77 Dec 06 '12 at 15:17
  • @PowerUser: Yeah. I know the name says Comma, but I've seen many other files that use other delimiters, and it seems to have worked in the past. The thing that really gets me is that when I open the file manually it comes out PERFECT??? – rohrl77 Dec 06 '12 at 15:19
  • Even though you're not specifying a comma as the delimiter, are you able to escape the commas by surrounding any values containing them with double quotes? e.g. `"JEAN 5 POCHES EXTENSIBLE+1,60M"` – Sid Holland Dec 06 '12 at 15:49
  • @SidHolland: The CSV has double quotes around all the values, including the commas. – rohrl77 Dec 06 '12 at 15:55

6 Answers6

5

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

António Almeida
  • 9,620
  • 8
  • 59
  • 66
Gido
  • 51
  • 1
  • 3
1

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

Romain
  • 1,292
  • 1
  • 10
  • 14
  • Hi Romain, I've just tried your suggestion. Same outcome. Then I tried combining your suggestion with a custom delimiter in combination with joseph4tw's answer. Saddly also no go. – rohrl77 Dec 06 '12 at 15:33
1

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
Joseph
  • 5,070
  • 1
  • 25
  • 26
  • Just tried your suggestion. Unfortunately, it didn't work. It's still coming up in two columns. Worth a try though. Thanks! – rohrl77 Dec 06 '12 at 15:27
  • @excelnova Wow, this is strange. I can't get it to open right no matter what I try.... – Joseph Dec 06 '12 at 15:44
  • I just tried importing the file as a `QueryTable.Add`. That seems to do the trick. Now I get the data as if I had opened the file manually. Unfortunately, this solution requires that I recode the entire import section of my macro. :- ( ... better this than no solution I suppose! – rohrl77 Dec 06 '12 at 15:57
  • @excelnova please do, i'd like to see what you come up with – Joseph Dec 06 '12 at 15:58
  • @joseph4tw... since PowerUsers suggestion worked, i won't post the work around. HOWEVER, your suggestion would have been a second solution! So thank you as well! – rohrl77 Dec 06 '12 at 16:43
1

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

PowerUser
  • 11,583
  • 20
  • 64
  • 98
  • You were right!!! Changing the file extension to TXT did the trick. Now it opens and calculates perfectly! Thanks! For the time being this is a better solution than reprogramming everything. – rohrl77 Dec 06 '12 at 16:42
  • I'd still love to know why this happens though and how to avoid it with a CSV format. Thank you for your help! – rohrl77 Dec 06 '12 at 16:44
  • Glad to be of help :) My untested&unresearched guess is that because the extension was CSV, it was using the comma no matter what. When you specified the semicolon, you were really just adding a second delimiter. Why it ignored the double-quotes around the comma, I have no idea. If anyone wants to correct my theory, please do. – PowerUser Dec 06 '12 at 20:19
1

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.

Stephen King
  • 581
  • 5
  • 18
  • 31
Jozef
  • 39
  • 1
0

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

Charlie Lo
  • 1
  • Hi Charlie, welcome to Stackoverflow and thanks for taking a stab at this. However, the problem I had was while importing. Setting the cell format would not have an effect until after the import was completed. – rohrl77 Dec 15 '22 at 10:09