none
Reading CSV and delimited-files in Foxpro

    General discussion

  • Because FoxPro has difficulties with reading CSV and other delimited files I have programmed the following EXE, witch translates delimited data to a DBF-file in no-time:

    ReadDeli.exe

    Syntax

    readdeli <nHeader> <nDeli> <nQuot> <sDbf>

    Parameters:

    • nHeader: 1 if the data-file contains a fieldname row 0 if other
    • nDeli: ASCII-value of the delimiter-char (example. 39 for ‘,’ 59 for ‘;’)
    • nQuot: ASCII- value of the quote-char else 0 (example. 34 for ”)
    • sDbf: name of the generated DBF-file (if empty this becomes readdeli.dbf)

    Example for FoxPro

    !readdeli nogomailing.csv 1 59 34 readin

    Makes the DBF-file readin.dbf out of nogomailing.csv with fieldnames from the first row, delimited with Semicolons (character 59) and with all values enclosed by Quotes (char 34).

    ReadDeli.exe is written in C# and works very fast. Other advantages:

    • Fields in the DBF have the correct length.
    • CSV-files with CR (13) or LF (10) characters in the data are converted correctly (if the correct nQuot parameter is provided)
    • Fields with values longer then 254 chars are automatically converted to MEMO
    • ReadDeli.exe doesn’t depend on other software (not even Foxpro)

    Additional options:

    Option

    Function

    /P=10

    makes fields 10 longer then the longest value (instead of 1, the default padding)

    /F=20

    makes fields minimum 20 long (fixed)

    /U8, /UN, /U

    converts the text-file form codepages UTF8, UNICODE or looks at the BOM (Byte Order Mark) for conversion

    /U8=1251

    After the previous option a codepage can be added:

    Example converts to codepage 1251

    Example with additional options

    !readdeli nogomailing.csv 1 59 34 readin /P=5 /F=20 /U8=1251

    Creates the DBF-file readin from data-file nogomailing.csv  with fieldnames from the first row, delimited by semicolons with values in between quotes.

    With padding=5, fixed=20 and codepage-conversion from UTF8 to windows-1251

    Remarks:

    1. Because ReadDeli.exe is an executable you have to place it in C:\WINDOWS or anywhere other in your windows search-path.
    2. ReadDeli.exe is programmed in C# and depends on .Net framework 4.0 you can install it here, if you don’t have it already:
      http://www.microsoft.com/net/

    Readdeli.exe can de downloaded at my skydrive:

    https://skydrive.live.com/?cid=15c899a43d314dbe#cid=15C899A43D314DBE&id=15C899A43D314DBE%21112

    Blade

    robert.buster@live.nl

    Wednesday, February 29, 2012 8:56 AM

All replies

  • Nice job,

    especially the features to 1. create DBFs on the fly, 2. import multiline text values. 3. create and fill MEMOs.

    Would be nice, if it also would be able to append to an existing DBF.

    And how about creating varchar instead of char fields by demand, via another command line switch?

    And what about autoinc? Do you set a field obviously an autoinc (with incrementing int values only) as an autoinc at all?

    I will try it out.

    Bye, Olaf.

    PS: tried it, created a CSV file with foxpro and your exe doesn't give me more than one field back into a new dbf, stripping out underline in this single field name, by the way, so instead of th_ans_ansatz_pkid I get thansansat, trimmed to 10 chars, obviously because of only creating free dbfs.

    With nHeader 1 I get the first field name only, created as a char(216) field, containing csv lines. With nHeader 0 I get F1 as Memo field with the full lines as it's content, but max(len(f1)) is 876.

    I sepcified 39 as the delimiter, as vfp does output comma separated csv.

    To solve the worst problem first: How do I get more than 1 field? What do you expect as header line as field name separator? Could you give several short CSV samples as test source data?


    Edit, after further playing around with this:

    I found out your exe really just works with semicolon, even if you specify 39 for comma instead of 59 for semicolon as parameter for the seperator.

    So this sample CSV is converted correctly:

    id;ctext1;ctext2
    1;"test";"multline
     test"
    2;"hello";"this
     is a
     test!"
    3;"world";""

    Three fields, and the multiline strings correctly go into the ctext2 field.

    If ading more text you also create a memo. Good enough.

    But actually csv means comma separated values, so comma is the common separator, not semicolon. If you get the nDeli parameter to work correclty, this'll be fine already.

    The created DBF can also be modified to autoinc or appended to an exsiting dbf, so all the extra wishes are secondary.

    Found it: comma is chr(44), not chr(39), chr(39) is ' single quote. So all you need to fix is your documentation, the exe is okay as is.

    • Edited by Olaf Doschke Wednesday, February 29, 2012 8:48 PM
    Wednesday, February 29, 2012 1:45 PM
  • You are correct

    The following line:

    nDeli: ASCII-value of the delimiter-char (example. 39 for ‘,’ 59 for ‘;’)

    Should read:

    nDeli: ASCII-value of the delimiter-char (example. 44 for ‘,’ 59 for ‘;’)

    At the company where I work we only use the CHAR datatype with FoxPro (and no VARCHAR).

    We also only use stand-alone tables instead of databases.

    When you have converted the textfile into a DBF it’s easy to convert to other formats in FoxPro.

    ReadDeli.exe can also be handy for converting from Excel:

    1. In Excel you choose Save as CSV -> “temp.csv”
    2. Then you convert to DBF by typing:

      !readdeli temp.csv 1 59 34 table1
    Thursday, March 01, 2012 12:03 PM
  • When you have converted the textfile into a DBF it’s easy to convert to other formats in FoxPro.

    True, and also append the dbf to an exisiting other dbf etc. It just is an overhead, if it comes to millions of records, that's not only uncomfortable but also a performance issue.

    I understand it's easier to only support creation of free DBFs.

    Still a very handy tool, thanks for contributing this.

    Bye, Olaf.

    Friday, March 02, 2012 7:17 AM
  • A method I have developed under VisualBasic Script to get csv data into VFP; is to convert the csv file into an XML file based upon the first row of field names. Then import the XML file into VFP.

    If anyone is interested in the VBScript to do this I can load it onto my website for them to download.

    Friday, March 30, 2012 3:29 PM
    Answerer
  • Fantastic little program, any chance of the source so we can add a little to it?

    I'd love to get hold of the source for this to expand it's options.


    Ken


    • Edited by Ken Sands Thursday, October 24, 2013 8:54 AM I missed an option
    Thursday, October 24, 2013 8:33 AM