CODEWITHSUNDEEP
vbaexcelvbscriptado
Jacek Kotowski
Jacek Kotowski

Reputation: 704

ADO in vba (vbs): Query Insert INTO text file does not work, locked file

I am trying to query a text file, a csv file and return the result of a query to a text file. I tried this apporoach https://stackoverflow.com/a/581638/3480717 which looked very promising, returning data direct from SQL command to a file, skipping the creation of a recordset. Prompting as it may be, I cannot make it work, here is my version:

Sub test()

Dim db: db = "C:\Databases\"
Dim exportFile: exportFile = "C:\Databases1\Exp.txt"


Dim cn: Set cn = CreateObject("ADODB.Connection")

cn.Open _
    "Provider = Microsoft.ACE.OLEDB.12.0;" & _
          "Data Source=" & db & ";" & _
          "Extended Properties=""text;HDR=YES;FMT=Delimited(,)"""


cn.Execute "SELECT [Surname INTO [text;HDR=Yes;Database=" & exportDirFile & _
   ";CharacterSet=65001]  FROM PhoneList.csv"

cn.Close

End Sub

PhoneList.csv - My data is a three column file with Surname, Name and PhoneNumber. For a test I tried to extract PhoneNumber column.

The code stops on cn.Execute

I get an error saying that "Cannot update. Database or object is read-only.

What am I doing (very) wrong? I have access to the files/can open and save them. It seems that the problem lies somewhere else.

How it would look like in VBS? (the code does not need Excel to visualise anything).

Thx very much for comments.

Upvotes: 0

Views: 1476

Answers (2)

Jacek Kotowski
Jacek Kotowski

Reputation: 704

  1. I succeeded with the following (both in VBA and VBS):

    Dim db: db = "C:\Users\jkotows2\Desktop\sql_script\"

    Dim cn: Set cn = CreateObject("ADODB.Connection")

    cn.Open _ "Provider=Microsoft.ACE.OLEDB.15.0;" & _ "Data Source=" & db & ";" & _ "Extended Properties=""text;HDR=YES;FMT=Delimited(;)"""

    cn.Execute "INSERT INTO [exp.csv] SELECT [Surname],[Name],[No] FROM [PhoneList.csv]"

cn.Close

  1. I had to make sure the script is executed in 32 bit mode (apparently my system is 64 bit and Office is 32 bit).
  2. I had to create a Schema.ini file with description of what tables take part in the query.

Upvotes: 0

Ekkehard.Horner
Ekkehard.Horner

Reputation: 38755

Your

cn.Execute "SELECT [Surname INTO [text;HDR=Yes;Database=" & exportDirFile & _
   ";CharacterSet=65001]  FROM PhoneList.csv"

should be

cn.Execute "SELECT [Surname] INTO [text;HDR=Yes;Database=" & "C:\Databases1" & _
   ";CharacterSet=65001] [Exp.txt] FROM PhoneList.csv"

(For TEXT, Database is a folder and the target of INTO a File name)

Upvotes: 2

Related Questions