How to import csv to ms-access without setting table columns?

Question

0.00/5 (No votes)

See more:

I have managed to import a csv file into a ms access table before, but this was done by specifying the table columns beforehand to match the csv. I am planning to tackle a larger csv file and was wondering if it is possible to have the table columns entered programmatically.

Private Sub Import_Click()
'import excel file

'delete current table data
 With CurrentDb
 CurrentDb.Execute "DeleteTableData"
End With

'import
Dim f As Variant
Set f = Application.FileDialog(3)
'set dialog title
With f
.Title = "Choose File"
f.AllowMultiSelect = False

'Use the Show method to display the File Picker dialog box and return the 
user's action.
'The user pressed the action button.
If .Show = -1 Then
For Each f In .SelectedItems


'import into table
DoCmd.TransferText acImportDelim, , "[Table1]", f, True
Next f
MsgBox "File Imported"
Else
'Show if Canceled is selected in a message box
f = "No File Selected to Import."
MsgBox f
End If

End With
DoCmd.SetWarnings True
DoCmd.Hourglass False

End Sub

What I have tried:

I'm wondering if I'd need to read first line first then set as table column or maybe something like that but I'm not really sure on how to go about doing it. The user is able to choose any csv file they want and onclick it will import it and enter the data into a table.

Posted 3-Apr-18 23:02pm

n-hanz

Updated 4-Apr-18 1:13am

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Accepted Answer · 2018-04-04T01:15:00

Quote:
I am planning to tackle a larger csv file and was wondering if it is possible to have the table columns entered programmatically.
...
I'm wondering if I'd need to read first line first then set as table column or maybe something like that but I'm not really sure on how to go about doing it.

It depends on csv content and HasFieldNames parameter for DoCmd.TransferText Method (Access)[^].

As official documentation states:

Quote:
Use True (1) to use the first row of the text file as field names when importing, exporting, or linking. Use False (0) to treat the first row of the text file as normal data. If you leave this argument blank, the default ( False ) is assumed. This argument is ignored for Microsoft Word mail merge data files, which must always contain the field names in the first row.

As per my experience, if you set HasFieldNames parameter to False, the names of columns are added automatically and they start with: F1, F2, F3,..., etc.

Conclusion: you have to check (and optionally ask a user) if first row is a header row, then depending on it to pass True/False to HasFieldNames argument.