Import CSV File into access

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

How can I pick out the specific columns?

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

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

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

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

Select allOpen in new window

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

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?

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

Select allOpen in new window

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

Thanks for the grade, the points and the fun question.  I'm glad I could help.
bol