CSV Parser

There has been a lot of discussion on this forum about the trials and tribulations of trying to parse a CSV file, having to deal with delimiters and/or linefeeds inside of quoted string, use or lack of escape characters etc…

Well… I think I have come up with a class that “should” handle all of that.
but I would like some input from all of you…

The class consists of one Method

  • Parse(buffer as string), where buffer is the line or lines to be (well…) parsed :slight_smile:
  • two properties… DELIMITER (default is [,]) and ESCAPE (default is [\])
  • one Event - RESULTS… which returns each line one at a time as the class processes it… allowing the main app to decide what to do with it

What I would like from you, is a wacky a “legal” CSV series of lines as you can come up with, so I can insure that I have all the bases covered.

The code (so far) is deceptively simple , but seems to handle what I’ve thrown at it so far.

Once I know it passes what you help me throw at it… I’ll publish it for all to use.

Assuming you mean CSV (not CVS) just grab the second item on this page and its all done already
http://www.great-white-software.com/REALbasic_Code.html

as impressive as that might be… it is total overkill for what I wanted, and yeah… CSV (CVS is a United States based Pharmacy)

my whole class is <100 lines and so far seems to handle every situation I can come up with, escaped characters, double quotes, internal linefeeds.
The only thing it “alters” is if it contains mixed linefeed characters, they all end up as the platform default… but for my purpose that is completely acceptable

mine accepts a block of characters (ie. file contents), and calls an event for each row as it is decoded
yours creates a “record set” like result… which is cool, but not what I wanted…

that codes been around for nearly 10 years and mentioned repeatedly on the forums but …
just trying to save you from reinventing another wheel
its your time

LOL… my class was done and tested BEFORE I posted… and yes it is my time, and that investment was not wasted…

  • I learned something (as I always do when solving problems)
  • I got something that works as I needed (ie. a round peg to fit a round hole)
  • I know EXACTLY how it works, making it less trouble for me to maintain it in the future

So… for me… invested time now, saves a lot of time later…

but thanks for the links :slight_smile:

FYI… the next release of Tadpole will include an enhance CVS Import routine, that is based on the Code that Norman mentioned above :). I would like to thank Tim Parnell for his help in modifiying Normans code to be compatible with the Tadpole infrastucture. I have addtionally added code to support full slash escapes, better error reporting, and a caching system that more that doubled the speed of the importer.

So… yeah you CAN teach an old dog new tricks… it just takes a 2x4 sometimes :slight_smile:

a cattle prod works better on me…

“sticks and stones may break my bones… but whips and chains excite me”… is that what you’re saying [lol]

or it takes a bigger stick than normal (person). Glad you are making great headway on your application.

Hello Norman,

Any idea how to get the total records ? here it returns -1

Then it seems that i have no idea how, i have to many blank columns , actual fields 13 , total columns 160 , is there a way to filter the blank ones and not to use them ?

And the last point, how i can save the data in array and parse it line by line ?

The scope is to get a csv file generated in Windows and to parse it and to add it into a database via a web interface, so i will need the specific columns to identify the data and to validate the correct document and each line to store it in db, the total lines i need it for progress bar import status.

Thanks a lot .

I have a modified version of Normans code as a stand-alone program written with the help of Tim Parnell… I will post a link to it in the morning… it should be simple to adapt it to your use… it contains line counters and a few other enhancements… (not to mention it is 2.4x faster than Normans original code :slight_smile: )

Well thanks Dave,

I`ll wait for your update then.

Thanks again.

CSV Importer

This code is based on the code Norman wrote and mentioned above. It was turned into a stand alone CSVImporter, originally as a test bed for integration into the Tadpole SQLite Manager (of which it is now a part).

As mentioned, originally written by Norman Palardy, modified by Tim Parnell and myself Dave S. This code is provided AS-IS, and any use or suitablity for a particular purpose is at the sole discretion of the developer.

[quote=342093:@Dave S]CSV Importer

This code is based on the code Norman wrote and mentioned above. It was turned into a stand alone CSVImporter, originally as a test bed for integration into the Tadpole SQLite Manager (of which it is now a part).

As mentioned, originally written by Norman Palardy, modified by Tim Parnell and myself Dave S. This code is provided AS-IS, and any use or suitablity for a particular purpose is at the sole discretion of the developer.[/quote]
Thanks a lot Dave,

I`ll have a look and let you know how it works.

When there is a hole, I always put my foot in !

There are two PushButtons: the left one - to choose a target database (“Select Database File”) - have to be pushed before pressing in the second PushButton ( “Show Import Sheet” ).

So, I started by a click in the second button !

You can either:
a. set the “Show Import Sheet” disabled and enable at the end of the “Select Database File” (once a File have been selected),
b. Send a Push to the “Select Database File”, and if a file have been correctly selected, execute the “Show Import Sheet” code.

Of course, I already modify the project for my own use.

Edit (addition):

An I the only one who have loaded and run and press a button of the CSV Parser example ?

As you understand above (reading between lines), you have to provide a data base file to put the csv data in.

I took the file shared in this forum (PropertyData.txt) and:
a. Extract the first line of text (the heading strings),
b. I wrote a sql string using that line, adding a PK (ID),
c. I add a PushButton where I put the code to create the data base file
d. Create a new data base file.

Then, I run the project (2015r1 if that matter),
click in my button to create the data base file,
click in the “Select Database File” button,
Choose my newly created file.

Step1: Setup
click in Guess (works fine).

Step2: Field Mapping
The code does not understand what ID INTEGER NOT NULL, PRIMARY KEY(ID)) means: the PopupMenu does not provide an entry to be selected (of course), and I am stuck here.

When I click in Undefined. Click to Assign, I do not get my ID field.

So, I stayed there (like the fool on the hill) with frustration.

I’ve fooled the csv parser and was able to access to the import part to get a datatype mismatch error.

Excepted the PK (INTEGER), everything else is set to VARCHAR.
Every 56 Fields are declared VARCHAR in the CREATE TABLE string.

is it possible to import into a new table instead of an existing table??

Hi Richard,

I do not know how to understand your question.

The way the project is done is:

a. you select a DataBase to fill (with the data you will elect in step b)
(you can ask to replace the data found in the selected database file).

b. You select the csv file to import the data from.
You have to fill some parts, then the import start.

Nowhere (in CSV Parser) you can create the data base nor fill a TABLE.

The CSV Parser is intelligent enough to ask in what TABLE you want to import the data when the selected database file holds more than one.

modify it as you see fit

[quote=342232:@Emile Schwarz]Hi Richard,

I do not know how to understand your question.

The way the project is done is:

a. you select a DataBase to fill (with the data you will elect in step b)
(you can ask to replace the data found in the selected database file).

b. You select the csv file to import the data from.
You have to fill some parts, then the import start.

Nowhere (in CSV Parser) you can create the data base nor fill a TABLE.

The CSV Parser is intelligent enough to ask in what TABLE you want to import the data when the selected database file holds more than one.[/quote]

what i mean is, it ask to select from the dropdown a list of existing table to import to. that is why i ask if the program can create a new table to insert into. when i try earlier, i need to add in the table manually and then do the import.