[AccessD] Excel & CSV File Import

Try wrapping the value in NZ()?

HTH,

Jim DeMarco
Director of Product Development
HealthSource/Hudson Health Plan


-----Original Message-----
From: ACTEBS [mailto:actebs at actebs.com.au]
Sent: Thursday, August 07, 2003 9:18 AM
To: 'Access Developers discussion and problem solving'
Subject: RE: [AccessD] Excel & CSV File Import


Stuart,

Thanks for that. All works great, but I get a sub script out range error
when the line feed gets to a blank/null row. Any ideas on how I can handle
that?

Thanks
Vlad

-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
Sent: Thursday, 7 August 2003 8:24 PM
To: Access Developers discussion and problem solving; ACTEBS
Subject: Re: [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.



_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com





_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com


***********************************************************************************
"This electronic message is intended to be for the use only of the named recipient, and may contain information from Hudson Health Plan (HHP) that is confidential or privileged.  If you are not the intended recipient, you are hereby notified that any disclosure, copying, distribution or use of the contents of this message is strictly prohibited.  If you have received this message in error or are not the named recipient, please notify us immediately, either by contacting the sender at the electronic mail address noted above or calling HHP at (914) 631-1611. If you are not the intended recipient, please do not forward this email to anyone, and delete and destroy all copies of this message.  Thank You".
***********************************************************************************