Import CSV File into access

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?
LVL 3
harris9999Asked:
Who is Participating?
 
b0lsc0ttIT ManagerCommented:
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.
bol
0
 
b0lsc0ttIT ManagerCommented:
harris9999,

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.

b0lsc0tt
0
 
harris9999Author Commented:
How can I pick out the specific columns?
0
Cloud Class® Course: Microsoft Azure 2017

Azure has a changed a lot since it was originally introduce by adding new services and features. Do you know everything you need to about Azure? This course will teach you about the Azure App Service, monitoring and application insights, DevOps, and Team Services.

 
b0lsc0ttIT ManagerCommented:
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.
bol
0
 
harris9999Author Commented:
Ok ao how then would I access the records.
Something like,

oRS("FirstName")?

Just loop through that until oRS.eof

I'll have a go at this.
Some code may be useful I am having trouble.
Thanks
0
 
b0lsc0ttIT ManagerCommented:
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.
bol
0
 
harris9999Author Commented:
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
						sBuffer=objStream.ReadLine
						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 & ","
									  Else
											sql = sql & ");"
									  End If                        
									Next
									response.Write(sql & "<br>")
									cn.Execute(sql)
						End If
						lineCount = lineCount + 1
				  Loop
				  objStream.Close
			End If
			
			Set objFSO = Nothing

Open in new window

0
 
b0lsc0ttIT ManagerCommented:
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.
bol
0
 
harris9999Author Commented:
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?

0
 
harris9999Author Commented:
Thanks,
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)
			
While NOT RS.EOF 
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")) & "')")
 
RS.MoveNext
Wend
 
RS.Close
set RS = NOTHING
Conn.Close
set Conn = NOTHING

Open in new window

0
 
b0lsc0ttIT ManagerCommented:
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.
bol
0
 
b0lsc0ttIT ManagerCommented:
Thanks for the grade, the points and the fun question.  I'm glad I could help.
bol
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.