[AccessD] Excel & CSV File Import

On 7 Aug 2003 at 19:36, ACTEBS wrote:

> Hi List,
>  
> Has anyone got any code that can import Excel & CSV files programmatically -
> not using transfer text/excel functions. The reason I ask is that I have
> some unusually formatted spreadsheets whose column names change from month
> to month and also the CSV file I need to import all the info from about row
> 20 onwards.
>  
If you can save the Excel as CSV as well, you can just open them all 
for input, read them a line at a time and decide on how to handle 
each line.  I've got quite a few routines that do this for various 
sorts of input.

As long as you can identify lines either by line count or content of 
one of the fields, you can parse out the data you need and dump it 
into any table you want.

In it's simplest form, just do something like the following.
You can build whatever conditions you need (repeating line counters, 
string comparisons etc) in the "If...End If"  section to handle the 
particular format of the input file

Sub GetData(strInputFile as String)
	Dim strTemp as String
     Dim  strParsedData() as String
     Dim lngLinecount as Long
     Dim rs as DAO.Recordset

	Open strInputFile for Input as #1
     Set rs = CurrentDb.OpenRecorset("tblMyTable")
	While not EOF(1)
           lngLinecount = lngLinecount + 1
		Line Input #1, strTemp
           strParsedData = Split(strTemp,",")
           If............................    Then 
	           rs.Addnew 
      	      rs!Field1 =  strParsedData(1)  
           	 .....              
            	rs.Update
           End If
      Wend
	Close #1
End Sub

  
 
-- 
Lexacorp Ltd
http://www.lexacorp.com.pg
Information Technology Consultancy, Software Development,System 
Support.