tags:

views:

1628

answers:

3

I'm writing a C# program to convert a FoxPro database to XML, and everything works except the memo field is blank. Is there something I'm missing to convert that bit?

I'm using C# .Net 3.5 SP1, Visual FoxPro 9 SP 1 OLE DB Driver. Connection string is okay, as all other data is being pulled properly.

When I converted the FoxPro database to SQL Server, the memo field is also blank there, so I can't convert twice.

A: 

I am not terribly familiar with C# or FoxPro or SQL Server, so I cannot give you much advice in that regard.

However, if you cannot find a suitable driver, you may consider parsing the raw data and memo files yourself. Another question has dealt with this:

http://stackoverflow.com/questions/37535/whats-the-easiest-way-to-read-a-foxpro-dbf-file-from-python

Believe it or not, these file formats are quite simple to parse should you decide to write your own C# parser. These specifications are available from Microsoft:

Adam Paynter
Talk about making things tricky - there are several ways to open FoxPro tables in .NET - Linking through Access is simple...
Matt
@Matt: Perhaps you should post that as an answer?
Adam Paynter
+1  A: 

I use ODBC to link VFP 8 tables and the memo fields work with no problem. I don't know if OLEDB is different.

You might not have Visual FoxPro tables here. Many VFP systems use the same tables as the FoxPro 2 or dBase application they replaced. You could look at the file header or just try one of the other ODBC drivers to see if they work.

geoff franklin
Still no luck. Using this connection string (string.Format()'d of course): "Driver={{Microsoft Visual FoxPro Driver}};SourceType=DBF;SourceDB={0};Exclusive=No; Collate=Machine;NULL=NO;DELETED=NO;BACKGROUNDFETCH=NO;"Tried to query normally and as left(column_name, 8000).
Jonathan DeMarks
+2  A: 

Ended up having to do some work myself, but maybe it can help someone else out in the future:

        public static object GetDbaseOrFoxproRawValue(string DBPath, string TableName, string ColumnName, 
        string CompareColumnName, string CompareValue, bool CompareColumnIsAutoKey)
    {
        using (BinaryReader read = new BinaryReader(File.Open(
            Path.Combine(DBPath, TableName + ".dbf"), FileMode.Open, FileAccess.Read, FileShare.ReadWrite)))
        {
            // Is it a type of file that I can handle?
            if (new byte[] { 0x02, 0x03, 0x30, 0x43, 0x63, 0x83, 0x8b,
                             0xcb, 0xf5, 0xfb }.Contains(read.ReadByte()))
            {
                // Skip date.
                read.BaseStream.Seek(3, SeekOrigin.Current);

                // Read useful datas...
                uint RecordCount = read.ReadUInt32();
                ushort FirstRecord = read.ReadUInt16();
                ushort RecordLength = read.ReadUInt16();
                int FieldCount = FirstRecord - 296 / 32;

                // Make sure things aren't stupid.
                ColumnName = ColumnName.ToLower();
                CompareColumnName = CompareColumnName.ToLower();

                // Find target column (field)
                string temp;
                UInt32 CompareFieldOffset = uint.MaxValue, FieldOffset = uint.MaxValue;
                byte CompareFieldLength = 0, FieldLength = 0;
                char FieldType = ' ';
                for (int i = 0; i < FieldCount; i++)
                {
                    read.BaseStream.Seek(32 + (i * 32), SeekOrigin.Begin);
                    temp = Encoding.ASCII.GetString(read.ReadBytes(11)).Replace("\0", "").ToLower();
                    if (temp == CompareColumnName)
                    {
                        read.ReadChar();
                        CompareFieldOffset = read.ReadUInt32();
                        CompareFieldLength = read.ReadByte();
                    }
                    if (temp == ColumnName)
                    {
                        FieldType = read.ReadChar();
                        FieldOffset = read.ReadUInt32();
                        FieldLength = read.ReadByte();
                    }

                    if (CompareFieldOffset != uint.MaxValue && FieldOffset != uint.MaxValue)
                        break;
                }

                // Make sure we can continue.
                if (CompareFieldOffset == uint.MaxValue || 
                    FieldOffset == uint.MaxValue) return null;

                // Iterate through each record to find the one we want.
                for (int index = 0; index < RecordCount; index++)
                {
                    read.BaseStream.Seek(FirstRecord + (index * RecordLength) + CompareFieldOffset, SeekOrigin.Begin);
                    temp = Encoding.Default.GetString(read.ReadBytes(CompareFieldLength)).Replace("\0", "");
                    if (temp == CompareValue)
                    {
                        read.BaseStream.Seek(FirstRecord + (index * RecordLength) + FieldOffset, SeekOrigin.Begin);
                        switch (FieldType)
                        {
                            case 'M':
                            case 'I': return read.ReadUInt32();
                            case 'C':
                            default: return Encoding.Default.GetString(read.ReadBytes(FieldLength)).Replace("\0", "");
                        }
                    }
                }
            }
            else
            {
                return null;
            }
        }

        return null;
    }

Just grab the result from that and use it as an index into the memo file (that code is pretty simple using the MSDN documentation).

Jonathan DeMarks