MatchUp Software:Excel/Access Troubleshooting Guide - Revision history

Admin at 22:51, 21 July 2016

2016-07-21T22:51:40Z

← Older revision		Revision as of 22:51, 21 July 2016
Line 2:		Line 2:

	{{CustomTOC}}		{{CustomTOC}}


	==Troubleshooting Tips When Processing an Access or Excel Table==		==Troubleshooting Tips When Processing an Access or Excel Table==
Line 173:		Line 172:


	[[MatchUp Software~~\|Back to MatchUp Main Page~~]]		[[Category:MatchUp Software]]

Admin at 22:49, 21 July 2016

2016-07-21T22:49:27Z

← Older revision		Revision as of 22:49, 21 July 2016
Line 1:		Line 1:
	~~Troubleshooting Tips When Processing an Access or Excel Table~~		[[MatchUp Software\|← MatchUp Software]]

			{{CustomTOC}}


			==Troubleshooting Tips When Processing an Access or Excel Table==
	Access and Excel are difficult formats to process because they are in a proprietary Microsoft format. Because of this, we are forced to use Microsoft's prescribed methods to access data. MatchUp uses one of two technologies for doing this: ADO and DAO.		Access and Excel are difficult formats to process because they are in a proprietary Microsoft format. Because of this, we are forced to use Microsoft's prescribed methods to access data. MatchUp uses one of two technologies for doing this: ADO and DAO.
	This document is intended to provide steps in working around the default behavior and providing MatchUp with formats it can handle.		This document is intended to provide steps in working around the default behavior and providing MatchUp with formats it can handle.


	~~[[MatchUp Software\|Back to MatchUp Main Page]]~~

Tim: /* Trouble Processing xlsx or accdb formats */

2014-09-22T14:48:49Z

Trouble Processing xlsx or accdb formats

← Older revision		Revision as of 14:48, 22 September 2014
Line 170:		Line 170:
	==Trouble Processing xlsx or accdb formats==		==Trouble Processing xlsx or accdb formats==
	MatchUp can not process xlsx nor accdb files when 64-bit msOffice is installed. The workaround is to install 32-bit msOffice or to process the files as a csv format.		MatchUp can not process xlsx nor accdb files when 64-bit msOffice is installed. The workaround is to install 32-bit msOffice or to process the files as a csv format.


	[[MatchUp Software\|Back to MatchUp Main Page]]		[[MatchUp Software\|Back to MatchUp Main Page]]

Tim at 14:48, 22 September 2014

2014-09-22T14:48:30Z

← Older revision		Revision as of 14:48, 22 September 2014
Line 3:		Line 3:
	Access and Excel are difficult formats to process because they are in a proprietary Microsoft format. Because of this, we are forced to use Microsoft's prescribed methods to access data. MatchUp uses one of two technologies for doing this: ADO and DAO.		Access and Excel are difficult formats to process because they are in a proprietary Microsoft format. Because of this, we are forced to use Microsoft's prescribed methods to access data. MatchUp uses one of two technologies for doing this: ADO and DAO.
	This document is intended to provide steps in working around the default behavior and providing MatchUp with formats it can handle.		This document is intended to provide steps in working around the default behavior and providing MatchUp with formats it can handle.


			[[MatchUp Software\|Back to MatchUp Main Page]]


Line 163:		Line 166:
	# Right-click and select Paste Special. Select Values.		# Right-click and select Paste Special. Select Values.
	# You can delete the original column of badly formatted Zip Codes.		# You can delete the original column of badly formatted Zip Codes.


			==Trouble Processing xlsx or accdb formats==
			MatchUp can not process xlsx nor accdb files when 64-bit msOffice is installed. The workaround is to install 32-bit msOffice or to process the files as a csv format.

			[[MatchUp Software\|Back to MatchUp Main Page]]

Tim: /* Choose an alternate format */

2014-01-27T18:46:18Z

Choose an alternate format

← Older revision		Revision as of 18:46, 27 January 2014
Line 143:		Line 143:
	If all else fails, converting the files to dBASE (or .csv) will work		If all else fails, converting the files to dBASE (or .csv) will work
	Our dBASE driver was written in-house and is the most stable and fastest database type available for output tables.		Our dBASE driver was written in-house and is the most stable and fastest database type available for output tables.

	If your Output table must be returned in its native format, it is usually quicker to process in dbase and import the output table to the native program.		If your Output table must be returned in its native format, it is usually quicker to process in dbase and import the output table to the native program.

	Although .csv is not as fast as dBASE, and does not allow you to use some of the advanced options (to write results back to the source table), it is a much more stable option than Excel or Access.		Although .csv is not as fast as dBASE, and does not allow you to use some of the advanced options (to write results back to the source table), it is a much more stable option than Excel or Access.

Tim at 18:45, 27 January 2014

2014-01-27T18:45:07Z

← Older revision		Revision as of 18:45, 27 January 2014
Line 151:		Line 151:
	Wouldn't we like to know... Excel is very quirky when working with Zip Codes. Sometimes it will get convert them all to numbers. Although that's fine in the midwest, up in the northeast one or more digit of the zip code can be lost.		Wouldn't we like to know... Excel is very quirky when working with Zip Codes. Sometimes it will get convert them all to numbers. Although that's fine in the midwest, up in the northeast one or more digit of the zip code can be lost.
	MatchUp does a good job of converting ‘excel-ed’ zip codes to their five digit format but problems can arise if there is a mix of zip5 and zip9 codes. Since excel will report that the field is numeric, the zip9 codes containing a dash will get wiped out, and MatchUp will see these records as not containing a zip code.		MatchUp does a good job of converting ‘excel-ed’ zip codes to their five digit format but problems can arise if there is a mix of zip5 and zip9 codes. Since excel will report that the field is numeric, the zip9 codes containing a dash will get wiped out, and MatchUp will see these records as not containing a zip code.
	One way to handle this (sometimes this won't work!):
	1. Insert a blank column next to the Zip field		One way to handle this (sometimes this won't work!):
	2. On the first row of data, type =TEXT(C1,0), where C1 is the cell containing the zip code.
	3. Copy the function for the rest of the column (the C1 should correctly adjust to C2, C3, etc as you go down). Your northeast Zip Codes should now read correctly.		# Insert a blank column next to the Zip field
	4. Click at the very top of the column to highlight the entire row.		# On the first row of data, type =TEXT(C1,0), where C1 is the cell containing the zip code.
	5. Right-click and select Copy.		# Copy the function for the rest of the column (the C1 should correctly adjust to C2, C3, etc as you go down). Your northeast Zip Codes should now read correctly.
	6. Right-click and select Paste Special. Select Values.		# Click at the very top of the column to highlight the entire row.
	7. You can delete the original column of badly formatted Zip Codes.		# Right-click and select Copy.
			# Right-click and select Paste Special. Select Values.
			# You can delete the original column of badly formatted Zip Codes.

Tim at 18:26, 27 January 2014

2014-01-27T18:26:40Z

← Older revision		Revision as of 18:26, 27 January 2014
Line 126:		Line 126:

	Notes:		Notes:
	• These settings are global, and are not setup file specific.		*These settings are global, and are not setup file specific.
	• If you omit any entry or specify an invalid one, the default will be used.		*If you omit any entry or specify an invalid one, the default will be used.

	This setup structure will process, where the previous would crash for many files…		This setup structure will process, where the previous would crash for many files…
Line 135:		Line 135:
	==CASS Considerations==		==CASS Considerations==
	Unlike some other database types, MatchUp CASS process does not allow you to append columns to the Excel or Access table you are processing. You must select an existing column. So before processing, you must create the columns in the respective program, using the recommended troubleshooting guidelines above.		Unlike some other database types, MatchUp CASS process does not allow you to append columns to the Excel or Access table you are processing. You must select an existing column. So before processing, you must create the columns in the respective program, using the recommended troubleshooting guidelines above.
	~~Especially with CASS (but also Merge Purge), make sure that you are allowing zero-length (aka, 'Allow NULLs') fields.~~

			Especially with CASS (but also Merge Purge), make sure that you are allowing zero-length (aka, 'Allow NULLs') fields.

			[[File:cassCons.png]]

	==Choose an alternate format==		==Choose an alternate format==

Tim at 18:16, 27 January 2014

2014-01-27T18:16:18Z

← Older revision		Revision as of 18:16, 27 January 2014
Line 129:		Line 129:
	• If you omit any entry or specify an invalid one, the default will be used.		• If you omit any entry or specify an invalid one, the default will be used.

			This setup structure will process, where the previous would crash for many files…

			[[File:ofm2.png]]

	==CASS Considerations==		==CASS Considerations==

Tim at 18:12, 27 January 2014

2014-01-27T18:12:26Z

← Older revision		Revision as of 18:12, 27 January 2014
Line 79:		Line 79:
	FIELD COLUMN NAMES		FIELD COLUMN NAMES
	Since some database systems have restrictions on column names, MatchUp follows suit and will not create these Output Table types if the column names are seen by MatchUp as multi-word or have a length of over 10 characters.		Since some database systems have restrictions on column names, MatchUp follows suit and will not create these Output Table types if the column names are seen by MatchUp as multi-word or have a length of over 10 characters.

	Excel allows duplicate field names. MatchUp tries to do what it can with these, but sometimes comes up short. It's best just to fix the problem in the native DBMS before working with the file in MatchUp. And better database practice!		Excel allows duplicate field names. MatchUp tries to do what it can with these, but sometimes comes up short. It's best just to fix the problem in the native DBMS before working with the file in MatchUp. And better database practice!

	A good rule of thumb – if you look at only the first 10 characters of the column names, and there are more than one instance, or embedded spaces, the program may crash.		A good rule of thumb – if you look at only the first 10 characters of the column names, and there are more than one instance, or embedded spaces, the program may crash.

	FIELD TYPES		FIELD TYPES
	If the table has a few 'oddball' fields, you may want to make a copy of the table and try removing the fields. Such fields would be non-character and non-numeric fields, and not critical to the deduping process or the desired output table.
			If the table has a few 'oddball' fields, you may want to make a copy of the table and try removing the fields. Such fields would be non-character and non-numeric fields, and not critical to the deduping process or the desired output table.

	If all fields are required in Output, you can try changing the output column type. For example, different versions of Excel and Access contain options for numeric data, logic fields and dates. If you find one of these to be problematic, try changing the output type to ‘fixed character’.		If all fields are required in Output, you can try changing the output column type. For example, different versions of Excel and Access contain options for numeric data, logic fields and dates. If you find one of these to be problematic, try changing the output type to ‘fixed character’.

	For a hands off, advanced approach, you can edit your .ini file…		For a hands off, advanced approach, you can edit your .ini file…

	'''Warning: This is an advanced topic. Proceed only if you know what you are doing!'''		'''Warning: This is an advanced topic. Proceed only if you know what you are doing!'''

	~~<span style="color:#FF0000"> This text will be red </span>~~

	When MatchUp needs to create a table, it normally uses data from the chart below to determine what DBMS (ie, 'native') data type should be used for a given MatchUp field type:		When MatchUp needs to create a table, it normally uses data from the chart below to determine what DBMS (ie, 'native') data type should be used for a given MatchUp field type:

Tim at 18:07, 27 January 2014

2014-01-27T18:07:35Z

← Older revision		Revision as of 18:07, 27 January 2014
Line 87:		Line 87:
	For a hands off, advanced approach, you can edit your .ini file…		For a hands off, advanced approach, you can edit your .ini file…

	Warning: This is an advanced topic. Proceed only if you know what you are doing!		'''Warning: This is an advanced topic. Proceed only if you know what you are doing!'''

			<span style="color:#FF0000"> This text will be red </span>

	When MatchUp needs to create a table, it normally uses data from the chart below to determine what DBMS (ie, 'native') data type should be used for a given MatchUp field type:		When MatchUp needs to create a table, it normally uses data from the chart below to determine what DBMS (ie, 'native') data type should be used for a given MatchUp field type: