Have client that posts financial data to Excel worksheets over a 5 week period and then manual enters data to Quickbooks enterprise on a desktop.
Wants to automate process by extracting various cells and post them to QB.
Tried to import Excel file directly via AlphaDAO and it inserts heading dates into dollar fields in cells below. Exported to CSV format and imported thru operation function with numeric fields for dollars fields. It converts date heading to numeric, i.e. 12/31/2102 to 12. Not a big problem, as I can prompt for dates. Negative values are format as
(20.00), instead of -20.00, resulting on zero value in import. can have them change to number format for input. Big problem is any value over 3 digits has first 3 digits dropped. i.e 1,974.12 becomes 1.00, 2,270.00 becomes 2.00. If I import with dollar fields as character, everything comes over find, except I don't think QB would like posting a character field to a numeric field. Even tried to use copy record operation with output field being numeric, but it carred over the input format, changing numeric output to character.
Anybody know why over 3 digit fields are being screwed up or why date headings are replacing dollar fields when trying to connect directly to Execl file?
I will be using xbasic to pull off the cells/rows(i.e. 27-34, 37-59,etc) I need to post to QB and create another file to use for posting. Hopefully using xbasic to move the character dollar field to a numeric field will solve the problem.
The next challenge will be to connect directly with QB desktop to do posting. The alterative is to create IIF import transaction that can be imported into QB.
How do I add attachments of Excel file and dbf imports?
Wants to automate process by extracting various cells and post them to QB.
Tried to import Excel file directly via AlphaDAO and it inserts heading dates into dollar fields in cells below. Exported to CSV format and imported thru operation function with numeric fields for dollars fields. It converts date heading to numeric, i.e. 12/31/2102 to 12. Not a big problem, as I can prompt for dates. Negative values are format as
(20.00), instead of -20.00, resulting on zero value in import. can have them change to number format for input. Big problem is any value over 3 digits has first 3 digits dropped. i.e 1,974.12 becomes 1.00, 2,270.00 becomes 2.00. If I import with dollar fields as character, everything comes over find, except I don't think QB would like posting a character field to a numeric field. Even tried to use copy record operation with output field being numeric, but it carred over the input format, changing numeric output to character.
Anybody know why over 3 digit fields are being screwed up or why date headings are replacing dollar fields when trying to connect directly to Execl file?
I will be using xbasic to pull off the cells/rows(i.e. 27-34, 37-59,etc) I need to post to QB and create another file to use for posting. Hopefully using xbasic to move the character dollar field to a numeric field will solve the problem.
The next challenge will be to connect directly with QB desktop to do posting. The alterative is to create IIF import transaction that can be imported into QB.
How do I add attachments of Excel file and dbf imports?
Comment