Importing CSV into MS-Access using form button, confusing error

Question

I'm trying to import a CSV file that is created from a web form I developed. When the form submits it creates a record in my CSV with a multitude of customer information.

As per requirements I needed to put it into a CSV, and then separately have it import into an Access database for others to use (Two steps required for server security).

The way I'm trying to do it is with a simple form with a button on it inside Access, that simply says Import, that will pull an update of the CSV whenever the user needs it.

My error is confusing me as it's stating

"Field 'F1' doesn't exist in destination table 'Applications' "

I do not have a field in my CSV labeled F1, or even any record that contains 'F1', and there is no field named F1 in my access table Applications (obviously).

Here is my VB module code from Access

Option Compare Database

    Sub ImportingCSV()
     Function Import()
    On Error GoTo Macro1_Err


    DoCmd.TransferText acImportDelim, "", "Applications", "C:\Users\ALee\Documents\formTesting22.csv", False, ""


      Import:
        Exit Function
      Macro1_Err:
        MsgBox Error$
        Resume Macro1_Exit

    End Function

And here is my CSV file format (spaced out for your readability)

    OPUCN#WVQNAJT4PD,
    2017.05.03,
    test,
    v,
    90545452929,
    4062033985,
    No,
    [email protected],
    10003937683827,
    test,
    test,
    689 395 3967,
    2048 2983999,
    No,[email protected],
    111 e Streeth south,
    12,
    Temporary,
    Commercial,
    100,
    200,
    300,
    208/120V,
    Three-Phase,
    Underground (UG),
    Ganged Position*,
    23,
    "dsbsdhfbslhfbshfbsdhlfbgshdfgsfslfgljshgfljshgfljshgflsj"

The error is telling me that the field for the second phone number ("4062033985" in the CSV) doesn't have a field in the table Applications, but it does! "F1" in the CSV is Customer Mobile. When I import manually through Access's import wizard this works fine.

Hopefully someone can point me in the right direction, not familiar with VB script or macros in access.

Have you tried removing the "" from your: DoCmd.TransferText acImportDelim, , "Applications", "C:\Users\ALee\Documents\formTesting22.csv", False ? I'm looking at some of my old transfer stuff and never placed any "" placeholders in there. Everything else in there looks okay. — geeFlo
– geeFlo, Commented May 9, 2017 at 18:30

Gustav · Accepted Answer · 2017-05-09 14:45:05Z

1

Don't import the file.

Link the csv file as a table. Then create a query to read and convert (purify) the data.

Use this query as source for further processing of the date like appending data to other tables.

answered May 9, 2017 at 14:45

Gustav

56.3k7 gold badges33 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Adam Lee Over a year ago

Will this leave my database open to security threats though? I'm only trying to capture the data in a two step process (Form -> CSV and then CSV -> Database) because my employer has requested me to do so, such that there is no direct link from the web form users into the database that hold's everyone else's submitted info. The CSV they would potentially have a link to only holds the data for a few days worth of submissions so if it was breached (somehow) the impact would be minimal.

Gustav Over a year ago

It will be no different from now. You replace the "transfer-import" method with a "transfer-link-runquery". The difference is, that in the query you have control over field names, can exclude invalid data, and may convert some or more fields to fit the fields of the receiving table.

Adam Lee Over a year ago

I've created a link and have the table it created. I'm facing an issue now with this though, that being: If a customer was to submit the web form application WHILE an employee has the database open, it won't append the submission to the CSV or the database. Because the link between the database is holding the CSV open, the new records are lost. I'm trying to wrap my head around how to avoid this, I'm thinking potentially an intermediary Database? One to link directly to the CSV and the second for our employees to view the data? I'm not sure if this would pose the same issue though.

Gustav Over a year ago

The CSV file is only kept open during the read. You can actually replace the file and - when you open the query again - the data will be updated. Or you can just copy the original CSV file to a temp location, then link the copy.

Adam Lee Over a year ago

The CSV file will not take any new records if the database is open even just sitting idle. The link is holding the file open just the same as if the CSV itself is open. Also creating a copy of my CSV and then linking the copy, that would mean I'm never able to pull in the new records that users submit to the CSV original.

|

Chris · Accepted Answer · 2017-05-09 20:05:30Z

0

a CSV file is a spreadsheet... try...

DoCmd.TransferSpreadsheet acImport, acSpreadsheetTypeExcel12Xml,[YourDestinationTable],"C:\YourFileDirectoryPath, filename, and extension",true,[Spreadsheet name if multiple sheet names]

answered May 9, 2017 at 20:05

Chris

2541 silver badge5 bronze badges

Comments

ASH · Accepted Answer · 2017-05-26 21:00:37Z

There are all kinds of ways to do this sort of thing. This is certainly the simplest method.

Private Sub Command0_Click()

DoCmd.TransferText acImportDelim, "", "Book1", "C:\your_path_here\Book1.csv", True, ""

End Sub

Let's say you want to import several CSV files, all of the same type, into the same table. Just run the script below.

Option Compare Database
Option Explicit

Private Sub Command0_Click()
    DoImport
End Sub


Function DoImport()

 Dim strPathFile As String
 Dim strFile As String
 Dim strPath As String
 Dim strTable As String
 Dim blnHasFieldNames As Boolean

 ' Change this next line to True if the first row in CSV worksheet
 ' has field names
 blnHasFieldNames = True

 ' Replace C:\Documents\ with the real path to the folder that
 ' contains the CSV files
 strPath = "C:\your_path_here\"

 ' Replace tablename with the real name of the table into which
 ' the data are to be imported

 strFile = Dir(strPath & "*.csv")


 Do While Len(strFile) > 0
       strTable = Left(strFile, Len(strFile) - 4)
       strPathFile = strPath & strFile
       DoCmd.TransferText acImportDelim, , strTable, strPathFile, blnHasFieldNames


 ' Uncomment out the next code step if you want to delete the
 ' EXCEL file after it's been imported
 '       Kill strPathFile

       strFile = Dir()

 Loop


End Function

You can do all kinds of other thins too; navigate to a file using the msoFileDialogFilePicker; you can loop through record sets and load them, one by one, into your table. As Gustav suggested, you can link to your file (staging) and write records into a table (production). You should probably try all of these methods, and play around with

Collectives™ on Stack Overflow

Importing CSV into MS-Access using form button, confusing error

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

6 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related