vba query csv file excel 2010

**oldfart101** · 05-12-2013, 02:52 PM

Trying to run a query on a csv file (* delimeter)
(Because its bigger than 3M), so can't import the lot!

problem no.1 - the jet engine doesn't work with 64 bit Office.
problem no.2 ODBC - don' want every machine to set up ODBC connection; dsn-less connection - driver manager error

anyone got 'here's one I made earlier' that will run with 64 bit Excel??

TIA

**tehneXus** · 05-12-2013, 05:20 PM

Hi,

did you try opening (not importing) the csv file with excel setting the format parameter to csv?
http://msdn.microsoft.com/en-us/libr.../ff194819.aspx

Workbooks.Open FileName:=MyFile, Format:=2

Regards

**oldfart101** · 05-12-2013, 05:26 PM

Thought of that; however because the file is bigger than excel can handle, I need to run a quey to return a sub-set of the data before loading/opening the file.
Also - once opened, using a for .. next loop to enter the result into excel will be slow?

**tehneXus** · 05-12-2013, 06:45 PM

Originally Posted by oldfart101

(Because its bigger than 3M)

So I suppose it's 3 GigaByte here.. shouldn't 64bit Excel open this? Might take some time though

Once opened you parse all data into an array with one codeline, construct your output in another array and paste it with one codeline, that's faster than looping through all cells.

Regards

**oldfart101** · 05-14-2013, 01:00 PM

Sub import_csv()
''This requires that you add a reference Microsoft ActiveX Data Objects to support the ADO code.
''also need to download & install Microsoft Access Database Engine 2010 Redistributable
''AccessDatabaseEngine_x64.exe
'' from http://www.microsoft.com/en-us/download/details.aspx?id=13255

'' you have to create a schema.ini file in the same directory
''[short.csv]           the file name of the csv file
''Format=Delimited(*)
''ColNameHeader = True
''FirstRowHasNames = True

Dim con, rst
Set con = CreateObject("ADODB.Connection")

'' the directry name is Source
con.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\sourcedir;Extended Properties='Text;HDR=YES;FMT=Delimited*';"

Set rst = CreateObject("ADODB.Recordset")

'' the csv file goes between the [brackets]
rst.Open "SELECT * FROM [short.csv] WHERE AccountNo=00009819;", con


Dim nextRow As Integer
 
rst.MoveFirst
nextRow = Worksheets("Sheet1").UsedRange.Rows.Count + 1
Worksheets("Sheet1").Cells(nextRow, 1).CopyFromRecordset rst
 
rst.Close
con.Close
 
Set rst = Nothing
Set con = Nothing
End Sub