VBA outlook- Array length reduces when trying to transpose to 1D array.

[Ebraham]

Hi

I am trying to extract the data from csv to array using outlook vba. Below code works fine but when i try convert the array to 1D the lenght reduces from 250,000 to approx 58k. I saw some solution using Application.transpose to convert 2D in to 1D but i am getting the error while using application.transpose in outlook.

Public email_array()

Sub load_emails_from_file()

strPath = "H:\XXXXX\XXXXX\"

Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset

'Set cn = CreateObject("ADODB.Connection")

strcon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & strPath & ";Extended Properties=""text;HDR=Yes;FMT=Delimited"";"
Conn.Open strcon
strSQL = "SELECT CUSTOMER_NAME FROM Emaillist.csv;"

Dim rs As Recordset
Dim rsARR() As Variant

Set rs = Conn.Execute(strSQL)

email_array = WorksheetFunction.Transpose(WorksheetFunction.Transpose(rs.GetRows))
rs.Close
Set Conn = Nothing


End Sub

 

[pjmorris]

why not use Powerquery to import the data - you get a lot more control and a lot less hassle.

From the Data Ribbon, Get & Transform Tab, New Query, From File, From CSV. This will open your CSV file in the Powerquery Editor where you have some pretty intuitive control over any changes. When you're happy with it, go to the Home Ribbon and select 'Close & Load', or 'Close and Load to' to open a dialogue with more control over where the data arrives in Excel.

Its worth the effort.

HTH

 

[Ebraham]

I don't want to use excel. I am performing some validations on emails in outlook. I am trying to load list of emails from csv and then I have events in outlook which triggers the event when the user clicks on reply, replyall ... etc.
So the issue is when I try to load the csv which has list of emails it's in 2D Array how can I convert the array to 1D using outlook vba. I saw some solutions using Application.transpose but that seems to not work in outlook vba, not sure y.
Any help is highly appreciated.

 

[mikerickson]

When in doubt, write your own brute force routine

For i = 1 to limit1
    For j = 1 to limit2
        newArray(i,j)= oldArray(j,i)
Next:Next

 

[Ebraham]

When in doubt, write your own brute force routine

For i = 1 to limit1
    For j = 1 to limit2
        newArray(i,j)= oldArray(j,i)
Next:Next

Loping takes a very long time I have approx 500k records, any other alternatives other than looping?
Error is similar to posted here Type mismatch

Thanks in advance. !!.

 

[mikerickson]

With 500K records, you might look at not transposing. If its that large, you can work with it as a 2-D array. From the coding, it looks like a single row array.

 

[Peter_SSs]

I don't want to use excel.

In that case, I'm wondering why ask in the Excel Questions forum?
Would I be best to move the thread to the General Discussion & Other Applications forum which is "for questions about applications other than Excel or Access"?