Import CSV (UTF-8) to Excel without formatting

Question

I have code for pasting data from CSV file to Worksheet. Everyhting works fine but I am facing some problems with current solution.

Is there a way to paste data as it is without formatting? Now Excel is formatting for example -Name to =-Name so there #Name? errors instead of Values. Also it is formatting 215067910018 to 2,15068E+11.

My CSV strings look like:

123456,"Word1","Word2","-Word3",,"Word4","Word5","00076",,"Word7","Word8"

After running the code "-Word3" looks like #Name? in Excel cell. Also instead of 00076 I get 76

Here is my current code:

Sub GetCustomers()
    Dim ws As Worksheet
    Set ws = ThisWorkbook.Sheets.Add(After:= _
             ThisWorkbook.Sheets(ThisWorkbook.Sheets.Count))
    ws.Name = "Sheet1"

    Dim strText As String

    Dim file As String
    file = "L:\15\186507.CSV

    ' read utf-8 file to strText variable
   With CreateObject("ADODB.Stream")
        .Open
        .Type = 1  ' Private Const adTypeBinary = 1
        .LoadFromFile file
        .Type = 2  ' Private Const adTypeText = 2
        .Charset = "utf-8"
        strText = .ReadText(-1)  ' Private Const adReadAll = -1
    End With

    ' parse strText data to a sheet
    intRow = 1
    For Each strLine In Split(strText, Chr(10))
        If strLine <> "" Then
            With ws
                .Cells(intRow, 1) = strLine
                .Cells(intRow, 1).TextToColumns Destination:=Cells(intRow, 1), DataType:=xlDelimited, _
                    TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=False, _
                    Semicolon:=False, Comma:=True, Space:=False, Other:=False
            End With

            intRow = intRow + 1
        End If
    Next strLine

End Sub

EDIT:

I am able to do it while Converting Text to Columns manually. Shouldn't it also be possible by VBA? I mean set Column Data Format while .TextToColumns

The TextToColumns method specifies TextQualifier=xlDoubleQuote. Are things like -Name in double quotes? If there were, that problem might go away? — Cindy Meister
– Cindy Meister, Commented Oct 17, 2019 at 9:29
@CindyMeister they are in double quotes, like "-Word3". But problem still appears. — 10101
– 10101, Commented Oct 17, 2019 at 10:15
If you import using Power Query (available in Excel 2010+), it respects the double quote and imports "-Wordn" as a text string. — Ron Rosenfeld
– Ron Rosenfeld, Commented Oct 17, 2019 at 12:30
@RonRosenfeld I was thinking about it but I am not sure how it will work if my CSV is constantly updating and there new rows coming. Will table be updated each time I open Workbook. I want to create VBS to run this application daily. So it is importing 2 CSV files to one Workbook, connects them together and them Exporting objects to our File Management system. — 10101
– 10101, Commented Oct 17, 2019 at 13:09
Refresh options are listed in the query Properties dialog window. And on File Open is one of the options. You can also use a timer. And you could also program VBA event code if you want other triggers. — Ron Rosenfeld
– Ron Rosenfeld, Commented Oct 17, 2019 at 13:20

user6585761 · Accepted Answer · 2019-10-17 15:25:40Z

You can use FieldInfo, which is a parameter of TextToColumns, to specify the data type. For example, to specify Text for Column 1 and Column 4, you would have the following...

.Cells(intRow, 1).TextToColumns _
            Destination:=.Cells(intRow, 1), _
            DataType:=xlDelimited, _
            TextQualifier:=xlDoubleQuote, _
            ConsecutiveDelimiter:=False, _
            Tab:=False, _
            Semicolon:=False, _
            Comma:=True, _
            Space:=False, _
            Other:=False, _
            FieldInfo:=Array(Array(1, 2), Array(2, 1), Array(3, 1), Array(4, 2), Array(5, 1), _
                Array(6, 1), Array(7, 1), Array(8, 1), Array(9, 1), Array(10, 1), Array(11, 1))

For each pair within the array, the first number indicates the column number, and the second number the data type (1 = General; 2 = Text; etc . . .). For more details, have a look here.

Dschuli · Accepted Answer · 2019-10-17 09:34:10Z

0

I tried to set the format of the target cells to Text (works sometimes on e.g. copy operation), but that did not help either .....

Not an elegant solution, but worked for me when testing, is to correct any formulas introduced by the .TextToColumns operation afterwards. See below:

Dim cl As Range, rg As Range

and as part of your strLineLoop

On Error Resume Next
Set rg = .Rows(1).SpecialCells(xlFormulas)
If Err.Number = 0 Then                                      'Test if SpecialCells retuned something
    For Each cl In .Rows(intRow).SpecialCells(xlFormulas)
        cl.Value = Mid(cl.Formula, 2)                       'Get rid of the erreanously introduced "="
    Next cl
End If
On Error GoTo 0

answered Oct 17, 2019 at 9:34

Dschuli

3793 silver badges12 bronze badges

Collectives™ on Stack Overflow

Import CSV (UTF-8) to Excel without formatting

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related