Accessing and Managing Congressional Constituent Services System Electronic Files

Learn about the contents of .DAT files in the Congressional Constituent Services System (CSS) Electronic Files, including how to open and manipulate them using Microsoft Office applications like Excel. Discover accompanying documentation and tips for handling large data sets effectively.

• Congressional files
• Constituent services
• Data management
• Microsoft Excel

delacerda_h Follow

Uploaded on Dec 12, 2024 | 0 Views

Download Presentation

Please find below an Image/Link to download the presentation.

The content on the website is provided AS IS for your information and personal use only. It may not be sold, licensed, or shared on other websites without obtaining consent from the author. Download presentation by click this link. If you encounter any issues during the download, it is possible that the publisher has removed the file from their server.

Presentation Transcript

1. Congressional Constituent Services System (CSS) Electronic Files What is in that .DAT file and what can we do with it? Alison White Deputy Archivist Senate Historical Office August 8, 2012

2. Electronic Constituent Correspondence Management In a handbook created by the Senate Archivist to assist Senators offices with the closing process, information is provided on archiving Constituent Service System (CSS) data A form is included for closing offices to request that the data be sent to their designated repository. Repositories typically receive a .DAT file containing up to 32 tab delimited record fields. Usually these are accompanied by a form letter library Depending on the selections made on the form, they may also receive related correspondence records

3. Accompanying Documentation Each .DAT file is usually accompanied by text files titled Readme.txt and Layout.txt Open the Readme.txt file to determine the number of records in the .DAT file Open the Layout.txt file to see all the defined fields that may contain records (See CSS Data Format: Record Description for Correspondence Data Slides 10 & 11 ) There may be additional files containing incoming or outgoing emails or other attached files

4. Is there an easy way to access these files? Depending on the Senator s length of service, the .DAT file will contain thousands, possibly hundreds of thousands of records If you also have related correspondence records, you may wish to develop a plan to create a robust, relational database to integrate all of these materials But keep in mind you can view and manipulate the records in the .DAT file using basic Microsoft Office applications such as Excel or Access

5. What application should I use to open the .DAT file - Excel? You can open the .DAT file in Excel, but only if there are fewer than 1,048,576 records. Otherwise, you get this error message: FILE NOT LOADED COMPLETELY This message can appear due to one of the following: The file contains more than 1,048,576 rows or 16,384 columns. To fix this problem, open the source file in a text editor such as Notepad. Save the source file as several smaller files that conform to this row and column limit, and then open the smaller files in Microsoft Excel The area that you are trying to paste the tab-delineated data into is too small. To fix this problem, select an area in the worksheet large enough to accommodate every delimited item Once you open the .DAT file in Excel, collapse the empty fields and . . .

6. Youll get something that looks like this:

7. What application should I use to open the .DAT file - Access? To open in Access Database . . . Save the .DAT file as a .TXT (text file) Import TXT file into Access database Create new database In new database, go to External Data tab located on Access Ribbon (this is where to go to bring in data from an outside source) Select Text File Follow directions through the Import Wizard

8. Import Wizard In the first screen select the Delimited format In the second screen select the Tab delimiter The next screen, Field Options, gives you the option to name the fields by clicking on Field 1 and subsequent field headers and replacing the text Use the Layout.txt file (see following slides for titles of record fields) and look at the field content to match it up correctly Click Finish, open your Access database table containing all the records in the .DAT file and . . .

9. Youll get something that looks like this:

10. CSS Data Format: Record Description for Correspondence Data Field Name Prefix First name Middle name Last name Suffix Appellation Title Organization Address line 1 Address line 2 Address line 3 Address line 4 City State Code Zip Code Country Field No. 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 Description Prefix (ex. Mr. or Mrs.) First name Middle name or initial Last name Suffix (ex. Jr.) Appellation (ex. MD) Org. Title (ex. President) Organization or business name First line of address Second line of address Third line of address Fourth line of address City 2-character state code Zip code for domestic address Country name (intnat l address)

11. CSS Data Format: Record Description for Correspondence Data Field No. Field Name 17 In Correspondence ID 18 In Correspondence Type Type of incoming corr CASE or GENERAL 19 In Correspondence Method Method of contact (Letter, Email, Phone) 20 In Correspondence Date Date Incoming corr received (YYYYMMDD) 21 In Correspondence Topic(s) Topics that describe corr (ie War on Terror) 22 In Correspondence Text Freeform text w/ notes or comments 23 In Correspondence Doc Name(s) Name/path of corr files/email attachmts 24 In Correspondence Filing # Identifier providing location of hardcopy doc 25 Out Correspondence ID Numeric identifier of outgoing corr 26 Out Correspondence Type Type of outgoing corr CASE or GENERAL 27 Out Correspondence Method Method of contact (Letter, Email, Phone) 28 Out Correspondence Date Date Outgoing corr closed (YYYYMMDD) 29 Out Correspondence Topic(s) Topics that describe corr (ie War on Terror) 30 Out Correspondence Text Freeform text w/ notes or comments 31 Out Correspondence Doc Name(s) Name/path of out corr files/email attachmts 32 Out Correspondence Fill-in Fields Name of fill-in field as it appears in doc followed by PIPE and text inserted into doc in place of fill-in field Numeric identifier of incoming corr Description

12. It is important to remember that . . . All offices do not handle constituent correspondence the same way Different offices may track different record fields No offices fill in all fields Related records that may be attached to correspondence such as incoming emails or outgoing letters or emails may or may not accompany these files In a perfect world the repository will work with the office before it closes to learn more about that office s system and to determine what related records might be included

13. What can you do with this information? Even doing something as simple as opening the data in Microsoft Excel enables the possibility of inserting columns containing .xml encoding around desired record fields Microsoft Access can be used to generate reports on the data contained within In Excel or Access you can hide columns containing identifying information such as names or street addresses while still using other fields for analysis

14. What might researchers do with this information? Researchers may want to study concerns about certain issues by zip code within the state Researchers may want to examine changes in the focus on certain topics over time Researchers may choose to make connections with tools such as Twitter trends or Google timeline Or researchers using numerous collections containing this type of data may wish to make regional comparisons throughout the nation And as we all know we can never anticipate all possible uses of our materials, but there is a clear trend towards research using data such as this