Import .CSV file containing sporadic use of quotation marks into MS Access

2
votes

I am importing/inserting data from a .csv file to a MS Access database. Some of the fields in the .csv file has quotation marks in them, others not (if the field contains a comma, the field is placed within quotation marks). The fact that only some fields contain quotation marks causes the import to skip each row which contains a field with quotation marks (fields without quotation marks are imported fine).

How do I import/insert all fields (irrespective of quotation marks)?

Here is my connectionstring to dabase:

Set conn = New ADODB.Connection
conn.open = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=somefolder\database.accdb"

Here is my query that inserts data from .csv file (UTF-8) into database (.csv file is indeed comma-separated):

Set rs = New ADODB.Recordset
Query = "INSERT INTO [table] SELECT * FROM [" & strCSVFileName & "] " _
        & "IN """ & strCSVFolder & """ ""Text;HDR=Yes;FMT=Delimited;CharacterSet=65001;""" 
rs.Open Query, conn

Brief background

The database will be updated daily by colleagues of mine with no interest in VBA or databases. Hence, the import/insert has to be automated and run from a macro contained in Excel.

UPDATE: I ended up parsing the .csv file with Adodb Stream (since FileSystemObject does not seem to be able to handle UTF-8). I have access to the .csv files also as tab-delimited, which I though would be easier to handle with Adodb Stream (but which I knew I could not correctly read with above described approach). However, tab-delimited .csv files seem to difficult to deal with everywhere - so I stuck with the comma-delimited file format and did what Gustav suggested; cleaned up each row to get rid of quotation marks.

Now that I have it working, I am happy I have gone this way, since I have been able to correct other problems in the .csv files, and since each .csv file only take about 3 seconds to parse, clean and insert into database.

4
vbacsvms-accessadodb
In that case, I also have the option of downloading the data as tab-delimited (rather than comma-delimited). The tab-delimitted version does not contain quotation marks. My proposed solution above won't work for tab-delimited files, but if I am going to use FileSystemObject to parse the file anyway, I might as well use the tab-delimited file. Thanks for your input Gustav. – CM2020
Gustav; if you post your comment as an answer, I can mark as accepted. – CM2020

4 Answers

3
votes

I noticed Text Qualifier field in the Import Text Wizard. If you select the quotation mark " there, MS Access will import the data correctly and will recognize that commas enclosed in quotation marks are not delimiters.

I'm not sure how to apply this in VBA though.

Text Qualifier in Access' Import Text Wizard

0
votes

Maybe try importing data into Excel first to see whether it gives correct results. If correct, then, you can import data from Excel directly to Access.

0
votes

There is no safe and simple method to import inconsistent data.

Thus, open the file as text, read line by line applying whatever validation, converting, and/or correction you may find needed, and then append the field of the line to the table.

0
votes

If you want to work with csv files from VBA, take a look up to this project. I can understand you need to get the raw data from a CSV over Excel, a fast option is to load the CSV records into an array and then dump it to a Worksheet.

Try this:

Sub ImportRecords_RFC4180()
    Dim CSVix As CSVinterface
    Dim filePath As String
    
    filePath = "C:\Demo.csv" 'Change this to suit your needs
    Set CSVix = New CSVinterface 'Create new instance
    Call CSVix.OpenConnection(fileName) 'Open a physical connection to the CSV file
    Call CSVix.ImportFromCSV 'Import data
    Call CSVix.DumpToSheet 'Dumps the data to the current Workbook's new Worksheet starting at named "A1" range.
    Set CSVix = Nothing 'Terminate the current instance
End Sub