MP218 (Programmer) Aug 1, 2003
i need to import in access table from txt file.

the problem is

txt looks like this.

a1 a2 a3
b11111 b2 b3
c1 c3


there is no c2....and format of b11111 looks like that


first question: how could i replace space with ;

so the result would be

a1;a2;a3...

second question

the result is

a1;a2;a3
b1111;b2;b3
c1;;c3

thank you in advance.

I expect that the first question to ask is:
are you in control of creating the text file?

If so, it should be easy to create a comma delimited text file.

if not; then it depends on the structure of the data.
However, the layout of your data appears to be unstructured: It seems to have variable spaces between fields.
i.e. the a row seems to be 3 spaces, the b row 1 space, and the c row ? spaces.
If there is no structure between the fields you have little hope of transferring them to a table.


Cheers Ray

When you import the text data into Access, it should know (or ask if) you're importing ASCII-delimited text. If so, it should at some point ask what the delimiter is. At that point, you say it's a space. There should also be a check box somewhere that says "treat multiple occurances of delimiter as one." If you check this box then two or more spaces in a row will be treated as one space. Your data should then import correctly.

I don't quite get why you're trying to change the spaces into semicolons before the import given my info above. At any rate, to do that you'd have to open the text file in a word processor and make two FIND/REPLACE ALL commands (don't include the quotation marks when you actually do this):

• Find " " (two spaces) and replace with " " (one space). Keep repeating this FIND/REPLACE ALL until you get the "zero found" message.
  
• Find " " (one space) and replace with ";".

Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division

I agree with Greg's of the Import method normally.
The only reason it couldn't be used in this instance is that row 'c1 c3' indicates a missing field which would have to handled at the inital creation of the 'delimited' file.

Cheers Ray

Ray, no matter what the condition of the data, you still must import it. How you import it really depends on the data.

Because the data presented in the original question has 1s in the first spot, 2s in the second, 3s in the third, etc. You can find that C2 is missing very easily after importing "C1 C3" and isolating C1 and C3 ("Hey! Where's the 2?!"). Import text-delimited first using the wizards, process the data with code after.

If the data is, instead, totally random and each datum length is totally random with possible missing data, then you have a problem indeed! You'd be required to import a whole line at a time using code and then process each line given the rules you set to isolate data. You'd still need some kind of delimiter to isolate the data, but you need a way to ID missing sets. For this situation, we'd need to see what the actual data looks like to offer help on how to decipher it.


Greg Serrano
Michigan Dept. of Environmental Quality, Air Quality Division