Error when importing from Excel

Contributions, Faith Promises

Error when importing from Excel

Postby rmorisset » Sun Dec 18, 2016 9:28 am

Good morning PC community,

I need your help. I have imported contributions from Excel into PC and I believe that I have formatted the data as required. My findings is that for any amounts above $1,000, only the first digit are considered. For example, if amount in the csv file is $1,060, the imported amount is $1.00. If the amount were to be $2,000, the imported value is $2.00 and so on and so forth. Any suggestion on what I need to look at, or is there anything that I'm doing wrong?

Regards,

Richard Morisset
rmorisset
 
Posts: 24
Joined: Tue Nov 08, 2011 3:47 pm

Re: Error when importing from Excel

Postby NeilZ » Sun Dec 18, 2016 1:11 pm

rmorisset wrote:Good morning PC community,

I need your help. I have imported contributions from Excel into PC and I believe that I have formatted the data as required. My findings is that for any amounts above $1,000, only the first digit are considered. For example, if amount in the csv file is $1,060, the imported amount is $1.00. If the amount were to be $2,000, the imported value is $2.00 and so on and so forth. Any suggestion on what I need to look at, or is there anything that I'm doing wrong?

Regards,

Richard Morisset


You need to format the Excel columns to the exact requirements of the system.

The file must contain the following fields in this order:
1. Envelope Number - (Number, up to 5 digits) - This is used to connect the Contributions record to the Personal Profile of the contributor.

2. Amount - (Number, up to 9 digits with 2 decimal places) - Amount of the individual contribution.

3. Date - (Date) - The date of the individual contribution. Formatted as MM/DD/YYYY or DD/MM/YYYY, depending on your regional settings

4. Fund Number - (Number, up to 5 digits) - This is used to define which Contribution Fund this was given to.

5. Check Number - (Character, up to 20 digits) - The contribution check number, if applicable.

6. Memo - (Character, up to 50 characters) - A note or memo on the contribution, if applicable.



1, 2 & 4 can be formatted as 'general' numbers. 1 & 4 are easy, and the system will take the comma delimiter as the end of the number, however item 2 must show 2 decimal places as the system expects it.

So if someone Donations $25, you have to enter 2500 in the cell.

Here is an export of three columns, the first was formatted as general, the second as a number with two decimal places, and the last as currency. In each column the number 25 and nothing else was entered. Notice I did not ask that the format include a comma for thousands.

25,25.00,$25.00

I'm fairly sure that the 2nd and 3rd columns would return the error you're seeing if you tried to import. The first would import as 25 cents.

You have to include the full decimal place when entering this, so if the donation is $25, you have to enter 2500, no period the system will see the 00 as the decimal places and import $25.00

Does this help any ??

Neil Zampella

Using PC+ since 1999.
NeilZ
 
Posts: 9108
Joined: Wed Oct 08, 2003 12:20 am
Location: Connellsville, PA

Re: Error when importing from Excel

Postby rmorisset » Tue Dec 20, 2016 1:20 am

Neil,

Thanks for your input. I don't think that your theory is accurate as I have values that are up to three digits...such as $46 (appears as 46 in the csv file) or $160 (appears as 160 in csv file)... and they did not import as $0.46 (46 cents) nor $1.60; in other words, I did not have to pad the cells with two extra zeroes for these to be correct. The error only surfaced when my cvs values contain a comma in the number. Currently, my recourse for this was to manually change each of these (I had 39 of them) to the correct values. I will try this again later to re-test your theory. Thank you.

On the same importing subject, I have another question. While importing the data, we have to choose a "contribution type". Now, since most of my contributions were cash, I chose cash; however, this also forced all imported values (including check contributions) to be treated as cash. Is there a way to change the contribution type of all donations which have a check number to "check", in lieu of the global chosen "cash" value.

Regards,

Richard
rmorisset
 
Posts: 24
Joined: Tue Nov 08, 2011 3:47 pm

Re: Error when importing from Excel

Postby Zorak » Tue Dec 20, 2016 9:41 am

Have you looked at the CSV file outside of Excel? Try opening it in Notepad or something equivalent, so you get away from Excel's formatting. It sounds like there is some strange number formatting going on on the screen that is not actually reflected in the CSV text file.
Zorak
Tech Support
Tech Support
 
Posts: 2571
Joined: Thu May 13, 2004 8:59 am
Location: PowerChurch Software

Re: Error when importing from Excel

Postby rmorisset » Tue Dec 20, 2016 10:31 am

Thanks for your input Zorak. Using Notepad, as suggested to look at the csv file, I noticed a double quotation mark ("1,000.00") around every number greater than 1,000. I am not sure why this happened, but at least, as you pointed out, there is some strange number formatting happening in the file. Will play with it at a later time to investigate a little bit more.

On the same token, do you know if there is a way to convert all of the contributions that have a check number from cash to check without doing this task one at a time?

Regards,

Richard
rmorisset
 
Posts: 24
Joined: Tue Nov 08, 2011 3:47 pm