Re: output to Access file

["David Jones" <djones@xxxxxxxxxx>]

>>> lparry@xxxxxxxxxx 12/19/01 01:59PM >>>
I am trying to convert data from our Community Information module to an
Access file.
<<<

A couple of notes that I keep handy for doing this type of thing:

1) Use the tab control character for the field separator (control character = 9)
2) Use an obscure character for the repeating field separator (ie, search your list to make sure the character doesn't exist in any of the variable length fields that you are going to export - % tends to work well)
3) Turn off the text qualifier
4) FTP the file and make sure to change the extenstion to ".txt"
5) Open it in Excel
6) If you have fields that repeat [you can tell by searching for the character you chose in (2) in each column]
- move the first column that might contain repeating values past the last column on the right 
- select the column
- use the "Data - Text to Columns" function to split the column into however many fields that exist. You can determine how many new columns you created by selecting all of the data and sort descending by the successive columns at the right. The first time you hit a column that has no entries in the first row, that is the new edge of your spreadsheet.
- rename each of the new columns to be like the original, but add "2, 3, 4..." after the name
- repeat for the remaining original columns that might have had repeating values.
7) Save as an Excel document
8) Import that into Access
9) If you need to make a relational database, always export the Innovative record number and then use queries to make new tables off of the original one just imported, using the record number as a key in each.

If you know that you're data has no repeating fields (i.e., all fixed fields or NR MARC fields), when ftp'ing the file, use ASCII if you want to try taking it directly into ACCESS. If you use Binary, the file has only a CR at the end of each line and ACCESS requires a CR/LF pair, whereas Excel does not.

HTH,
David

=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+=+
David Jones                         
Library Systems Manager III         
Orradre Library                      
mailto:djones@xxxxxxxxxx
phone: 408-551-7167
fax:   408-551-1805
http://www.scu.edu/library/


Santa Clara University               
500 El Camino Real                   
Santa Clara CA 95053-0500            "Insert pithy quote here"