how to import a delimited text file like this?

[Seanw]

it is very large file, has 678000 rows.
but the txt file get is like that

"name","address","zip","state"
="11",="aaa",="00001",="NY"
="22",="bbb",="00002",="CT"
="33",="ccc",="00003",="NJ"
="44",="ddd",="00001",="MA"


there is = signs and when i import in Access, it returns error"Unparsable Record"
i tried to use notepad to replace all "=" sign into "", but the file is so large, notepad cannot handle. since it is a on going event, is there any way i can import to access directly?

thanks

[Seanw]

bump......

[MMcCarthy]

it is very large file, has 678000 rows.
but the txt file get is like that

"name","address","zip","state"
="11",="aaa",="00001",="NY"
="22",="bbb",="00002",="CT"
="33",="ccc",="00003",="NJ"
="44",="ddd",="00001",="MA"


there is = signs and when i import in Access, it returns error"Unparsable Record"
i tried to use notepad to replace all "=" sign into "", but the file is so large, notepad cannot handle. since it is a on going event, is there any way i can import to access directly?

thanks

Hi Sean

I would import the file as it is with comma as the separating value. Turn off the " as the text identifier. This will import the records with the = signs and the " quotes but it should be a lot easier to do the find and replace in Access.

Only other option is to read the file in line by line using VBA and ignoring all = signs. It's not a straightforward code but the following tutorial should give you some idea what's involved.

Import csv (Comma Separated Value) File

Mary

[Seanw]

Hi Sean

I would import the file as it is with comma as the separating value. Turn off the " as the text identifier. This will import the records with the = signs and the " quotes but it should be a lot easier to do the find and replace in Access.

Only other option is to read the file in line by line using VBA and ignoring all = signs. It's not a straightforward code but the following tutorial should give you some idea what's involved.

Import csv (Comma Separated Value) File

Mary

Thanks a lot Mary, yes i tried first one, but since it is a such large file, it take at least 30 minutes to run replace, and my access can only run around 60000 replace each time. it ask me to modify registry, but it is locked.

Use VBA is good idea, but i am not that familiar with it. Anyway, thank you for your reply.