Convert from Text to Columns Doesn't Work if Source is Modified

[D_Marcel]

Hello VBAX friends!
I'm facing a very strange issue when importing "*.csv" files to Excel without open them. Basically, I need to import always the newest file of a directory, so after writing the desired instructions, I'm performing the following test:

1. Import the only file in the directory, let's call it "original file".
2. Open it, delete some rows and save it in the same directory, only to ensure that the macro will import this file, as this file is now the newest one.

Step 1 - CSV imported successfully;
Step 2 - The "raw data" of the CSV file is placed in the first column, but is not being converted from text to column.

I've read about all the properties of QueryTable at MSDN and performed some changes in the code, but the error persists.

After got the newest file:

On Error GoTo ERROR_HANDLING1
LV_NEWEST_FILE = NEWESTFILE(LV_FILE_PATH, "*.csv", "Path")
Call IMPORT_TXT_FILE(LV_NEWEST_FILE, GV_PREVIEW_TABLE)

I'm calling a sub only to import the CSV:

Sub IMPORT_TXT_FILE(ByVal SOURCE_PATH As String, TARGET_SHEET As Worksheet)

TARGET_SHEET.Select
With TARGET_SHEET.QueryTables.Add(Connection:="TEXT;" & SOURCE_PATH, DESTINATION:=Range("A1"))
.Name = "CAPTURE"
.FieldNames = True
.RowNumbers = False
.FillAdjacentFormulas = False
.PreserveFormatting = True
.RefreshOnFileOpen = False
.RefreshStyle = xlOverwriteCells
.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
.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
.TextFileTrailingMinusNumbers = True
.Refresh BackgroundQuery:=False
End With


End Sub

I was trying right now to provide some samples, but the original file has more than 100.000 rows of confidential data, and if I delete them in order to allow a test and/or debugging, we will fall in the step 2, because as I said, any modifications in the original file causes the conversion from text to columns to not work.

Anyone has some idea of why this is happening, or alternative ways to import the CSV file without opening it?

Thanks a lot!

Regards,

Douglas

[mana]

>.TextFileCommaDelimiter = False

Why?

[D_Marcel]

Hello mana, thanks for replying.
Because according to MSDN, this propertie should be used when the comma is the delimiter:

19934

This propertie is related to this field where the mouse is pointing:

19935

Despite it's a CSV file, the only delimiter is the tab.
Changing the value to True, cells with decimals numbers will cause rows to displaced to right.

[mana]

Please post your CSV file.

[mdmackillop]

Please post your CSV file.
Please post only a few lines your 100k rows to show the setup. Change any confidential information

[D_Marcel]

Greetings!
mana and mdmackillop, thanks a lot!

I'm sending a sample of the same .CSV file, with 20 rows.
I've performed the macro with this sample and the error occurs. The raw data is imported but it's not converted from text to columns.

While I don't figure out what's happening, I'm using this as a turnaround:

If Cells(1, 2).Value = "" Then Range(Cells(1, 1), Cells(Cells(Rows.Count, 1).End(xlUp).Row, 1)).Select
Selection.TextToColumns DESTINATION:=Range("A1"), DataType:=xlDelimited, _
TextQualifier:=xlDoubleQuote, ConsecutiveDelimiter:=False, Tab:=True, _
Semicolon:=False, Comma:=False, Space:=False, Other:=False, TrailingMinusNumbers:=True
End If


Regards,

Douglas Marcel

[mana]

>.TextFileTextQualifier = xlTextQualifierDoubleQuote

.TextFileTextQualifier = xlTextQualifierNone

[D_Marcel]

I've tested here and now it really works. The only problem is that in the first column, excel adds quotation marks in all rows, but I can solve it by using replace function.

19953

Thanks!