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

Question

upd.: Here is a Syntax that finaly solved the issue: 'createSQL = "SELECT * INTO newlyСreatedTableName FROM [fileName.csv] IN 'folderPath' [""Text;HDR=YES;FMT=Delimited""]"

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 to solve the issue. I'would be very thankful for anyhelp.

What is the size of the 6 mln CSV file? More than 2GB (msAccess table limit)? — ALeXceL, Jul 31 '22 at 13:12
@BZngr `FMT=Delimited(,) ` returns the same error. 1 line .csv I added the screenshot of the source data — Mik, Jul 31 '22 at 14:32
@ALeXceL, I have built Access tables of 9+ million rows well under Access 2GB limit, closer to 1GB. At three columns, OP can handle this operation. Even better if OP uses a lookup table with code description for class code for only two column table and avoid the repetition of text. — Parfait, Jul 31 '22 at 15:26
If you have MSACCESS.exe, run the CSV query output of Debug.Print in Access' Query Design. Run even few rows, `SELECT TOP 100 *...`, to see if a clearer message raises. Try removing the `INTO` clause for only CSV results. — Parfait, Jul 31 '22 at 15:30
Your CSV path looks to be a network/online folder. Try running the import from local disk. — Parfait, Jul 31 '22 at 15:38
split csv file data to Excel with 7 different sheets or 7 different Excel and then use import export wizard to import data to SQL — Karthick Gunasekaran, Jul 31 '22 at 17:36
@Parfait, thank you for the idea very much. I removed `* INTO [Classification]` and it is the same error. So the problem is in a csv string as I understand. But I'm sure It should be a solution. — Mik, Jul 31 '22 at 17:38
Something else to keep in mind: Once you import the ~6 million rows from CSV into Access (which my 10-year-old notebook did in under 2 minutes using `DoCmd.TransferText`) you may find that transferring the rows directly from Access to SQL Server may be the true bottleneck. See [this answer](https://stackoverflow.com/a/25879264/21443900) for further discussion. (TL;DR: "bulk insert"-type methods are almost always the best choice if available.) — Gord Thompson, Jul 31 '22 at 18:43
[This answer](https://stackoverflow.com/a/73187179/2144390) may also be of interest. — Gord Thompson, Jul 31 '22 at 23:36

ALeXceL · Answer 1 · 2022-07-31T16:54:08.530

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)

thank you for the answer. It's a nice idea. Can you add an idea of how to import PQ table into SQL Server, I didn't experienced it ), may be in few words. — Mik, Jul 31 '22 at 16:57
Neither do I, I'm sorry. But since each of these 6 can be loaded into a spreadsheet, from there you can load them into SQLServer. — ALeXceL, Jul 31 '22 at 17:02

score 1 · Answer 2 · answered Jul 31 '22 at 13:07

1

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

answered Jul 31 '22 at 13:07

ASH

20,759
19
87
200

Thank you for the answer. I want to transfer add data about 6 mln rows to access db. With a QueryTable object I can't find the way to trasfer it. – Mik Jul 31 '22 at 13:56
What is '6 mln rows'? Do you mean 6 million rows? You may need PowerQuery, or a real database. Access can only handle 2GB of data. There are so many other alternatives out there, many of which can handle hundreds of millions of rows, or billions of rows, or more. – ASH Jul 31 '22 at 14:19
It's a 0,5 GB file. The final destination is MS SQL Server. The thing is that I can't use BULK insert due to restrictions. I used a textstream with parsing but it is 2 hours of loading. For a one file it's ok, but it can be several of them every month. the idea is to get it into Access then transfer to DWH. And I want to avoid the import wizard of Server Studio – Mik Jul 31 '22 at 14:44
Will you do any processing on the data read before loading it into SQLServer? What kind of treatment? Filter, format, record deletion? – ALeXceL Jul 31 '22 at 15:02
@ASH, at 3 columns, Access can store many rows of bit size data within its 2GB limit. See this old post: [Maximum number of rows in an MS Access database engine table?](https://stackoverflow.com/q/1221435/1422451) – Parfait Jul 31 '22 at 15:36
@ALeXceL, yes, I need a temp table before final upload. I will change some data types from a string to numerical, some Int to tinyint etc, some string values to date, no filter, no deletion. nothing complicated. The sample in the question is one of tables. – Mik Jul 31 '22 at 16:06
I have been using SQL Server for more than 10 years. I have never heard of any restrictions with Bulk Insert. It's designed for enterprise level jobs, much larger than what you described here. Do a Google search for Bulk Insert. That should do anything/everything you need. – ASH Jul 31 '22 at 17:27
@ASH, my IT says that to run BULK insert I need admin rights, and I'm not allowed to have it. Bu t I didn't check yet as trusted him ) – Mik Jul 31 '22 at 17:34
@Mik - According to [the docs](https://learn.microsoft.com/en-us/sql/t-sql/statements/bulk-insert-transact-sql?view=sql-server-ver16), you would need "INSERT and ADMINISTER BULK OPERATIONS permissions" unless the SQL Server instance is running on Linux, in which case "Only the sysadmin can perform bulk inserts for SQL Server on Linux.". – Gord Thompson Jul 31 '22 at 18:38

score 0 · Accepted Answer · answered Aug 14 '22 at 09:55

0

Here is a Syntax that solved the issue:

'createSQL = _
     "SELECT * INTO newlyСreatedTableName" & _ 
     " FROM [fileName.csv] IN 'folderPath' [""Text;HDR=YES;FMT=Delimited""]"

answered Aug 14 '22 at 09:55

Mik

2,099
1
5
19

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

3 Answers3