Code only returns top line of CSV file

JackDanIce

Well-known Member
Joined
Feb 3, 2010
Messages
9,922
Office Version
  1. 365
Platform
  1. Windows
Hi,

I've found the following pieces of code to read data from a CSV file into Excel (PowerQuery is far too slow) but both are returning the correct number of rows but row 1 repeated instead of the actual data.

It suggests I need to loop, but from these two links I can't see what's missing

Version1
VBA Code:
Private Sub Read_Data1()

    Dim LineItems       As Variant
    Dim LineFromFile    As String
    Dim s               As Double: s = Timer
    
    Open wSettings.Range("Data_Path").Value For Input As #1
    Line Input #1, LineFromFile
    LineFromFile = Replace(LineFromFile, Chr(34), vbNullString)
    LineItems = Split(LineFromFile, ";")
    
    wData.Cells(1, 1).Resize(UBound(LineItems) + 1).Value = LineItems
    
    Debug.Print "Run Time 1 : " & Round(Timer - s, 2)

End Sub
Version2
VBA Code:
Private Sub Read_Data2()

    Dim data    As Variant
    Dim s       As Double: s = Timer
    
    data = Split(CreateObject("Scripting.FileSystemObject").getFile(wSettings.Range("Data_Path").Value).openastextstream.readall, vbCrLf)
    With wData.Cells(1, 1).Resize(UBound(data) + 1)
        .Value = data
        .TextToColumns , xlDelimited, xlTextQualifierNone, False, False, False, True, False, False
    End With
    
    Debug.Print "Run Time 2 : " & Round(Timer - s, 2)

End Sub
Both return the same results of the header row repeating for all data rows and the data file definitely has distinct table data to header row.

Any suggestions?

TIA,
Jack
 

Excel Facts

Difference between two dates
Secret function! Use =DATEDIF(A2,B2,"Y")&" years"&=DATEDIF(A2,B2,"YM")&" months"&=DATEDIF(A2,B2,"MD")&" days"
If it's a 1D array, then you will need to transpose it.
 
Upvote 0
I had that but it generated run-time error '1004'
Code:
wData.Cells(1, 1).Resize(UBound(LineItems) + 1).Value = Application.Transpose(LineItems)
At this point, in Locals window Variant/String(0 to 26) for LineItems
 
Upvote 0
With your 2nd code, this works for me
VBA Code:
    With wData.Cells(1, 1).Resize(UBound(data) + 1)
        .Value = Application.Transpose(data)
        .TextToColumns , xlDelimited, xlTextQualifierNone, False, False, False, True, False, False
    End With
but it will depend on the data
 
Upvote 0
Still same result here unfortunately.

This is the raw CSV file I'm using where columns L:BZC have been deleted, but apart from column A (date), all other columns are positive whole numbers.

Just the top row is repeating for all rows in the data pull.

Putting an "x" or any string into A2 doesn't help but it is an empty cell in the raw file:

(The file originated from a US PC, but opened on a UK PC, hence date column showing a switch from date to text after May 12th as it doesn't recognise 13 as a month value):

Facebook Insights Data Export - TEST - 2020-06-28.csv
ABCDEFGHIJ
1DateLifetime Total LikesDaily New LikesDaily UnlikesDaily Page Engaged UsersWeekly Page Engaged Users28 Days Page Engaged UsersDaily Total ReachWeekly Total Reach28 Days Total Reach
2Lifetime: The total number of people who have liked your Page. (Unique Users)Daily: The number of new people who have liked your Page (Unique Users)Daily: The number of Unlikes of your Page (Unique Users)Daily: The number of people who engaged with your Page. Engagement includes any click or story created. (Unique Users)Weekly: The number of people who engaged with your Page. Engagement includes any click or story created. (Unique Users)28 Days: The number of people who engaged with your Page. Engagement includes any click or story created. (Unique Users)Daily: The number of people who had any content from your Page or about your Page enter their screen. This includes posts, check-ins, ads, social information from people who interact with your Page and more. (Unique Users)Weekly: The number of people who had any content from your Page or about your Page enter their screen. This includes posts, check-ins, ads, social information from people who interact with your Page and more. (Unique Users)28 Days: The number of people who had any content from your Page or about your Page enter their screen. This includes posts, check-ins, ads, social information from people who interact with your Page and more. (Unique Users)
305/01/202010301131171488018395174244794194618
405/02/2020104001012409412017668482739853188092
505/03/2020106402462766387317352777735021185016
605/04/2020108882473561370816795631631746180203
705/05/2020111222273454340716497601327618177577
805/06/202011307188795345516182850529167172996
905/07/2020115262256660324516011686629889167396
1005/08/2020116901665456342315644691131556162138
1105/09/2020118821935389350415194610432781155530
1205/10/2020120761937441348214991735934740152323
1305/11/2020122761996656355514732765835104146895
1405/12/2020125062272492358914405675035458143768
155/13/2020127242151463334014382743335172140737
165/14/2020129241982901342814549794635340139304
175/15/2020131091861671354914475724637041137122
185/16/202013289183311384003145481084140644133886
195/17/202013481196672425114466750840393131261
205/18/2020136892153537432414485692740945127969
215/19/2020138811983901445114515883241937122106
225/20/2020140231423434448714156678241780112789
235/21/2020142572322439430713806643341573110808
245/22/2020144541971814414213628782640816108897
255/23/2020146501983499372613176689239345107651
265/24/2020148501991439360712985736039302106147
275/25/20201508023049043834129511029141759106906
285/26/202015329242220994744134401574547837109258
295/27/2020155462152828523413459918749594110092
305/28/2020157582153560527013335696749615111022
315/29/2020159501942407488013473524449264112047
325/30/2020161281803656518113754762649702114021
335/31/2020163091845497522413778624649038115008
Facebook Insights Data Export -
 
Upvote 0
Do you get an error with the transpose, or just copies the first row multiple times?
 
Upvote 0
Your first code only reads one line.

Your second code should work - have you checked the contents of the data array while debugging?
 
Upvote 0
@Fluff No error with transpose, just copies first row to all rows

@RoryA first code modified from StackOverFlow link in #1 but that was my first thought too when I saw output
The Locals window confirms it's not reading data correctly
For second code, in Locals window, I can see the correct rows of data in data(0), data(1), data(2)... data(32)

With the first code, I sometimes get error message of file is currently open. Am I missing a Close statement vs Open in first code? Either way looks like first code is about to be deleted!

Alternatively, do you have suggestions for reading CSV data via VBA that doesn't loop or is faster?
 
Upvote 0

Forum statistics

Threads
1,213,489
Messages
6,113,954
Members
448,535
Latest member
alrossman

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top