Hands on: Importing text into Access

Brian Ritchie has some data he wants to import into an SQL-type database, such as Access or Oracle. The data comes from an old mainframe system and started as a comment field. However, the only way Brian can export the data is as a series of text strings. His question is whether it is possible to stitch these back together again into, say, a memo field in Access.

No problem. The first step is to import the table containing the text strings into a table in Access. Now use an UPDATE query using GUI. First, open the query builder in design view and add the table TextImport. Pop down the Query menu option and choose Update Query. Select the field called AllText and then type the following into the Update To section:

[T1] & “ “ & [T2] & “ “ & [T3] & “ “ & [T4]

This says, essentially, add the contents of the T1 field to a space and then add the contents of the field called T2, then add a space, and so on and so forth.This is saved as a query called FixText. When you run it you will see two warning messages; accept those and it will run and update the AllText column. The SQL is:

UPDATE TextImport SET TextImport.AllText = [T1] & “ “ & [T2] & “ “ & [T3] & “ “ & [T4];

This process of stringing values together is called concatenation and the & operator is, unsurprisingly, a concatenation operator. You can also use + as a concatenation operator, but it has an odd side-effect.

If any of the strings being concatenated is null or zero-length, then the entire string becomes null. You can see this effect by running the query called FixText2, which uses + instead of &. After running, the second row of the AllText field is blank because of the null value in T4.

This is not a bug as there are circumstances in which it is desirable to have the entire field as a null if even one component is null. The trick is, of course, to use the correct operator for the job in hand.

Like this story? Spread the news by clicking below:

 del.icio.us     Digg this     reddit!