VERY long fields in CSV file - QueryTable doesn't import properly, UI Power Query does

[Buzzcut]

This is odd. My VBA imports the file OK but the very long fields spill over and overwrite all subsequent columns on that row with blanks:

ReDim ColumnsType(16383)
    For i = 0 To 16383
        ColumnsType(i) = 2
    Next i
    
    With wbNew.ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FileToOpen, Destination:=wbNew.ActiveSheet.Range("A1"))
        .PreserveFormatting = True
        .RefreshStyle = xlOverwriteCells
        .AdjustColumnWidth = True
        .TextFileParseType = xlDelimited
        .TextFilePlatform = Encoding
        .TextFileTabDelimiter = True
        .TextFileCommaDelimiter = False
        .TextFileColumnDataTypes = ColumnsType
        .Refresh BackgroundQuery:=False
    End With

If I go the UI Data-From Text/CSV route then the data imports fine with no spilling. The recorded Macro however isn't that useful.
I don't see any way of uploading my test file, it's only 2 rows and 270KB

This is after going the manual route, all the columns appear normally:

and this is using the VBA above, you can see that E1 (~107k characters) and D2 (~53k characters) have overwritten everything in the columns after.

I tried to skip the fields in the VBA by using the below on my real file. The columns are skipped but the data is still missing, I think it must be importing then deleting.

.TextFileColumnDataTypes = Array(2, 9, 9, 9, 2......)

Does anyone have an idea how to get the VBA working properly with these long fields?

Thanks...Buzz

 

[rlv01]

You say the import file is CSV, but your VBA query settings look like you have it set up for tab-delimited, not comma-delimited.
Try this instead (not tested)

    With wbNew.ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FileToOpen, Destination:=wbNew.ActiveSheet.Range("A1"))
        .PreserveFormatting = True
        .RefreshStyle = xlOverwriteCells
        .AdjustColumnWidth = True
        .TextFileParseType = xlDelimited
        .TextFilePlatform = Encoding
        .TextFileTabDelimiter = False 'True
        .TextFileCommaDelimiter = True 'False
        .TextFileColumnDataTypes = ColumnsType
        .Refresh BackgroundQuery:=False
    End With

 

[Buzzcut]

You say the import file is CSV, but your VBA query settings look like you have it set up for tab-delimited, not comma-delimited.
Try this instead (not tested)

    With wbNew.ActiveSheet.QueryTables.Add(Connection:="TEXT;" & FileToOpen, Destination:=wbNew.ActiveSheet.Range("A1"))
        .PreserveFormatting = True
        .RefreshStyle = xlOverwriteCells
        .AdjustColumnWidth = True
        .TextFileParseType = xlDelimited
        .TextFilePlatform = Encoding
        .TextFileTabDelimiter = False 'True
        .TextFileCommaDelimiter = True 'False
        .TextFileColumnDataTypes = ColumnsType
        .Refresh BackgroundQuery:=False
    End With

Yes, the CSV file is actually tab separated, sorry. The VBA works fine for any other file, just not for those which have very long values.

 

[rlv01]

If I go the UI Data-From Text/CSV route then the data imports fine with no spilling. The recorded Macro however isn't that useful.

Why isn't it useful? If it recorded a successful import it sounds like it would be very useful.

 

[Buzzcut]

Why isn't it useful? If it recorded a successful import it sounds like it would be very useful.

You're right! After reading your comment I looked at it again and it seems doable. My real file has over 100 columns so the query is massive.
I'll have to see if I can push my variables in there somehow though, like filename and Destination. It looks like those might be the only ones.
Thanks for pushing me back to the Macro.

ActiveWorkbook.Queries.Add Name:="test", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""C:\Users\U6065108\Downloads\test.csv""),[Delimiter=""   "", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""a"", Int64.Type}, {""b"", type text}, {""c""" & _
        ", type text}, {""d"", type text}, {""e"", type text}, {""f"", type text}, {""g"", type date}, {""h"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=test;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [test]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "test"
        .Refresh BackgroundQuery:=False

 

[Buzzcut]

You're right! After reading your comment I looked at it again and it seems doable. My real file has over 100 columns so the query is massive.
I'll have to see if I can push my variables in there somehow though, like filename and Destination. It looks like those might be the only ones.
Thanks for pushing me back to the Macro.

ActiveWorkbook.Queries.Add Name:="test", Formula:= _
        "let" & Chr(13) & "" & Chr(10) & "    Source = Csv.Document(File.Contents(""C:\Users\U6065108\Downloads\test.csv""),[Delimiter=""   "", Columns=8, Encoding=1252, QuoteStyle=QuoteStyle.None])," & Chr(13) & "" & Chr(10) & "    #""Promoted Headers"" = Table.PromoteHeaders(Source, [PromoteAllScalars=true])," & Chr(13) & "" & Chr(10) & "    #""Changed Type"" = Table.TransformColumnTypes(#""Promoted Headers"",{{""a"", Int64.Type}, {""b"", type text}, {""c""" & _
        ", type text}, {""d"", type text}, {""e"", type text}, {""f"", type text}, {""g"", type date}, {""h"", type text}})" & Chr(13) & "" & Chr(10) & "in" & Chr(13) & "" & Chr(10) & "    #""Changed Type"""
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.ListObjects.Add(SourceType:=0, Source:= _
        "OLEDB;Provider=Microsoft.Mashup.OleDb.1;Data Source=$Workbook$;Location=test;Extended Properties=""""" _
        , Destination:=Range("$A$1")).QueryTable
        .CommandType = xlCmdSql
        .CommandText = Array("SELECT * FROM [test]")
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = False
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .ListObject.DisplayName = "test"
        .Refresh BackgroundQuery:=False

I've got this working now after feeding the "test" name and filename through to make it reusable. One major gotcha seemed to be that the macro didn'tpick up on the tab delimiter properly and listed it as 5 spaces, I had to change it to this. It was giving a constant error on the last line.

[Delimiter=""" & vbTab & """