4 Replies Latest reply on May 29, 2013 12:06 PM by flybynight

    Data issues with export from Quickbooks, .csv ... Excel

    flybynight

      OK, so I did a bunch of exports from Quickbooks in .csv format.

      Having some issues importing into FMP… particularly with fields that have a quote " in the field. Usually something denoting inches, like "2 1/2" address labels" etc.

      When it hits that ", it messes up the "," field delimiter pattern, and you end up with a string of several fields, even several records, in one field.

       

      I tried bringing it in to Excel first… that helped, but in that case, it drops the " in the middle, and you are left with a trailing " at the end of the field. Not the end of the world, but still annoying.

       

      The other issue is some that number fields that end with a decimal like "256.90" end up in FMP as something like 256.89999999995 - and I know it was only "256.90" in the .csv and shows up as "256.9" in the Excel file. I also tried saving as a tab-delimted .txt file, but in that case, and fields with commas in them ended up wraped in quotes like "Company Name, Inc."

       

      If anyone knows of solutions to these minor issues, let me know. For what it is, we can deal with it, but if there is an easy out, I'll take it.

      It might be an issue to go back to the QB file to export in a different format, but not super convenient for me to get access to it.

       

      Thanks,

      -Shawn

        • 1. Re: Data issues with export from Quickbooks, .csv ... Excel
          mikebeargie

          For the decimal issue, are you opening up the CSV in a text editor or excel to confirm? Filemaker treats number fields as raw numbers, and it wouldn't arbitrarily change 256.9 to 256.89999999995. I do know that excel will mask numbers it thinks are currency (any decimal really) to two decimal places sometimes (CSV or XLS/XLSX), but formatting all columns as text will show the complete decimal data.

           

          As for everything else, sounds like the trials and tribulations of dealing with not so great data coming from quickbooks. If quickbooks is not escaping text string quotes in the CSV output, then either a different format that supports those strings (IE XLS) should be what's used for the cleanest data.

           

          My recommendation would be to create a standalone file for "processing" your data, and script the first time through all the replaces you need to do for cleanup. You can then run data through this processing file to clean it up in the future, and automate the entire process.

           

          It might also be worth it to get some advice from some of the companies dealing with Quickbooks and filemaker. Such as Productive Computing and Seedcode.

          • 2. Re: Data issues with export from Quickbooks, .csv ... Excel
            usbc

            A search for QIF to CSV software will find desktop aps from $16 - $99.

            • 3. Re: Data issues with export from Quickbooks, .csv ... Excel
              PSI

              Shawn,

               

              Not to be redundant but you could avoid all these issues, and the time invovled in figuring out how to fix them by using FMBooks...:-)

               

              John Morina

              • 4. Re: Data issues with export from Quickbooks, .csv ... Excel
                flybynight

                Mike,

                 

                That one is just odd. I started with a .csv out of QB. I open that with a text editor (TextWrangler) and can see that it is 256.90. I open that in excel, and it truncates it to 256.9, like I would expect Excel to treat something that it sees as a number. It's only when I import the .xlsx into FMP that it changes. Obviously, I can round it, or just display it with 2 decimal places in FMP. It's not actually a big deal… just unexpected behavior.

                 

                Thanks!

                -Shawn