Query Help

[mlr0911]

I have a text document that is linked and am trying to run a query off of that document.

I have all of my queries working great except for one thing......if a comment line drops to the next line, I'm not able to capture it.

Example:

12345
; This is the comment line.
I need to get the comment line on the first line.

Example
12345 ; This is the comment line.

Does anyone know how I can do this and make sure that the comment line is associated with the rest of the field?

Thanks

 

[David Eagar]

Concatinate

 

[mlr0911]

I thought of that; however, it is in the same field as the account number (from the text document). If if was in a different field, I can see doing a concatenate, but since it is within the same field, I can't do it.

 

[Rabbie]

What is actually in your account number field. Is it

12345
; This is the comment line

If so you can replace the cr lf in the string with a space character.

 

[mlr0911]

But how would I get all of the information on 1 line (i.e. the same field) like:

12345 ;This is the comment line.

 

[David Eagar]

In query newfield: = 12345 + This is the comment line.

Display newfield

 

[boblarson]

If I'm reading this properly you currently are storing data in ONE FIELD like displayed. The next question would be why? It is this storage way that, because it isn't good, is going to make it really difficult to get what you want. Is this something that comes from another system and you don't have the ability to change the source?

 

[mlr0911]

Yes, we get this report from another vendor and it comes accross this way.

 

[boblarson]

How are you currently putting the data into the database? I'm thinking that it might be easier to put it in right to begin with so you don't have to worry about this.

 

[mlr0911]

I have the text file linked as a delimited file and using a query off of that table. This file can have anywhere from 100 to 1000 entries.

 

[boblarson]

Is it possible to see the text file? I realize it may not be possible, but if I can see it I can probably write a function to import it in the way that it should be to save you the headache of using workarounds.

 

[mlr0911]

Bob

I have attatched an example of the information.
I have tried to get everything on one line, but I haven't had any success.

Thanks for your help.

 

[boblarson]

Any way you can include more than one record? It would help to see how it looks with more than one.

 

[mlr0911]

Sure.

As you will see, some of the files won't have a ";" comment line.

Thanks

 

[boblarson]

Okay, that's actually helpful. I'll see what I can do.

 

[mlr0911]

Thanks Bob.

 

[mlr0911]

Bob

Have you been able to work on the text file?

 

[boblarson]

Here you go!

Here's the sample database that will import the text file into one line when there are two lines for that particular item.

You will probably need to tweak it to fit your stuff, but hopefully this will get you on your way.