Import Text file into MS Access - ignore first two lines 1

[BxWill]

Having difficulty importing a text file (with over 340,000 records) that has two lines
of text before the column headings and the columnar data.

Importing a text file into Excel - one is presented with an option to begin the import at a specific line.

On the other hand, importing a text file into MS Access - it appears that one cannot perform a partial import and ignore specific lines.

Any thoughts on how I can ignore the first two lines during the import?

Currently exploring software to facilitate the import but just thought that someone might have some insight...

 

[MasterRacker]

How often do you need to do this import? If it's infrequent, the simplest solution may be to delete the offending lines by hand before the import.

Otherwise, in Access you could script deleting the lines then calling an import. Or you could go old fashioned and write the complete import routine in VBA.

I would stay away from Excel. Excel likes to make assumptions about data types that can trip you up down the line.

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[BxWill]

Will need to perform the import on a weekly basis.

Not aware of a method to manually delete the first two lines.

Are you aware of VBA code that will resolve this issue?

 

[MasterRacker]

By manually deleting the lines, I meant simply to load the file into a text editor and delete the two lines. At 340000 rows, it's probably too big for Notepad, but Notepad++, EditPad Lite or any number of free editors could handle it. Even in the 21st century, it's still not unheard of to have manual steps involved in a data handling process.

You'll need to write code for either of the VBA options. That would be better asked here: forum705

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[MasterRacker]

If the only thing that's stopping you is getting the 1st two lines out, you could do that with a VB script as well. A crude version could be

dim FSO, inFile, outFile, strLineInFile, count
set FSO = WScript.CreateObject("Scripting.FileSystemObject")

set outFile = FSO.CreateTextFile("C:\test\import.csv")
set outFile = Nothing 

const ForReading = 1
const ForWriting = 2
set inFile = FSO.OpenTextFile ("C:\test\datafile.csv", ForReading)
set outFile = FSO.OpenTextFile ("C:\test\import.csv", ForWriting)

count = 0
While Not inFile.AtEndOfStream
	count = count + 1
	strLineInFile = inFile.ReadLine
	
	if count > 2 then
		outFile.WriteLine strLineInFile
	end if
Wend

'----- Cleanup
set FSO = nothing
inFile.Close
outFile.Close
Wscript.Quit

I wouldn't use that as production code. If you need further help with it post here: forum329

Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]