CODEWITHSUNDEEP
excelcsvvbscriptxlsx
user3657176
user3657176

Reputation: 3

Convert CSV to Excel table

I'm trying to convert a CSV file to xls.

The CSV file looks like this:

410E   54B9    C700    0   0   0   0

but when the convert is done the result is

410E;54B9;C700;0;0;0;0
Const xlDelimited                =  1
Const xlTextQualifierDoubleQuote =  1
Const xlOpenXMLWorkbook          = 51

Set xl = CreateObject("Excel.Application")

xl.Workbooks.OpenText "C:\sssss.csv", , , xlDelimited _
  , xlTextQualifierDoubleQuote, True, True, True, True, True, True, _
  , Array(Array(1,2), Array(2,2), Array(3,2), Array(4,1), Array(5,2) _
  , Array(6,1), Array(7,1), Array(8,1), Array(9,1), Array(10,1), Array(11,1))
Set wb = xl.ActiveWorkbook

wb.SaveAs "C:ssss.xlsx", xlOpenXMLWorkbook, , , , False
wb.Close

xl.Quit

I want it as a table.

Upvotes: 0

Views: 504

Answers (2)

Andy Wynn
Andy Wynn

Reputation: 1273

You are using a lot more arguments than you need, this should do the job:

Workbooks.OpenText Filename:="C:\ssss.csv", _
    StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=True, Space:=True

Having said that, if you are using a different type of space character in your CSV than the normal space character you may need to use this:

Workbooks.OpenText Filename:="C:\ssss.csv", _
    StartRow:=1, DataType:=xlDelimited, TextQualifier:=xlDoubleQuote, _
    ConsecutiveDelimiter:=True, Other:=True, OtherChar:=" "

Copy and paste the space character from the CSV to between the double quotes after OtherChar.

But also, as Ansgar said, if the file extension is .csv, then Excel ignores all of those rules and just opens the file as a CSV.

You can rename the file with Name, or you can continue to open the file and add a text to columns function straight after the import:

Range("A:A").TextToColumns Destination:=Range("A:A"), DataType:=xlDelimited, _
        TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=True, Tab:=False, _
        Semicolon:=False, Comma:=False, Space:=True, Other:=True, OtherChar:=";"

Hope this helps!

Upvotes: 1

Ansgar Wiechers
Ansgar Wiechers

Reputation: 200573

Excel is very particular about what it accepts as CSV. Rename the file to something other than .csv (e.g. C:\sssss.txt) and the code should work as you expect. Otherwise you need to change the content of the file (substitute the semicolons with commas) to match Excel's expectations.

Upvotes: 1

Related Questions