Hi all
I am working with data in a text file (LS-Dyna keyword format). Most of the fields that I'm interested in are 10-wide. When I used the "437: OEM United Sates" file origin and "---Custom---" delimiter, it mostly imports correctly. However, there is one specific situation where it imports incorrectly. This is when there is no space between the characters in two adjacent fields. Here is an example:
In the text-file vs how it imports in the workbook:
Here is the VBA code that I use for the import:
Sub kfile_sample_import()
'
' kfile_sample_import Macro
'ActiveWorkbook.Queries.Add Name:="kfile_sample", Formula:= _
"let" & Chr(13) & "" & Chr(10) & " Source = Csv.Document(File.Contents(""C:\Users\jopot\OneDrive\Documents\Timestep\Resources\IT\Software\LS-Dyna\Methodology investigations\Welds\0125-FEA-I-S\kfile_sample.k""),9,"""",ExtraValues.Ignore,437)," & Chr(13) & "" & Chr(10) & " #""Changed Type"" = Table.TransformColumnTypes(Source,{{""Column1"", type text}, {""Column2"", type text}, {""Column3"", type text}, {""Column4"", " & _
"type text}, {""Column5"", type text}, {""Column6"", type text}, {""Column7"", type text}, {""Column8"", type text}, {""Column9"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & " #""Changed Type"""
With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
"OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=kfile_sample;Extended Properties=""""" _
, Destination:=Range("$A$1")).QueryTable
.CommandType = xlCmdSql
.CommandText = Array("SELECT * FROM [kfile_sample]")
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.BackgroundQuery = True
.RefreshStyle = xlInsertDeleteCells
.SavePassword = False
.SaveData = True
.AdjustColumnWidth = True
.RefreshPeriod = 0
.PreserveColumnInfo = True
.ListObject.DisplayName = "kfile_sample"
.Refresh BackgroundQuery:=False
End With
End Sub
I have placed the sample input file and workbook at the OneDrive link below:
https://1drv.ms/u/s!Aqj7uRefFoRzhZ5jtFpXYrb7xQSQTg?e=GsYBC1
Does anyone know how I can avoid this issue please?
Thanks!
JP