When you need to import a file into QuickBooks using Transaction Pro there are two ways it can go. Either your formatted file is formatted exactly right for import OR you may need to do some reformatting or resolving of issues that will not play nicely with QuickBooks and will cause errors on import.
Many people tend to use products like Excel to clean up their data, but it can be very time-consuming and error-prone. When using another program, it is also possible that you will accidentally reformat the data, dropping prefixes with zeros or converting dates into numbers, etc. The possibility of errors is endless.
If your file looks like the below image, you will see that there are two common errors represented:
1. Empty line feeds
2. Unwanted characters. When you try to import this file as-is into both QuickBooks Desktop or Quickbooks online, QuickBooks will return the following error through Transaction Pro:
Here are 2 very simple scripts that you can incorporate into a batch script to eliminate these 2 issues:
Rather than use Excel, use notepad to write a command script that can be applied to our .csv file and therefore will eliminate potential errors. To do this you will need to go to Start>Notepad and then copy and paste or type the scripts I show below in these examples. When you are done, save the file in Notepad with a .BAT extension. Once you save the file in this format, you can launch the script by double-clicking the file that you just created. Once it completes, you will notice that your .csv file has had the appropriate changes applied and then you are ready to import into QuickBooks using Transaction Pro!
- To Remove Empty line feeds – use this script and these parameters
The commands highlighted in yellow create a “Find and Replace” command to replace the “$” with nothing. Here is the result of that command:
You can see already that the file is cleaner and import ready. And there are no “$” signs.
Once this new clean data file is uploaded into Transaction Pro it is in perfect condition to import successfully into QuickBooks.
Command-line scripting is an extremely fast, accurate, and efficient way to clean up your data. It also avoids the issue of incorrect data reformatting that often occurs if you try to manually edit in Excel. Next time you have a data file that isn’t quite ready for import, try this method and you will be amazed at how effective it is. Happy importing!
