Parse into a table from a ttx file

  • Thread starter Thread starter steve
  • Start date Start date
S

steve

Can anyone help me sort this data out please. It consists of three
fields: ID Number, Name, DataString. The (cr) signifies the next
record. Two records are below. The data is just one long text string.

.....(cr)"429088930","Robert E Watkins
","692026636666",(cr)"206844084","James Thomas ","642036636466",
.......

I can have the (CR) or exclude it from the data, or have any other
record delimiter.

What i want out is a 3 field table consisting of

ID Number Name DataString


Any guidance appreciated.

thanks
Steve
 
Steve,
I'm not a programmer by trade but was looking for a decent parsing
tool that didn't require the knowledge base to do something like this.
Check out http://www.parse-o-matic.com/ - it will take some time to
get used to the syntax but I've applied it to many of these types of
needs and it works great with batch files and Access.
I'd be happy to assist with the start of this.

Frank
 
Hi Steve,

Access wants CR+LF (0x0d0a) for a record separator. CR alone won't do.
Your file should then look like this
...
"429088930","Robert E Watkins","692026636666",<CR+LF>
"206844084","James Thomas ","642036636466",<CR+LF>
...
and Access will be able to import it.

As it stands, the quote marks will force Access to import the ID number
as text rather than a number, and the comma between the DataString value
and the record separator means Access will identify a fourth, empty
field.

If you can alter the textfile to omit the quotes round the first field,
and omit the last comma, well and good. Otherwise, just import the file
as it stands to a new table with fields
IDString
PersonName (many Access objects have a Name property, and
using "Name" as the name of a field or control sooner
or later causes problems)
DataString
Dummy (the empty field)

Then add a number(Long) field called IDNumber (life is simpler if you
don't use spaces or special characters in names) to the table, and put
the ID numbers into it by using an Update query, updating IDNumber to
Clng([IDString]).

Finally delete the IDString and Dummy fields.
 
Sorry if I already posted this - Google did not respond when I first
tried.....

Thanks John and Frank - I would like to pursue the Access option
without 3rd party software if possible - however I seem to be missing
something:

So far in a new DB I get external date/ import file/ browse to the
..ttx file and the text wizard opens. I follow the steps and it just
gives me hundreds of columns with one row of data, the <CR+LF> just
part of the data in every third field.

What am I doing wrong, it is probably something about
this----(0x0d0a), which i do not understand.

thanks again

Steve




John Nurick said:
Hi Steve,

Access wants CR+LF (0x0d0a) for a record separator. CR alone won't do.
Your file should then look like this
...
"429088930","Robert E Watkins","692026636666",<CR+LF>
"206844084","James Thomas ","642036636466",<CR+LF>
...
and Access will be able to import it.

As it stands, the quote marks will force Access to import the ID number
as text rather than a number, and the comma between the DataString value
and the record separator means Access will identify a fourth, empty
field.

If you can alter the textfile to omit the quotes round the first field,
and omit the last comma, well and good. Otherwise, just import the file
as it stands to a new table with fields
IDString
PersonName (many Access objects have a Name property, and
using "Name" as the name of a field or control sooner
or later causes problems)
DataString
Dummy (the empty field)

Then add a number(Long) field called IDNumber (life is simpler if you
don't use spaces or special characters in names) to the table, and put
the ID numbers into it by using an Update query, updating IDNumber to
Clng([IDString]).

Finally delete the IDString and Dummy fields.

Can anyone help me sort this data out please. It consists of three
fields: ID Number, Name, DataString. The (cr) signifies the next
record. Two records are below. The data is just one long text string.

....(cr)"429088930","Robert E Watkins
","692026636666",(cr)"206844084","James Thomas ","642036636466",
......

I can have the (CR) or exclude it from the data, or have any other
record delimiter.

What i want out is a 3 field table consisting of

ID Number Name DataString


Any guidance appreciated.

thanks
Steve
Click to expand...
Click to expand...
 
Hi Steve,

For Access to import a textfile, the records in the file must be
separated by a carriage return and a line feed. This is standard for
textfiles in Windows and DOS, though other operating systems often use
different conventions.

The carriage return is the character known as CR or Control-M; its ASCII
value is 13 (or 0D in hexadecimal). The line feed is the character known
as LF or Control-J; its ASCII value is 10 (or 0A). 0x0d0a is another
convention for representing this pair of characters; I guessed wrongly
that you'd be familiar with it.

I also guessed that when you put
(cr)
in your original post you were referring to the single character CR
separating the records. If that's the case, you need to replace each CR
with a CR LF pair. Normally this will happen automatically if you open
the file in Word (on a PC) and save it as plain text under a different
name. If the
(cr)
actually indicates something else, you need to replace whatever that is
the a CR LF pair. Often you can do this using Word's Replace feature:
replace whatever it is with ^p and then save the result as plain text.

If this doesn't solve the problem, post back with a very careful
explanation of exactly what separates your records.



Sorry if I already posted this - Google did not respond when I first
tried.....

Thanks John and Frank - I would like to pursue the Access option
without 3rd party software if possible - however I seem to be missing
something:

So far in a new DB I get external date/ import file/ browse to the
.ttx file and the text wizard opens. I follow the steps and it just
gives me hundreds of columns with one row of data, the <CR+LF> just
part of the data in every third field.

What am I doing wrong, it is probably something about
this----(0x0d0a), which i do not understand.

thanks again

Steve




John Nurick said:
Hi Steve,

Access wants CR+LF (0x0d0a) for a record separator. CR alone won't do.
Your file should then look like this
...
"429088930","Robert E Watkins","692026636666",<CR+LF>
"206844084","James Thomas ","642036636466",<CR+LF>
...
and Access will be able to import it.

As it stands, the quote marks will force Access to import the ID number
as text rather than a number, and the comma between the DataString value
and the record separator means Access will identify a fourth, empty
field.

If you can alter the textfile to omit the quotes round the first field,
and omit the last comma, well and good. Otherwise, just import the file
as it stands to a new table with fields
IDString
PersonName (many Access objects have a Name property, and
using "Name" as the name of a field or control sooner
or later causes problems)
DataString
Dummy (the empty field)

Then add a number(Long) field called IDNumber (life is simpler if you
don't use spaces or special characters in names) to the table, and put
the ID numbers into it by using an Update query, updating IDNumber to
Clng([IDString]).

Finally delete the IDString and Dummy fields.

Can anyone help me sort this data out please. It consists of three
fields: ID Number, Name, DataString. The (cr) signifies the next
record. Two records are below. The data is just one long text string.

....(cr)"429088930","Robert E Watkins
","692026636666",(cr)"206844084","James Thomas ","642036636466",
......

I can have the (CR) or exclude it from the data, or have any other
record delimiter.

What i want out is a 3 field table consisting of

ID Number Name DataString


Any guidance appreciated.

thanks
Steve
Click to expand...
Click to expand...
Click to expand...
 
thank you John - I think I have got it - I was literally getting (CR)
from the data source - I am going to get that changed and it seems
like it will work..I will post back if it does not work when I receive
the data.

thanks

John Nurick said:
Hi Steve,

For Access to import a textfile, the records in the file must be
separated by a carriage return and a line feed. This is standard for
textfiles in Windows and DOS, though other operating systems often use
different conventions.

The carriage return is the character known as CR or Control-M; its ASCII
value is 13 (or 0D in hexadecimal). The line feed is the character known
as LF or Control-J; its ASCII value is 10 (or 0A). 0x0d0a is another
convention for representing this pair of characters; I guessed wrongly
that you'd be familiar with it.

I also guessed that when you put
(cr)
in your original post you were referring to the single character CR
separating the records. If that's the case, you need to replace each CR
with a CR LF pair. Normally this will happen automatically if you open
the file in Word (on a PC) and save it as plain text under a different
name. If the
(cr)
actually indicates something else, you need to replace whatever that is
the a CR LF pair. Often you can do this using Word's Replace feature:
replace whatever it is with ^p and then save the result as plain text.

If this doesn't solve the problem, post back with a very careful
explanation of exactly what separates your records.



Sorry if I already posted this - Google did not respond when I first
tried.....

Thanks John and Frank - I would like to pursue the Access option
without 3rd party software if possible - however I seem to be missing
something:

So far in a new DB I get external date/ import file/ browse to the
.ttx file and the text wizard opens. I follow the steps and it just
gives me hundreds of columns with one row of data, the <CR+LF> just
part of the data in every third field.

What am I doing wrong, it is probably something about
this----(0x0d0a), which i do not understand.

thanks again

Steve




John Nurick said:
Hi Steve,

Access wants CR+LF (0x0d0a) for a record separator. CR alone won't do.
Your file should then look like this
...
"429088930","Robert E Watkins","692026636666",<CR+LF>
"206844084","James Thomas ","642036636466",<CR+LF>
...
and Access will be able to import it.

As it stands, the quote marks will force Access to import the ID number
as text rather than a number, and the comma between the DataString value
and the record separator means Access will identify a fourth, empty
field.

If you can alter the textfile to omit the quotes round the first field,
and omit the last comma, well and good. Otherwise, just import the file
as it stands to a new table with fields
IDString
PersonName (many Access objects have a Name property, and
using "Name" as the name of a field or control sooner
or later causes problems)
DataString
Dummy (the empty field)

Then add a number(Long) field called IDNumber (life is simpler if you
don't use spaces or special characters in names) to the table, and put
the ID numbers into it by using an Update query, updating IDNumber to
Clng([IDString]).

Finally delete the IDString and Dummy fields.

On 20 Oct 2003 13:19:43 -0700, (e-mail address removed) (steve) wrote:

Can anyone help me sort this data out please. It consists of three
fields: ID Number, Name, DataString. The (cr) signifies the next
record. Two records are below. The data is just one long text string.

....(cr)"429088930","Robert E Watkins
","692026636666",(cr)"206844084","James Thomas ","642036636466",
......

I can have the (CR) or exclude it from the data, or have any other
record delimiter.

What i want out is a 3 field table consisting of

ID Number Name DataString


Any guidance appreciated.

thanks
Steve
Click to expand...
Click to expand...
Click to expand...
 
Back
Top