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
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.
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
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
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.
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 )
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.
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,
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.
Ive 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.
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.