Does anyone know how to get external data from a txt file into a access table when the txt file has different delimiters in it i.e. my file has , . spaces ( ) / etc splitting a line up to mean separate fields however I cannot change these within the actual text file as there are over thousand lines and the file will constantly be updated with new lines of the same format.
Can you give an example? It can be done with code. But there needs to be 'rules' for splitting a record in the file. For example, if the first field is delimited by a comma and the second field is always delimited by period. Or something like that then code can be written for that.
Another possibility is... If you link to the file with a comma as the delimiter (may be another delimiter) are the rest of the fields subfields of those fields. An example would be:
If you linked to this with a comma as the delimiter you would have the following fields:
Is so, then you can link to the file and then use a query to split the fields even more.
Seeing as your problem lies within field7 thru field10 you can "simply" import it using a comma seperated file. Then use an update query to seperate out the now combined field7 into the new fields you need.
I think this would be easiest if your not "into" VBA
to go down the VBA route
you need to select / specirfy the file - up to you how yiou do that
Open tbFileName For Input As #1 'opef the file for reading
While Not EOF(1) 'iterate through the file till eof reached
Line Input #1, strLine 'read a line from the file
..... 'find the next character you are searching for (using instr)
......'split that string off using left$ or mid$ (your choice)
'the implementation of the split could be a function eg Columnx=ParseText(strline,startat,searchfor)
......'write to table using .add & .update methods of your ADO/DAO object
wend 'continue loop processing.....
cloase #1 ' release the resources attached to the file
I have tried the vb putting getting errors staiting the file is already open, this ispossibly because I am changing the file extension from one to a txt file. I have tried the update query it all works fine untill i turn it into a update query - when i do this i get an invalid naming error. I know you can seperate all the deliminaters within Excel giving me all the fields I want, and I just want access to do that but the import in access only allows for one deliminator
if you have succesfully imported this file into excel, and are unable to do write you own access import filter, why don't you save the excel data, and then import / link that into your Access app?
That way round its under your contol. Its not the most elegant soluiton, but if it gets your data in who cares? you may need to reconsider if this is an operaton you need to do frequently but it will get you going at present. If this is a frequent operation then write a fulter, its not a major task, approx 0.5 to 1 days max I reckon.