Importing a text file (Access 2003)

Topic

New Reply

Hello All,

I’m currenly working on a project that requires me to grab information that is kept in our PDM system (Agile), such as manufacture, manufacture part number, compliance, notes, etc. Since I have no way to link to this data (corporate policy for people like me), I’ve been exporting entire data tables (20K to 40K records) from this program in a comma delimited text file then loading it up into Access via Access’ import. When using Access’ import feature, I’m able to load the data in a table in the correct format as long as a comma doesn’t exist in the data itself. If a comma does exist, in say the notes field, Access thinks that it must go to the next field when actually it’s part of the data in the same field. Is their some way to get this to work? I’ve tried changing the delimator (find and replace in Word, Notepad, and Wordpad) to a special character that I know is not used in our data but this takes a LONG time for the find a replace to do it’s thing.

Ideally, I would like a user (myself and a couple others) to be able to click a button, open a text file selected by the user, and then have the file load directly into a specific table. However, this is easier said than done when your a novice user of Access with no VB training and limited background. I don’t know how I could do a lot of this, but if I could get the text into a variant or string, I could use MID, LEN, and other easy functions to extract the data to the table. I have a strong feeling that this wouldn’t be the best way, but not knowing VB that we’ll, I don’t know of any other way. Any help or suggestions would be greatly appreciated.

Thanks in advance.
Regards,
Drew

Reply | Quote

Replies

If you have data values containing the delimiter, you have a problem whether you import manually or using code. Access has no way of determining whether a comma is a delimiter or part of a value, nor has VBA code.

Does the export from your PDM system have an option to specify a text qualifier such as “? If so, text values will be enclosed in text qualifiers, and you can specify the same text qualifier when importing into Access.

Reply | Quote

Yes, it does enclose data in double quotes (data”,”data”,”data).
I thought you could only specify one character as a delimator when doing the Access import?

Reply | Quote

You can specify only one field delimiter, but you can also specify a text qualifier. See screenshot below.

Reply | Quote

Hans,

Sorry I haven’t replied sooner. I’ve been very busy.
Yes, for every Import I do, I use the double-quote as the qualifier and the data is imported incorrectly. It’s seems like I’ve tried many thing and always get data that doesn’t load correctly, which is the reason why I was asking about using VBA.

Thanks,
Drew

Reply | Quote

VBA uses the same method as the Import Text Wizard, so if importing interactively fails, importing using code will fail too.

If you wish, you can attach a small sample file. Replace sensitive information with dummy data.

Reply | Quote

Hi Hans,

I must of overlooked the text qualifier in our data on the last couple of test loads. When I done a test this morning, I noticed that we had used one double-qoute to indicate inches followed by a comma. From this point down, my data was off. From what I can tell, Access’ Import only uses one double-quote as the text qualifier, but what is really needed for my data is a “,” qualifier. This is the reason why I had brought up VBA.
My thought was to use MID and INSTR functions in a loop to load a table from the text file, looking for “,” (I have the knowledge to write the loop; can’t figure out how to get a text file declared in VBA). Maybe this would be to processor intensive? The only other way I have to solve this problem is to open the text file in Word and do a find and replace on “,”. Changing it to something I know is not used in our data and then indicate that character in the Access Import. This ussually takes quite a while (good part of a morning). Ussually I just won’t pull fields containing notes due to this reason (unless absolutely needed).

Regards,
Drew

Reply | Quote

Are ALL fields in the text file surrounded by quotes?

Reply | Quote

Addition: if all fields are surrounded by quotes, you can use the attached code. You must, of course, substitute the correct names. There are 2 assumptions:
– You have already created a table in the database with the correct structure.
– You have set a reference to the Microsoft DAO 3.6 Object Library in Tools | References…
– The fields do not contain “,”

Reply | Quote

Hans,

Attached is the export from our PDM system of hand picked records that I would have problems loading when using Access’ import.

Your have assumed correctly…
– A table already exist
– 3.6 Object Library is selected
– No field contains “,”

I’ve tried your code, but the data is not loading correctly. Please note the format of the file. In prior post, I didn’t indicate that the extract includes unwanted carrage returns. This is fixable, but I would have to go through all records that include an unwanted carriage return. Is their anyway to compinsate for this? If not, I’ll just fix it but will take some time.

Thanks!
Drew

Reply | Quote

This is horrible. Is there any chance that you can get the PDM system to deliver usable exports?
If not, you’ll have to clean the exports manually before importing into Access.

Reply | Quote

I can’t agree with you more.
We are a small division of a large corporate comany. Our PDM system is nearly 5 years old and service packs have not been installed due to going to the next version of the software incorporating all other business units. The corporate deadline for this keeps on getting pushed out. In the mean time, it makes it very difficult for myself and others to report on what we put into our system which is the reason why I continue to use extracts along with Access.
For my problem, I’ll either try to clean the extracts manually or just leave the problem fields out.

Thank you very much for your help.
Regards,
Drew

Reply | Quote

Hans,

Logically, I think I have a way to parse this out, but I have a VBA question.
When opening a text file in VBA and using loops with it, how does VB know what location in the file you are at? I see that your code continues to loop by using the string found in Line Input #, but I don’t know how it knows to go to the next line. In addition, is their a way to use Left(), Mid(), and instr() functions with text files?

Thanks,
Drew

Reply | Quote

Line Input automatically shifts the pointer into the file to the next line, ready for the next Line Input instruction. If the end of the file is reached, the EOF(…) function will return True.
You can use string functions such as Left, Mid, Right and InStr to parse the string variable filled by Line Input.

Reply | Quote

Must line input always be used when working with text files? And, is their anyway to set the pointer to the previous line (or is it pointer forward only)?

Thanks,
Drew

Reply | Quote

You can open a file in Random access mode if it has a fixed record length. This does not work for a file consisting of lines with varying lengths.
If you want to work with multiple lines, you could read the lines into an array of strings.

Reply | Quote

Hans,

Does this mean that you can’t go back in a text file? Similar to a recordset.MovePrevious.

Attached is the code that I’m trying to get to work. You’ll probably be able to decifer this better than I could explain it. What I’m trying to do is find the end of a record by finding a double-quote at the end of one line input (previous) and a double quote at the beginning of the next line input (current). The problem I’m having, is that if a line in the text file does not start with a double-quote, the string it pulls out is an empty string. Does Line Input require double-quotes at the beginning of each line? If so, is there a work around? (Microsoft help on this is very limited on this).

Thanks!
Drew

Reply | Quote

There’s no attachment.

Reply | Quote

Sorry.

Reply | Quote

I would demand that you get usable data instead of absolute rubbish. The amount of time and energy needed to extract usable information is out of proportion, since the data must already exist in a workable format.

Reply | Quote