how assign Data Types for data columns in CSV file when import the same to Excel?

sameerasnd

New Member
Hi All,

I need to import CSV file to the Excel work sheet while assigning data types for data columns in CSV file. For that I created Excel macro code like bellow and it was succeeded in Excel.

With ActiveSheet.QueryTables.Add(Connection:= _
"TEXT;C:\Users\sandaruwans\Documents\RPA_Finance\DP Automation\a.txt", _
Destination:=Range("$A$1"))

.Name = "a"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = xlDelimited
.TextFileTextQualifier = xlTextQualifierDoubleQuote
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = True
.TextFileSemicolonDelimiter = False
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = "|"
.TextFileColumnDataTypes = Array(2, 2, 2, 2, 1, 2, 1, 2, 4)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With
End Sub

so I try to changed duplicated Import CSv Action in Excel VBO with the bold item above but It gives following error when check the code.
Compiler error at line 32: 'Array' is a type and cannot be used as an expression.

Please help me on this regards.
Thanks
sameera.
 

Jiri.Hurda

New Member
Hi, I did it this way as I don't need to have different formats at every column. I guess you can change the code to work with input collection with formats.

My code isn't perfect as it solves the exact situation and not the general need.
I hope it helps
Jiri


Dim dw, ds, dr, qt As Object
Dim MyArray(NumberOfColumns) As Integer 'Array
Dim i as Integer

Try

dw = GetWorkbook(Handle, Workbook)
ds = GetWorksheet(Handle, Workbook, Worksheet)
dr = ds.Range(Range)
qt = ds.QueryTables.Add(Connection:="TEXT;" & Path, Destination:=dr)

For i = 0 To NumberOfColumns 'I had to format 28 columns, numbering starts from 0, so input to code stage is 27
MyArray(i) = FormatOfColumns 'I had to format all columns as text, so I have input = 2
Next i

With qt
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = 1 'xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.TextFilePromptOnRefresh = False
.TextFilePlatform = 437
.TextFileStartRow = 1
.TextFileParseType = 1 'xlDelimited
.TextFileTextQualifier = Qualifier
.TextFileConsecutiveDelimiter = False
.TextFileTabDelimiter = False
.TextFileSemicolonDelimiter = True
.TextFileCommaDelimiter = False
.TextFileSpaceDelimiter = False
.TextFileOtherDelimiter = Delimiter 'as per chosen Delimiter, I had "/"
.TextFileColumnDataTypes = MyArray
.TextFileTrailingMinusNumbers = True
End With
qt.Refresh(False)

Success = True

Catch e As Exception
Success = False
Message = e.Message
Finally
dw = Nothing
ds = Nothing
dr = Nothing
qt = Nothing
End Try


View attachment 1596522326581.png
 
Last edited:
Top