CODEWITHSUNDEEP
vbaexcelcsv
Dave R
Dave R

Reputation: 21

VBA text to columns not parsing all data

I have two .csv files - in eyes they're identical, but something about them must be different. When data from the first one hits my text-to-columns macro, it does so only for part of the columns (first 39), skipping a portion (from 40 to the end going-right). This does not happen to data from the second .csv.

Here is a sample of the data from both (they're generated from two different databases):

QRT NAME;REPORTING YEAR /;SOLVENCY CALCULA;LEGAL NAME OF TH;HOME COUNTRY;REPORTING CURREN;PORTFOLIO TYPE;ASSET HELD IN UN;PORTFOLIO;LINE ID;ID CODE;ID CODE TYPE;ID CODE LONG TEX;ORIGINAL CURRENC;ISSUER;ISSUER MIDDLE TE;ISSUER SECTOR;ISSUER COUNTRY;ISSUER CODE;ISSUER GROUP;ISSUER GROUP MID;PARTICIPATION;CUSTODIAN;CUSTODIAN MIDDLE;COUNTRY OF CUSTO;CIC ASSET CATEGO;CIC SUB ASSET CA;ASSETS SUBCLASS;TYPE OF SECURITY;TYPE OF COLLATER;BALANCE SHEET IT;EQUITY TYPE;RATING AGENCY;EXTERNAL RATING;MATURITY DATE;DURATION;VALUATION METHOD;QUANTITY;TOTAL PAR AMOUNT;UNIT SII PRICE;PERCENTAGE OF PA;ACCRUED INTEREST;TOTAL SII AMOUNT;ACQUISITION PRIC;WRITE-OFFS/WRITE;TOTAL AMOUNT LOC;IFRS ACCOUNTING;CLEAN VALUE IN O;DIRTY VALUE IN O;EXPOSURE IN ORIG;EQUITY;PROPERTY;FX RISK (DOWNSID;FX RISK (UPSIDE;INTEREST RATE (D;INTEREST RATE (U;SPREAD RISK BOND;SPREAD RISK STRU;SPREAD RISK DERI;;USE OF DERIVATIV;LONG OR SHORT PO;UNWIND TRIGGER O;TRADE DATE;PREMIUM PAID TO;PREMIUM RECEIVED;SWAP OUTFLOW AMO;SWAP DELIVERED C;SWAP INFLOW AMOU;SWAP RECEIVED CU;TRIGGER VALUE;DELTA;NUMBER OF CONTRA;CONTRACT DIMENSI;NOTIONAL AMOUNT;MAXIMUM LOSS UND;ASSET PLEDGED AS;FUND NUMBER;MATCHING PORTFOL;CURRENT INTEREST;INTEREST RATE FR;REDEMPTION;REFERENCE RATE L;FIXING FACTOR;FIXING SPREAD;QUOTATION TYPE;INTEREST RATE TY;REDEMPTION TYPE;QUOTED;LISTING COUNTRY;ANNUITY/INSTALLM;DISCOUNTING SPRE;FUNDS: FIXED INC;FUNDS: EQUITY QU;FUNDS: PROPERTY;FUNDS: CASH QUOT;FUNDS: ALTERNATI;FUNDS: FX-QUOTA;FUNDS: SPREAD DU;FUNDS: DURATION;BALANCE SHEET IT;
S.06.02.01;006.2016;M1;C;PL;PLN;L;N;LF_COMP;1;PLOPNPL00013;ISIN;X SA;PLN;9978;X;G47;PL;2594002C0DGL8KBA5491;18284;X;N;9448;ING BANK SLASKI;PL;3;PL31;EQ-STOCKS;Equity;NA;EQUI-EQUI;TYPE1;;;;;QMP;4900,000000;;31,470000;;0,000000;154203,000000;160424,170000;;;154203,000000;154203,000000;154203,000000;;49098,510000;0,000000;0,000000;0,000000;0,000000;0,000000;0,000000;;;;;;U;;;;;;;;;;;;;;NC;;;;0,00;;0,00;;;Per unit;;;Quoted;PL;;;;;;;;;;;31;
S.06.02.01;006.2016;M1;C;PL;PLN;L;N;LF_COMP;3;PL_CLF_PLN_BRE§2510;CAU;TD_PL_CLF_BRE BANK_PLN;PLN;9357;MBANK;K64.1.9;PL;259400DZXF7UJKK2AY35;727;COMMERZBANK;;9357;MBANK;PL;7;XT73;CASH-TD;Deposit;FIN-SEN;CASH-CASH;TYPE2;MDY;BBB;30.12.2016;0,500000;AVM;;448000,000000;;1,000000;4963,960000;452963,960000;;;;448000,000000;448000,000000;452963,960000;;0,000000;0,000000;0,000000;0,000000;-2655,240000;2436,920000;0,000000;;;;;;U;;;;;;;;;;;;;;NC;;;0,02210;0,00;;0,00;;;Percent;;;(none);;;;;;;;;;;;41;


QRT NAME;REPORTING YEAR /;SOLVENCY CALCULA;LEGAL NAME OF TH;HOME COUNTRY;REPORTING CURREN;PORTFOLIO TYPE;ASSET HELD IN UN;PORTFOLIO;LINE ID;ID CODE;ID CODE TYPE;ID CODE LONG TEX;ORIGINAL CURRENC;ISSUER;ISSUER MIDDLE TE;ISSUER SECTOR;ISSUER COUNTRY;ISSUER CODE;ISSUER GROUP;ISSUER GROUP MID;PARTICIPATION;CUSTODIAN;CUSTODIAN MIDDLE;COUNTRY OF CUSTO;CIC ASSET CATEGO;CIC SUB ASSET CA;ASSETS SUBCLASS;TYPE OF SECURITY;TYPE OF COLLATER;BALANCE SHEET IT;EQUITY TYPE;RATING AGENCY;EXTERNAL RATING;MATURITY DATE;DURATION;VALUATION METHOD;QUANTITY;TOTAL PAR AMOUNT;UNIT SII PRICE;PERCENTAGE OF PA;ACCRUED INTEREST;TOTAL SII AMOUNT;ACQUISITION PRIC;WRITE-OFFS/WRITE;TOTAL AMOUNT LOC;IFRS ACCOUNTING;CLEAN VALUE IN O;DIRTY VALUE IN O;EXPOSURE IN ORIG;EQUITY;PROPERTY;FX RISK (DOWNSID;FX RISK (UPSIDE;INTEREST RATE (D;INTEREST RATE (U;SPREAD RISK BOND;SPREAD RISK STRU;SPREAD RISK DERI;;USE OF DERIVATIV;LONG OR SHORT PO;UNWIND TRIGGER O;TRADE DATE;PREMIUM PAID TO;PREMIUM RECEIVED;SWAP OUTFLOW AMO;SWAP DELIVERED C;SWAP INFLOW AMOU;SWAP RECEIVED CU;TRIGGER VALUE;DELTA;NUMBER OF CONTRA;CONTRACT DIMENSI;NOTIONAL AMOUNT;MAXIMUM LOSS UND;ASSET PLEDGED AS;FUND NUMBER;MATCHING PORTFOL;CURRENT INTEREST;INTEREST RATE FR;REDEMPTION;REFERENCE RATE L;FIXING FACTOR;FIXING SPREAD;QUOTATION TYPE;INTEREST RATE TY;REDEMPTION TYPE;QUOTED;LISTING COUNTRY;ANNUITY/INSTALLM;DISCOUNTING SPRE;FUNDS: FIXED INC;FUNDS: EQUITY QU;FUNDS: PROPERTY;FUNDS: CASH QUOT;FUNDS: ALTERNATI;FUNDS: FX-QUOTA;FUNDS: SPREAD DU;FUNDS: DURATION;BALANCE SHEET IT;
S.06.02.01;006.2016;M1;C;PL;PLN;L;N;LF_COMP;1;PLOPNPL00013;ISIN;X SA;PLN;9978;X;G47;PL;2594002C0DGL8KBA5491;18284;X;N;9448;ING BANK SLASKI;PL;3;PL31;EQ-STOCKS;Equity;NA;EQUI-EQUI;TYPE1;;;;;QMP;4900,000000;;31,470000;;0,000000;154203,000000;160424,170000;;;154203,000000;154203,000000;154203,000000;;49098,510000;0,000000;0,000000;0,000000;0,000000;0,000000;0,000000;;;;;;U;;;;;;;;;;;;;;NC;;;;0,00;;0,00;;;Per unit;;;Quoted;PL;;;;;;;;;;;31;
S.06.02.01;006.2016;M1;C;PL;PLN;L;N;LF_COMP;3;PL_CLF_PLN_BRE§2510;CAU;TD_PL_CLF_BRE BANK_PLN;PLN;9357;MBANK;K64.1.9;PL;259400DZXF7UJKK2AY35;727;COMMERZBANK;;9357;MBANK;PL;7;XT73;CASH-TD;Deposit;FIN-SEN;CASH-CASH;TYPE2;MDY;BBB;30.12.2016;0,500000;AVM;;448000,000000;;1,000000;4963,960000;452963,960000;;;;448000,000000;448000,000000;452963,960000;;0,000000;0,000000;0,000000;0,000000;-2655,240000;2436,920000;0,000000;;;;;;U;;;;;;;;;;;;;;NC;;;0,02210;0,00;;0,00;;;Percent;;;(none);;;;;;;;;;;;41;

Here is the text-to-columns formula I use:

note: data is copied from InputBook then the text-to-columns happens in ParamBook

Public Sub Load_input()

InputPath = Application.GetOpenFilename("Comma Separated Values (*.csv),*.csv")

Call Fetch_Data

End Sub


Sub Fetch_Data()
    Dim InputBook As Workbook
    Dim ParamBook As Workbook
    Dim n As Integer
    Dim K As Integer
    Set InputBook = Workbooks.Open(InputPath)
    Set ParamBook = ThisWorkbook


    '==========CLEAR CONTENTS OF DATA SHEET==========
    If ParamBook.Sheets("Data").Cells(1, 1) <> "" Then
        ParamBook.Sheets("Data").Cells.Clear
    End If

'==========COPY INPUT AND PASTE INTO DATA SHEET==========
n = InputBook.Sheets(1).Cells(Rows.Count, "A").End(xlUp).Row
InputBook.Activate
InputBook.Sheets(1).Range(Cells(1, 1), Cells(n, 1)).Select
Selection.Copy
ParamBook.Activate
Application.DisplayAlerts = False
ActiveSheet.Paste Destination:=Worksheets("Data").Range("A1")


'==========TEXT TO COLUMSN IN DATA SHEET==========
K = ParamBook.Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
ThisWorkbook.Sheets("Data").Activate
For K = 1 To K
    Application.DisplayAlerts = False
    ThisWorkbook.Sheets("Data").Cells(K, 1).Select
    Selection.TextToColumns DataType:=xlDelimited, Semicolon:=True, Comma:=False
Next K


InputBook.Close
End Sub

If there is something I can do with the code I'd appreciate some tips, I'd rather not mess with how the .csv files are generated.

edit:

This text-to-columns code just works faster, but still the same issue.

'==========TEXT TO COLUMSN IN SAP_BW_DATA SHEET==========
K = ParamBook.Sheets("SAP BW Data").Cells(Rows.Count, "A").End(xlUp).Row
ThisWorkbook.Sheets("SAP BW Data").Activate
    ThisWorkbook.Sheets("SAP BW Data").Range(Cells(1, 1), Cells(K, 1)).Select
    Selection.TextToColumns DataType:=xlDelimited, Semicolon:=True, Comma:=False

edit 2:

OK, I have narrowed it down - turns out, the problem does not lie with TtC functionality, but the way the data is pasted from the .csv file. The way it is currently set up only grabs a portion of the data (the same part which is being text-to-column'd), the remainder is not being pasted. Solving that will help the issue. Thanks everyone who participated in helping!

edit 3:

@cxw has fixed the issue, please see his post!

Upvotes: 1

Views: 1428

Answers (2)

cxw
cxw

Reputation: 17041

Edit See the actual answer.

The problem is probably this line:

For K = 1 To K

You are changing the loop bound as you loop, which is almost never what you want :) . (Correction See Comintern's comment below.) (If you wanted flexible bounds, a Do loop would be a better choice.) Try

LastRow = ParamBook.Sheets("Data").Cells(Rows.Count, "A").End(xlUp).Row
ThisWorkbook.Sheets("Data").Activate  ' <--- should this reference ParamBook?
Dim CurrentRow as Long
Application.DisplayAlerts = False  ' move out of the loop for speed
For CurrentRow = 1 To LastRow
    ThisWorkbook.Sheets("Data").Cells(CurrentRow, 1).Select
    Selection.TextToColumns DataType:=xlDelimited, Semicolon:=True, Comma:=False
Next CurrentRow

Also, your current code references three workbooks: InputBook, ParamBook, and ThisWorkbook (where the macro lives). Edit I see you have initialized ParamBook to be a copy of ThisWorkbook. I recommend that you stick with ParamBook throughout, then, as it will make it easier to port the code to an addin should you need to later on.

Upvotes: 1

cxw
cxw

Reputation: 17041

OK — let's try this again. When the obvious answers fail, check your inputs.

Set InputBook = Workbooks.Open(InputPath)

opens InputPath in the default mode. You called these "csv" files, but really they are semicolon delimited, per your macro. I suspect the two files have different extensions, and the problematic one is a *.csv. Yes?

I just tried this on my Excel 2013 installation. When I run the above line on a *.csv file containing your first test case above, Excel breaks it into columns at the commas. Therefore, the data in the InputBook spans columns A:S, so your code only pulling out of column A wouldn't get all the data. To fix this, I used

Set InputBook = Workbooks.Open(InputPath, format:=5)

Format 5 is "no delimiter" per the docs. When I do that, I get each row entirely in column A, and a manual text-to-columns works.

Edit Yes, extension does matter: When I rename my test file from .csv to .txt, a regular Workbooks.Open with no format specified doesn't split the columns.

Upvotes: 1

Related Questions