Link to text file creates #Num! error on import

[GregTheSquarePeg]

Hello,

I have a text file containing mixed text and numeric data - It's pulled from a warehouse management system. The data comes off in 'pages' with the header text repeated at the top of each page. Problem is - I have my link to Access set with a link import specification. The spec imports numeric data as double & long integer - however, when the linked data is viewed in Access, the repeated text headers are each replaced with a '#Num!' error... which is understandable.

I then attempt to use the linked text file in a Make Table query, with no criteria such as 'Is not null' etc, it simply makes a table for me to append to - which tends to run fine the first time - but if you try to run again, and overwrite the previous table, it fails with the error:

"You cannot record your changes because a value you entered violates the settings defined for this table or list (for example, a value is less than the minimum or greater than the maximun). Correct the error and try again."



Does anybody know of a good method for dealing with importing columns containing mixed data types, to avoid/workaround this problem?

It's probably clear that I'm a novice in Access, so any help appreciated.

Gregg

[ItsMe]

I don't know that I understand the header appearing in each page of the text file. I guess I am reading this wrong.

What I usually do with text files is import them into a table. You can create a macro using the wizard. The wizard will step you through the process. Headers can be difficult to deal with if the text file is delimited vs. a fixed width format. However, it may be beneficial to import the RAW file into a table and see what it looks like. Your table can have columns that are of the Text data type to help manage the Header.

Hopefully, after you have one or two sample files in your table you can look at the table. View your table and try to see where the header falls. If the text file was designed correctly, the header should place itself in one or more rows (Records). Now you can see how you will need to query this table and append another table with the desired records and data types.

[Dal Jeanis]

Generally, what I would do is one of two things.... but, come to think of it, they amount to the same thing.

Either (1) you can take the external file, and scrub it to remove all the excess headers and empty lines before you import it, or (2) you can import the external file into a temporary table, scrub out the crud, and then append the good data to the table.

[ssanfu]

Another approach that I have had to take is to write code to read the text file line by line and parse each line. A lot more code, but you can control what gets written to a table or tables.

My report was a printout, but printed to a text file instead of paper. Had the title, date line and a page break symbol then the data. The report printed on paper in the smallest font available on a dot matrix, wide format paper. I think it was 132 columns across. I had tried doing like Dal suggested, that is, manually editing the text file before trying to import it, but it took a while to edit the text file. The report was up to 35 pages and I had to get the data 5 times a day. So for me, code was the way to go.

Lots of code with lots of variables to hold the data, but I was in control.


Good luck.......

[GregTheSquarePeg]

Thanks for the suggestions all.

The text files can run to 500k lines, so removing the page headers pre-import isn't really an option. I'm going to try and create a SELECT query to remove the #Num! errors, then have a Append or MakeTable query create a fresh table from the SELECT query.

Will let you know how I get on.
Thanks again.

Gregg

[Dal Jeanis]

Greg - The number of lines is irrelevant. Don't the headers have a format that allows them to be detected? A VBA program doesn't care whether it detects one header to delete or 100K.

[GregTheSquarePeg]

Hi Dal,

Yes, I appreciate the point.
I'm a little familiar with VBA in Excel (very basis stuff), but have never used it to edit text files. The headers will always be the same for this particular report yes.

Would you be able to provide a piece of example code for finding and deleting specific text strings in .txt files?

Thanks,
Gregg

[ssanfu]

You would have to provide a sample of your data. At least 2 pages (with the page breaks) in the sample.

It is really simple:
Open a text file for input (reading)
Open a text file for output (writing)
Input a line
Do I keep this line??
If yes,
output (write) the line
Else
loop back and read the next line.

do this until you reach the end of the text file.
Close both files.

[GregTheSquarePeg]

Thanks Steve, for taking the time to reply - but I think I've resolved the issue with a different workaround.

I've set the link spec to bring all fields in as text, then I'm converting the number and date fields using CDate & CLng within the append query. Seems to do the job well enough.
Although I'm having problems with the CDate conversion - my dates are 25.03.2013 format, but get deleted by the CDate function. Will post that in a seperate thread.

Thanks,
Gregg