Importing data into a table

[Guest]

I'm importing a text file into my access db however the text file has the
following rows that I do not want imported

The First 4 rows

Source File Name : CDW - Facility
File Create Date : 20060209
Time Period : 200512
SRC_SYS_ID|CORP_NBR|IP_ID|OBLIGOR_NUM|

And the last 3 rows

Total Number of records - 50516
Total Facility Limit - 82529811024.27
Total Charge Off - 638805636.63

This is the SQL command I am useing to import the data

DoCmd.TransferText , "EC_Facility_Specification v4 (type)", "(1) ec_facility
- New", varFilename, True
DoCmd.RunSQL "SELECT [obligor_num] & ['_'] & [obligation_num] AS
[obligor & obligation], [(1) ec_facility - new].* INTO Current_Month_Data
FROM [(1) ec_facility - new]"

Can some one help so when I import the text file those rows will be deleted

Thanks

Chris

 

[Guest]

Start by doing your imort by hand. That is, from the main tool bar, File,
Get External Data, Imort. When you get to the Import Text Wizard, Click on
advanced. Here you can choose which fields to import, what names to assign
them, and what data types they should imort as. Once you have it set like
you want it, click on Save As. Give it a name. This is a Specification
Name. Then in your TransferText, the second argument (I think, check Help to
be sure) is where you would put the Specification name you saved as in the
wizard. It will then do the import based on those rules.

 

[Tim Ferguson]

Can some one help so when I import the text file those rows will be
deleted

Use Open and Line Input# to filter the original text file into a temp file?

Tim F

 

[Guest]

Ok Tim,

I am totaly new to this can you help rewrite the code so it will do what you
are saying and I will try it

Thanks

 

[Tim Ferguson]

I am totaly new to this can you help rewrite the code so it will do
what you are saying and I will try it

This is air code and without looking up any of the functions:

' obvious
const pathToMyFile as String = "\\newyork\data\mydata.txt
const pathToTempFile as string = "c:\temp\christemp.txt"

' need to recognise when the good data is finished and the
' three final lines are starting
const firstBadLine as string = "Total number of records"

' file handles
dim inFile as integer
dim outFile as integer

' obtain legal handles
inFile = freefile()
outFile = freefile()

' open the files: this needs to be error-trapped properly
open pathToMyFile for input as #inFile
open pathToTempFile for output as #outFile

' skip over first four lines
dim i as integer
dim readText as string

for i = 0 to 3
' read in the line but don't do anything with it
line input #inFile, readText

next i

' copy all the next lines into the temp file
do while not eof(#inFile)
' read in the next line
line input #inFile, readText

' examine it to see if it's the first illegal one
' you may be able to think of a more robust test, but
' I don't know your data as well as you do
if left(readText, len(firstBadLine)) = firstBadLine Then
exit do
end if

' since it's a valid line, copy it out to the
' new temp file
print #outFile, readText

' now go round again for the next line
loop

' tidy up
close #inFile
close #outFile


That should give you enough of a clue to carry on with. Hope it helps


Tim F

 

[John Nurick]

' obtain legal handles
inFile = freefile()
outFile = freefile()

' open the files: this needs to be error-trapped properly
open pathToMyFile for input as #inFile
open pathToTempFile for output as #outFile

I often get this wrong too. Should be more like

inFile = FreeFile()
Open pathToMyFile For Input As #inFile
outFile = FreeFile()
Open pathToTempFile For Output As #outFile

because if you don't open a file after calling FreeFile() the first
time, the next call will return the same handle as the first.

 

[Tim Ferguson]

because if you don't open a file after calling FreeFile() the first
time, the next call will return the same handle as the first.

Well.. I said it was air code!! Thanks for the heads-up.

Tim F