[AccessD] A2000: Import Needs

Just a quick thought, split the file into separate files, and import the
separate ones with their own specs.

For example:

Function RunImports(strPathToFileToImport as string)
Dim f as long
Dim strTemp as string
Dim I as Long
dim ABCCollection as collection
dim DEFCollection as collection
dim EFGCollection as Collection
dim strArray() as string
set ABCCollection=new Collection
set DEFCollection=New Collection
set EFGCollection=New Collection
f=freefile
Open strPathToFileToImport for binary access read as f
Strtemp=space(lof(f))
Get f,,strtemp
Close f
strArray=split(strtemp,vbcrlf)
for i=0 to ubound(strArray())
	select Case Left(strArray(i))
		Case "ABC"
			ABCCollection.Add strArray(i)
		Case "DEF"
			DEFCollection.Add strArray(i)
		Case "EFG"
			EFGCollection.Add strArray(i)
	End select
Next i



Now you have three collections that have the data you want to import.  You
simply have to create temp files with them, ie:

F=freefile
Open "C:\ABCTempFile.txt" for binary access write as f
For i=1 to ABCCollection.Count
	Put f,,ABCCollection(i) & vbcrlf
Next i
Close f

Then run the import, with the right spec:

DoCmd.TransferText .....

Delete the temp file

Kill "C:\ABCTempFile.txt"

Then repeat for the other collections.

You could get more complex, and have it all done with one collection, using
individual collections, etc.  Just depends how much time you want to spend
on creating the process.

Drew

-----Original Message-----
From: Darren DICK [mailto:darrend at nimble.com.au] 
Sent: Monday, November 13, 2006 6:42 PM
To: 'Access Developers discussion and problem solving'
Subject: [AccessD] A2000: Import Needs

Hi all
 
I have been given large (very large) text files to manipulate
 
I can Import them using the fixed width method and I can even attach them
using
the get External data wizard - It all works OK
 
But the issue with these files is that one file may contain different data
types
- all determined by a 3 character code in the first 3 positions in the left
most
position of each line
 
EG we may have 3000 lines of data that start with "ABC" - Cool - I can
create an
import spec - Call it the ABC import spec and break up each line into the
relevant fields in a table using fixed width - Easy
 
But after the 3000 "ABC" data type lines in my text file - there may be 1200
lines that start with "DEF" as the left most 3 characters and the data in
each
of the "EFG" type lines need to be 'mapped' at different fixed lengths into
different fields
 
So the issue is - has anybody had experience kind of 'merging' several
Import
Specs but only across 1 file?
 
Or does anyone have any suggestions on handling this?
 
Many thanks in advance
 
Darren
-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com