Open a CSV ; File in multiple columns

[drom]

Hi and thanks in advance!


Is there any easy way of opening a CSV File from VBA once you know the FullName but in multiple columns

If I use:

Sub OpenCSVFile()
Dim wFxFN As String: wFxFN = "C:\Users\HiHello\Desktop\GodSaveTheQueen\CSV Files\AAA_202007.csv"
                    Workbooks.Open Filename:=wFxFN
End Sub

I will Open the CSV File in a new Workbook but the CSV's Info is gonna be in a single Column, (Column A)

Is there a way to open the File From VBA in multiple Columns, the same way we do from DATA\Get External Data\From Text\...
Or the same way we see the File when opening directly from Windows Explorer

 

[John_w]

Is there a way to open the File From VBA in multiple Columns, the same way we do from DATA\Get External Data\From Text\...

Record a macro whilst doing those steps. The generated code can edited as required.

 

[drom]

Yes but when I record a macro opening a CSV file from Excel (Open...), the new file comes with many columns
If I close the opened file and I run the macro again, the new file comes with a single column, 1 column
So I do not know how can I open a CSV file with the correct number of columns, I mean

If 27 columns then 27 columns (not 1)​

If 13 columns then 13 columns (not 1)​

Thanks!

 

[Joe4]

Yes but when I record a macro opening a CSV file from Excel (Open...), the new file comes with many columns

Did you turn on the Macro before you open it for the first time (when it does it correctly)?
What does the VBA code you recorded look like (post it here)?
Also, can you open the CSV file in a text editor (not Excel) and post the first few rows of data here, so we can see what the data truly looks like?

 

[drom]

Did you turn on the Macro before you open it for the first time (when it does it correctly)?
What does the VBA code you recorded look like (post it here)?
Also, can you open the CSV file in a text editor (not Excel) and post the first few rows of data here, so we can see what the data truly looks like?

YES.

Sub Macro2()
     'WORKS FINE when RECORDING THE MACRO, but when I run this macro, NO
    Workbooks.Open Filename:="C:\Users\HiHello\Desktop\GodSaveTheQueen\CSV Files\AAA_202007.csv"
End Sub

First 4 rows:

CREATED;ACCOUNT_ID;ACCOUNT_NAME;ACCOUNT_USERNAME;DIRECTION;SRC_IP;USER_AGENT;SRC;DST;DURATION;BILLSEC;TOTAL_SELL_PRICE;TOTAL_BUY_PRICE;PREFIX_GROUP_ID;PREFIX_GROUP_NAME;RATE_MATCH;RATE_MATCH_NAME;SELL_FLAT_RATE_ID;ID;A_LEG_UUID;HANGUP_CAUSE;HANGUP_CAUSE_Q850;ACCOUNT_REF;DIVERSION_NUMBER;PROVIDER;FAILS
29/07/2020 8:54;000000005bc88b4e015bc92c4e8b0025;Ip Telecom;7	59097E+12;OUTBOUND;109.167.126.200;VozManager-DpServer/sip1 (2.15.1);34692157615;34616367273;38;0;0;0;000000005b8a3ee2015b8d5f779300fc;Espa?a M?vil;34616;Movil TELEFONICA MOVILES;000000005bcd335d015bcdf6bc161ae9;6709e7c836314e3badf56968458fd9d6;5394c742-1dc8-4e1a-b890-eb2ceba5ced2;USER_BUSY;17;;34918981509;Siptize;0
29/07/2020 9:08;000000005c83562f015cad8243f62bf3;Sastre Quiros Gestoría;6	35049E+12;OUTBOUND;109.167.99.122;VozManager-Vpbx-FS/pbx6 (19.15.0);34920227103;34918972504;55;55;0;0;000000005b8a3ee2015b8d5f77d00111;Espa?a Fijo;3491897;Nacional TF (Madrid);000000005bcd335d015bcdf6bc161ae9;6983a7b351844ac6a0c47c7fa538b7a2;c27e55de-1b11-4da9-9d0c-1acb4720d08c;NORMAL_CLEARING;16;;;;
29/07/2020 9:11;000000005c83562f015cad8243f62bf3;Sastre Quiros Gestoría;6	35049E+12;OUTBOUND;109.167.99.122;VozManager-Vpbx-FS/pbx6 (19.15.0);34918972504;34918970874;143;136;0;0	01337;000000005b8a3ee2015b8d5f77d00111;Espa?a Fijo;3491897;Nacional TF (Madrid);000000005bcd335d015bcdf6bc161ae9;2d5b7579fdbb4f71a5a65ba34d6d427c;c70dd168-4b9a-4405-8393-150b7bb2c78c;NORMAL_CLEARING;16;;;Siptize;0

In any case the CSV comes with 26 Columns, comes max with 4 columns, and Sometimes the CSV has 52 columns when Opened recording a macro first time or just when opened from Window explorer
Actually when Opened from windows explorer the file comes Perfect
And when Opened from


DATA\Get External Data\From Text\C:\Users\HiHello\Desktop\GodSaveTheQueen\CSV Files\AAA_202007.csv
The File comes perfect

And the Macro when Coming from :
DATA\Get External Data\From Text\C:\Users\HiHello\Desktop\GodSaveTheQueen\CSV Files\AAA_202007.csv


My Problems Comes because I do not know the FINAL Number of COLUMNS
If I know the Columns... no problem, but when is dinamic??

Sub Macro6()  'WORKS FINE for 26 COLUMN'S CSV FILES
'I Have modified this Macro but Works FINE for 26 Columns CSV files
On Error Resume Next
  Dim wFxFN As String: wFxFN = "TEXT;C:\Users\HiHello\Desktop\GodSaveTheQueen\CSV Files\AAA_202007.csv"
Dim aMyArray():            aMyArray = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
                                      'But aMyArray is DINAMIC
    ActiveWorkbook.Worksheets.Add
    With ActiveSheet.QueryTables.Add(Connection:=wFxFN, Destination:=Range("$A$1"))
        .CommandType = 0
        .Name = "cdr8"
        .FieldNames = True
        .PreserveFormatting = True
        .RefreshStyle = xlInsertDeleteCells
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .TextFilePlatform = 1252
        .TextFileStartRow = 1
        .TextFileParseType = xlDelimited
        .TextFileTextQualifier = xlTextQualifierDoubleQuote
        .TextFileTabDelimiter = True
        .TextFileSemicolonDelimiter = True
       
       
        'This is DINAMIC, so here comes my problem
        'I do not know how many columns is gonna have my next CSV file
        'I can Create a Array named say aMyArray and put
        'I can Use the following row if the CSV comes with 26 columns
        .TextFileColumnDataTypes = aMyArray
        '.TextFileColumnDataTypes = Array(1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1, 1)
       
       
        'But when the CSV File comes with 30, or 50, or 71 ???
       
        .TextFileTrailingMinusNumbers = True
        .Refresh BackgroundQuery:=False
    End With


End Sub

Thanks again!

 

[Joe4]

If I know the Columns... no problem, but when is dinamic??

I think if you just do it for the maximum number of columns (did you say it was 52)? Then it should work.
I don't think it should be a problem if the code tries to split more columns that there really are on any particular file.

 

[sandy666]

maybe count ; for each line then max from all lines and it will give you number of columns

 

[John_w]

Try deleting .TextFileColumnDataTypes = aMyArray. If TextFileColumnDataTypes isn't specified it will import however many columns are in the file.

 

[drom]

Try deleting .TextFileColumnDataTypes = aMyArray. If TextFileColumnDataTypes isn't specified it will import however many columns are in the file.

YES!

Many Thanks! ?