We help IT Professionals succeed at work.

We've partnered with Certified Experts, Carl Webster and Richard Faulkner, to bring you a podcast all about Citrix Workspace, moving to the cloud, and analytics & intelligence. Episode 2 coming soon!Listen Now


Import CSV File into access

harris9999 asked
Medium Priority
Last Modified: 2013-11-19
I want to be able to insert values from an uploaded csv file into an access database.
The CSV file could have many heading maybe 10 or more.
But I only want to insert 5 of the columns.  
Do the columns need to be in a specific order?
Watch Question

b0lsc0ttIT Manager


No.  The order won't matter as long as you can tell them apart so you can order them in the code.

Let me know if you have any questions or need more information.



How can I pick out the specific columns?
b0lsc0ttIT Manager

The generic way to do it that will work in any CSV is to use Fields.Item(#).  This assumes you opened the CSV file as a Recordset.  You then use a number (starting with 0) in place of # to access the "column" you want.  For example ...
oRS.Fields.Item(1).Value ' gives the second field in a CSV line
If the CSV file has a header row and is opened with the object knowing that then I believe they can be used to access a specific "column".  I would have to look through some old server files to find the exact syntax and to confirm this so let me know if you need it.
If you have a question about any of this please let me know.


Ok ao how then would I access the records.
Something like,


Just loop through that until oRS.eof

I'll have a go at this.
Some code may be useful I am having trouble.
b0lsc0ttIT Manager

I would try the method I suggested first.  Don't try to access the field with a name but use the generic Fields.Item() method.  So your line would become ...
oRS.Fields.Item(0) ' assuming FirstName was the first field in the CSV row
If that works then please answer my question about the header row and show the code you use to connect to the file and open the recordset (omit sensitive info of course).  Let me know how this method works or if you have a question.


The code I was using did not open the csv as a recordset.  I assume that would be better?
The code I was using is below.  It works fine as long as the csv file is in the correct format and only has the 5 columns.  
It is basically for uploading contacts.  
So say someone expoerted contacts from Outlook. It could upload that straight away,
That why I am wondering if it could pick through the csv for the appropriate columns.  (Assmuming the csv has the proper headings) even if there is irrelevant information.  
Can this be achieved by opening the csv as a recordset.  

I have achieved something similiar by opening an xls as a recordset.  
			Set objFSO = Server.CreateObject("Scripting.FileSystemObject")
			If not objFSO is nothing then
				  set objStream = objFSO.OpenTextFile(filepath & "\" & filename,1, False) ' 1 = ForReading
				  lineCount = 1
				  do while not objStream.AtEndOfStream
						If lineCount > 1 Then ' ignoring first line as it contains header info
									arrLine = Split(sBuffer, ",")
									sql = "INSERT INTO Contacts (UserId, FirstName, LastName, Email, Country, Company) VALUES (" & Session("userid") & ", "
									For X = 0 To Ubound(arrLine)
									  sql = sql & "'" & SanitizeSQL(arrLine(X)) & "'"
									  If X <> Ubound(arrLine) Then
											sql = sql & ","
											sql = sql & ");"
									  End If                        
									response.Write(sql & "<br>")
						End If
						lineCount = lineCount + 1
			End If
			Set objFSO = Nothing

Open in new window

b0lsc0ttIT Manager

I wouldn't rely on it but I believe with the right connection type and a file that has a header row you can use name in the header row.  The syntax would be like you had in your previous post.  The number and Fields.Item() method is most reliable though.  Not all connection methods may support specifying the header row or using it for a "field label."  It would depend mainly on the server and how you make the connection.
Feel free to try it but you should always remember the main method I suggested since that will always work and can still be used to grab just what you want from each "row."
Let me know if you have any other questions on this or need more info.


Ok, it will be best to use your method.  
The CSV File will have a header row but the uploaded csv files won't always be the same.  
E.g. I have it so that users can download a sample csv file and add to it.  
But they may also export there contacts from Outlook.  This will create a CSV File,
which has the headers:
Title, FirstName, MiddleName, LastName, Suffix, Company, Jobtitle, HomeCountry, EmailAddress

On upload I don;t need all them files.  But I'm unsure with your method how exactly I could access the fields I need :
FirstName, LastName, EmailAddress, Company, HomeCountry

Have you sample code?

IT Manager
What sample code do you need or mean?  Do you have code now to connect to the CSV file and get info using the main method I suggested?  That is what you need to start with just using a CSV file you know.
Once that works I would try to use a field name to see if that will work with the type of connection you are using.  If so then the issue you asked about in your last post will be easier to handle.  As long as the header uses the same field names you could get what you need.  If the field names are set by the user or your connection won't support using them to reference a field then we can come up with Plan B.
Any Plan B will involve more script and coding.  As I thought about it my recommendation would be to have the user tell you which field number in their file corresponds to the specific fields you want.  For example you would have a form for inputting the number (starting at 0) for the order where First Name, Last Name, Email Address, etc occur in the CSV file they upload.  This method involves the least code and would be the most reliable.
Any other would be a guess on your part.  Of course factors like your coding skills and the ability and understanding of the user (i.e. they need to know the order and how to use a form to tell it to you) would influence this.  Hard for me to say but hopefully mentioning them to you will help you narrow down what you need.
Let me know how this helps or if you have a question.  I can provide some specific code on something if you need but I need an idea of what and responses to the above.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts


I've got it working I think.  The code is below.  
It is working.  Do you forsee any problems with it?

I have trapped the error it gives it the headers do not match my SQL staement and give them an error message to check there headers and give a link to a page with the correct format required and  sample csv file to download.  
Set Conn = Server.CreateObject("ADODB.Connection")
Set RS = Server.CreateObject("ADODB.Recordset")
conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & filepath & ";Extended Properties='Text;HDR=YES;FMT=Delimited'"
sSQL="SELECT * FROM [" & filename & "]"
set RS = Conn.Execute (sSQL)
cn.Execute("INSERT INTO Contacts (UserId, FirstName, LastName, Email, Country, Company) " & _
"VALUES (" & Session("userid") & ", '" & SanitizeSQL(RS("FirstName")) & "', '" & SanitizeSQL(RS("LastName")) & "', '" & SanitizeSQL(RS("EmailAddress")) & "', '" & SanitizeSQL(RS("HomeCountry")) & "', '" & SanitizeSQL(RS("Company")) & "')")
set Conn = NOTHING

Open in new window

b0lsc0ttIT Manager

I'm glad it is working and with the field names.  Besides the issue you trapped for I don't see any other possible issue.  The real tell will be using it but since you provide that sample and info it should be something the user can learn to fix.
Let me know if you have any other questions on this.
b0lsc0ttIT Manager

Thanks for the grade, the points and the fun question.  I'm glad I could help.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.