解析 Quickbook IIF 格式文件

Question

我正在使用 Quickbook 的 IIF 文件格式，需要编写一个解析器来读取和写入 IIF 文件，并且在读取文件时遇到一些问题。

这些文件很简单，它们是制表符分隔的。 每一行要么是一个表定义，要么是一行。 定义以“!”开头 和表名，行仅以表名开头。 这是我遇到的问题：某些字段允许换行。

当我第一次遇到这个问题时，我想，好吧，只需逐个选项卡而不是逐行解析它，但要做到这一点，我必须用制表符替换换行符，并最终获得比列更多的值，但我缠绕带有换行符的值分布在太多列中。

您将如何解析这样的文件？

编辑：一个例子

!CUST   NAME    REFNUM  TIMESTAMP   BADDR1  BADDR2  BADDR3  BADDR4  BADDR5  SADDR1  SADDR2  SADDR3  SADDR4  SADDR5  PHONE1  PHONE2  FAXNUM  CONT1   CONT2   CTYPE   TERMS   TAXABLE LIMIT   RESALENUM   REP TAXITEM NOTEPAD SALUTATION  COMPANYNAME FIRSTNAME   MIDINIT LASTNAME    CUSTFLD1    CUSTFLD2    CUSTFLD3    CUSTFLD4    CUSTFLD5    CUSTFLD6    CUSTFLD7    CUSTFLD8    CUSTFLD9    CUSTFLD10   CUSTFLD11   CUSTFLD12   CUSTFLD13   CUSTFLD14   CUSTFLD15   JOBDESC JOBTYPE JOBSTATUS   JOBSTART    JOBPROJEND  JOBEND  HIDDEN  DELCOUNT
CUST    St. Mark    359 1176670332  Saint Mark Catholic Church  609 W Main St   City, State Zip 
!CLASS  NAME    REFNUM  TIMESTAMP   HIDDEN  DELCOUNT
!INVITEM    NAME    REFNUM  TIMESTAMP   INVITEMTYPE DESC    PURCHASEDESC    ACCNT   ASSETACCNT  COGSACCNT   QNTY    QNTY    PRICE   COST    TAXABLE PAYMETH TAXVEND TAXDIST PREFVEND    REORDERPOINT    EXTRA   CUSTFLD1    CUSTFLD2    CUSTFLD3    CUSTFLD4    CUSTFLD5    DEP_TYPE    ISPASSEDTHRU    HIDDEN  DELCOUNT    USEID
INVITEM Labor   1   1119915308  SERV    Labor                                                                                               0
!TIMEACT    DATE    JOB EMP ITEM    PITEM   DURATION    PROJ    NOTE    XFERTOPAYROLL   BILLINGSTATUS
TIMEACT 3/8/08  876 Development Jane Doe {Consultant}   Labor       00:15       Renewing all domain name for 876 Development.
REIMBURSEMENT: 44.72 for one year renewal on all domain names.  N   1
TIMEACT 3/17/08 Greg:Bridge Jane Doe {Consultant}   Labor       01:00       Preparing Studio    N   1
TIMEACT 3/17/08 John Doe and Associates Jane Doe {Consultant}   Labor       00:06       REIMBURSEMENT: Toner cartridge on ebay & Fuser from FastPrinters- ask wendell before invoicing to see if this fixed the problem
49.99 (include tax) toner
$175.18 (include tax) fuser
    N   1
TIMEACT 3/17/08 John Doe II Jane Doe {Consultant}   Labor       01:00       Fixing Kandis's computer - replaced entire computer with similar system N   1

Answer 1

我做到了：

 public DataSet parseIIF(Stream file) {
            iifSet = new DataSet();
            String fileText;

            using (StreamReader sr = new StreamReader(file)) {
                fileText = sr.ReadToEnd();
            }
            //replace line breaks with tabs
            //fileText.Replace('\n', '\t');
            fileText = fileText.Replace("\r\n", "\n");
            fileText = fileText.Replace('\r', '\n');

            //split along tabs
            string[] lines = fileText.Split('\n');

            this.createTables(lines, iifSet);
            this.fillSet(lines, iifSet);

            return iifSet;
        }

        /// <summary>
        /// Reads an array of lines and parses them into tables for the dataset
        /// </summary>
        /// <param name="lines">String Array of lines from the iif file</param>
        /// <param name="iifSet">DataSet to be manipulated</param>
        private void fillSet(string[] lines, DataSet set) {
            //CODING HORROR
            //WARNING: I will monkey with the for loop index, be prepared!
            for (int i = 0; i < lines.Length; i++) {
                if (this.isTableHeader(lines[i])) {
                    //ignore this line, tables are alread defined
                    continue;
                }
                if (lines[i] == "" || lines[i] == "\r" || lines[i] == "\n\r" || lines[i] == "\n") {
                    //ignore lines that are empty if not inside a record
                    //probably the end of the file, it always ends with a blank line break
                    continue;
                }

                if (lines[i].IndexOf(";__IMPORTED__") != -1) {
                    continue;
                    //just signifying that it's been imported by quickbook's timer before, don't need it
                }

                string line = lines[i];
                while (!isFullLine(line, set)){
                    i++;            //<--------------------------- MONKEYING done here!
                    line += lines[i];       
                }
                //now, the line should be complete, we can parse it by tabs now
                this.parseRecord(line, set);
            }
        }

        private void parseRecord(string line, DataSet set) {
            if (isTableHeader(line)) {
                //we don't want to deal with headers here
                return;
            }

            String tablename = line.Split('\t')[0];
            //this just removes the first value and the line break from the last value
            String[] parameters = this.createDataRowParams(line);

            //add it to the dataset
            set.Tables[tablename].Rows.Add(parameters);
        }

        private bool isFullLine(string line, DataSet set) {
            if (isTableHeader(line)) {
                return true;    //assumes table headers won't have line breaks
            }
            int values = line.Split('\t').Length;
            string tableName = line.Split('\t')[0];
            int columns = set.Tables[tableName].Columns.Count;

            if (values < columns) {
                return false;
            } else {
                return true;
            }
        }

        private void createTables(string[] lines, DataSet set) {
            for (int index = 0; index < lines.Length; index++) {
                if (this.isTableHeader(lines[index])) {
                    set.Tables.Add(createTable(lines[index]));
                }
            }
        }

        private bool isTableHeader(string tab) {
            if (tab.StartsWith("!"))
                return true;
            else
                return false;
        }

        private bool isNewLine(string p) {
            if (p.StartsWith("!"))
                return true;
            if (iifSet.Tables[p.Split('\t')[0]] != null)    //that little mess there grabs the first record in the line, sorry about the mess
                return true;
            return false;
        }

    private DataTable createTable(string line) {
        String[] values = line.Split('\t');

        //first value is always the title
        //remove the ! from it
        values[0] = values[0].Substring(1);     //remove the first character
        DataTable dt = new DataTable(values[0]);
        values[0] = null;   //hide first title so it doesn't get used, cheaper than resetting array
        foreach (String name in values) {
            if (name == null || name == "")
                continue;
            DataColumn dc = new DataColumn(name, typeof(String));
            try {
                dt.Columns.Add(dc);
            } catch (DuplicateNameException) {
                //odd
                dc = new DataColumn(name + "_duplicateCol" + dt.Columns.Count.ToString());
                dt.Columns.Add(dc);
                //if there is a triple, just throw it
            }
        }

        return dt;
    }

   private string getTableName(string line) {
        String[] values = line.Split('\t');

        //first value is always the title
        if(values[0].StartsWith("!")){
            //remove the ! from it
            values[0] = values[0].Substring(1);     //remove the first character
        }
        return values[0];
    }

    private string[] createDataRowParams(string line) {
        string[] raw = line.Split('\t');
        string[] values = new string[raw.Length - 1];

        //copy all values except the first one
        for (int i = 0; i < values.Length; i++) {
            values[i] = raw[i + 1];
        }

        //remove last line break from the record
        if (values[values.Length - 1].EndsWith("\n")) {
            values[values.Length - 1] = values[values.Length - 1].Substring(0, values[values.Length - 1].LastIndexOf('\n'));
        } else if (values[values.Length - 1].EndsWith("\n\r")) {
            values[values.Length - 1] = values[values.Length - 1].Substring(0, values[values.Length - 1].LastIndexOf("\n\r"));
        } else if (values[values.Length - 1].EndsWith("\r")) {
            values[values.Length - 1] = values[values.Length - 1].Substring(0, values[values.Length - 1].LastIndexOf('\r'));
        }


        return values;
    }

    private string[] createDataRowParams(string line, int max) {
        string[] raw = line.Split('\t');

        int length = raw.Length - 1;
        if (length > max) {
            length = max;
        }

        string[] values = new string[length];
        for (int i = 0; i < length; i++) {
            values[i] = raw[i + 1];
        }

        if (values[values.Length - 1].EndsWith("\n")) {
            values[values.Length - 1] = values[values.Length - 1].Substring(0, values[values.Length - 1].LastIndexOf('\n'));
        } else if (values[values.Length - 1].EndsWith("\n\r")) {
            values[values.Length - 1] = values[values.Length - 1].Substring(0, values[values.Length - 1].LastIndexOf("\n\r"));
        } else if (values[values.Length - 1].EndsWith("\r")) {
            values[values.Length - 1] = values[values.Length - 1].Substring(0, values[values.Length - 1].LastIndexOf('\r'));
        }

        return values;
    }

Answer 2

我已经有一段时间没有完成 IIF 了，但是除非他们修复了它，否则 QuickBooks 无论如何都会在这些换行符上呕吐。 看来这些人也有同样的问题，他们用空格处理了它。

就我个人而言，当进入 QuickBooks 时，我会倾向于管道或能够清楚地描绘换行符的东西。 如果您绝对必须有换行符，请加入 Intuit 开发者网络 并使用 SDK 发送这些值一旦你的程序导入它们，就发送给 QB。

Answer 3

为什么不用空格而不是制表符来替换换行符？

Answer 4

我经常遇到这样的事情。 关键是在进行解析时处理此类特殊情况，即将特殊情况替换为文本中极不可能出现的内容，然后在完成后再次替换。

例如，输出中存在换行符，可以使用正则表达式轻松检测到换行符。 使用 Regex.Replace 将它们转换为 LINEBREAK 之类的内容。 使其在编辑器中脱颖而出以进行调试。 然后照常进行其余的解析，最后一步，用原始值（或新值）替换特殊标记。

Answer 5

想法：

1. 预处理你的文件，用一些高位 ASCII 字符替换换行符（假设它是单个 CR 或 LF）。 然后按制表符进行解析，最后用换行符替换所述高位 ASCII 字符。

2. 不要逐行处理，而是逐字符处理。 请注意，只有当嵌入的换行符与记录末尾的标准 CRLF 有所不同时，这仍然有效。

Answer 6

我在 CodePlex 上找到了这个。 您可以使用 nugget 包获取它。
https://qif.codeplex.com/

我测试了它，我能够读写快速书籍格式。