SELECT * INTO Access FROM csv ; running VBA ADO from Excel-CodePudding

Struggling with data transferring. I have a .csv with a 6 mln rows and trying to import it to Access DB running code in Excel. I have a simple code.

Function getAccCN(ByVal dbFullPath As String) As Object
    Set getAccCN = CreateObject("ADODB.Connection")
    getAccCN.connectionString="Provider=Microsoft.ACE.OLEDB.16.0;Data Source=" & dbFullPath
End Function

Function createSQL() As String
    createSQL = "Select * INTO [" & _ 
                fileName & "] FROM [Data source = " & _
                repFile.ParentFolder.Path & _ 
                "\; Extended Properties=""Text;HDR=Yes;FMT=Delimited""]." & _ 
                repFile.Name ' repFile is a *.csv as "Scripting.FileSystemObject"

    Debug.Print createSQL ' returns following:
     ' Select * INTO [Classification] FROM [Data source = \\av-fs01.av.local\profiles$\...\Project IQVIA\; Extended Properties="Text;HDR=Yes;FMT=Delimited"].Classification.csv
     ' *.accdb and the table name and  *.csv have the same base name - "Classification"
End Function

Function uploadCSV() as Boolean
Dim CN as Object
    Set CN = getAccCN(repFile.ParentFolder.Path & "\" & baseFileName & ".accdb")
    CN.Open
    CN.Execute createSQL() ' this creates Error
    Exit Function
ErrHandler:
    Debug.Print Err.Number ' = -2147467259  "Wrong argument."
       'CN.ERROR - Arguments are of the wrong type, out of range, or conflict with each other.
End Function

Headers and first rows of the source.

So, I can't find out how solve the issue. I'would be very thankful for anyhelp.

CodePudding user response：

As the problem is the amount of rows to be imported at once, I suggest using Power Query to import and, in it, perform the desired work (Power Query is designed for that). After that, divide the 6 million rows into 6 queries, each of which can be imported into Excel or served as a basis for exporting to another application. I did a test here and it worked: I created a CSV file with 6M lines containing the text "This is a 6M line file and this is the line (lngPtr)". Once imported into Power Query, I split the single column into 4 others and finally applied Table.Split, dividing the table into 6 (1M rows each), according to the M script below (example to be adapted):

let
    Font = Csv.Document(File.Contents("C:\temp\6MRows.CSV"),[Delimiter=",", Columns=1, Encoding=1252]),
    #"Split Table" =Table.Split(Font,1000000)
in
    #"Split Table"

After splitting the CSV file, 6 "Table" will appear in PQ. Then add them into a 6 New Query. In less than 20 minutes the CSV was into PQ (Excel365, Win10Pro, I7/16Gb)

CodePudding user response：

Please try this and feedback.

Sub csv_Import()
Dim wsheet As Worksheet, file_mrf As String
Set wsheet = ActiveWorkbook.Sheets("Single")
file_mrf = Application.GetOpenFilename("Text Files (*.csv),*.csv", , "Provide Text or CSV File:")
With wsheet.QueryTables.Add(Connection:="TEXT;" & file_mrf, Destination:=wsheet.Range("B2"))
  .TextFileParseType = xlDelimited
  .TextFileCommaDelimiter = True
  .Refresh
End With
End Sub